如何根据多行的结果设置mysql“case-when”的默认值

我的问题是：
通过以下查询，我得到以下结果：

SELECT e.employes_id AS user_id, IFNULL(e.employes_initiales, 'Divers') AS initials, s.lieu, s.debut, s.fin, IFNULL(s.couleur, '#000000') AS color , s.id AS p_id,
CASE when couleur = '#00FF00' then 1 else 0 END as callservice
FROM ap_employes e LEFT JOIN
(ap_planing_employes sl JOIN
ap_planing s
ON sl.id_planing = s.id AND s.effacee = 0 AND
DATE('2018-04-27') BETWEEN CAST(s.debut AS DATE) AND CAST(s.fin AS DATE))
ON employes_id = sl.id_employes
WHERE e.employes_effacee = 0
ORDER BY e.employes_id ASC, FIELD(s.couleur,'#00FF00') desc, s.debut ASC, s.lieu ASC

结果

user_id  initials  lieu                                                    debut                  fin  color      p_id  callservice  
-------  --------  ----------------------------------------  -------------------  -------------------  -------  ------  -------------
      1  DV        Test                                      2018-04-27 07:30:00  2018-04-27 07:30:00  #000000     526              0
      1  DV        Another Test                              2018-04-27 09:00:00  2018-04-27 09:00:00  #000000     504              0
      1  DV        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      2  SA        Call service                              2018-04-27 00:00:00  2018-04-28 00:00:00  #00FF00     336              1
      2  SA        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      3  SW        Customer x                                2018-04-27 00:00:00  2018-04-27 00:00:00  #000000     547              0
      3  SW        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      3  SW        End at 2pm                                2018-04-27 14:00:00  2018-04-27 14:00:00  #FF0000     538              0
      4  JE        Test2                                     2018-04-27 10:00:00  2018-04-27 10:00:00  #000000     541              0
      4  JE        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      5  FR        Holydays                                  2018-04-11 00:00:00  2018-04-29 00:00:00  #FF0000      75              0
      5  FR        Holydays                                  2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      8  IE        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
    999  Divers    (NULL)                                                 (NULL)               (NULL)  #000000  (NULL)              0

好吧，到目前为止还不错
如您所见，在第4行中，最后一列“callservice”是1，因为根据我的请求，此行的颜色是正确的
现在是我的问题。我希望当没有人拥有与呼叫服务对应的颜色时，它会自动分配给用户id 3
如果任何用户的颜色与呼叫服务对应，那么他将有一个collone“callservice”为1。否则，默认情况下它必须是用户id 3。。。
我理解函数的工作原理，但我不能将它应用于几行。。。
如何做？
如果我的问题不是很清楚，我可以试着改进
下面是我想要得到的结果的一个例子。如您所见，没有人具有与callservice对应的颜色，因此callservice被分配给用户id 3

user_id  initials  lieu                                                    debut                  fin  color      p_id  callservice  
-------  --------  ----------------------------------------  -------------------  -------------------  -------  ------  -------------
      1  DV        Test                                      2018-04-27 07:30:00  2018-04-27 07:30:00  #000000     526              0
      1  DV        Another Test                              2018-04-27 09:00:00  2018-04-27 09:00:00  #000000     504              0
      1  DV        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      2  SA        Customer y                                2018-04-27 00:00:00  2018-04-28 00:00:00  #000000     336              0
      2  SA        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      3  SW        Customer x                                2018-04-27 00:00:00  2018-04-27 00:00:00  #000000     547              1
      3  SW        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              1
      3  SW        End at 2pm                                2018-04-27 14:00:00  2018-04-27 14:00:00  #FF0000     538              1
      4  JE        Test2                                     2018-04-27 10:00:00  2018-04-27 10:00:00  #000000     541              0
      4  JE        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      5  FR        Holydays                                  2018-04-11 00:00:00  2018-04-29 00:00:00  #FF0000      75              0
      5  FR        Holydays                                  2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      8  IE        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
    999  Divers    (NULL)                                                 (NULL)               (NULL)  #000000  (NULL)              0

嗯，这也许是一个复杂的解决方案，但是。。。

SET @couleur_number = 0;

DROP TEMPORARY TABLE IF EXISTS temp_table;
CREATE TEMPORARY TABLE temp_table ENGINE=MEMORY AS

SELECT e.employes_id AS user_id, IFNULL(e.employes_initiales, 'Divers') AS initials, 
s.lieu, s.debut, s.fin, IFNULL(s.couleur, '#000000') AS color , s.id AS p_id,
CASE when couleur = '#00FF00' then @couleur_number := @couleur_number + 1
FROM ap_employes e LEFT JOIN
(ap_planing_employes sl JOIN
ap_planing s
ON sl.id_planing = s.id AND s.effacee = 0 AND
DATE('2018-04-27') BETWEEN CAST(s.debut AS DATE) AND CAST(s.fin AS DATE))
ON employes_id = sl.id_employes
WHERE e.employes_effacee = 0
ORDER BY e.employes_id ASC, FIELD(s.couleur,'#00FF00') desc, s.debut ASC, s.lieu ASC;

SELECT user_id, initials, 
    s.lieu, s.debut, s.fin, color, p_id
CASE when @couleur_number > 0
    then
        CASE when color='#00FF00'
        then 1 else 0
        end
    else
        CASE when user_id='3'
        then 1 else 0
        end
END as callservice
 FROM temp_table;

如何根据多行的结果设置mysql“case-when”的默认值

1条答案

相关问题

热门标签

最新问答