mysql - MYSQL中如何选择多个行值作为列值？

Question

我有两个通过 ID 链接的表(下面是简化的示例):

Table 1

ID     NAME
1      John
2      Frank

Table 2

ID     DAY     TIME_FROM    TIME_TO
1      Sun     8:00         15:00
1      Mon     8:00         15:00
1      Tue     NULL         NULL
1      Wed     NULL         NULL
1      Thu     NULL         NULL
1      Fri     8:00         15:00
1      Sat     8:00         15:00
2      Sun     8:00         15:00
...

我想要的是一个查询，理想情况下返回一个字段中每个 ID 的缩写天数，如下所示:

ID     NAME    DAYS 
1      John    S M F S 
2      Frank   S M T W T F S

或者也许:

ID     NAME    SUN    MON   TUE    
1      John    TRUE   TRUE  FALSE
2      Frank   TRUE   TRUE  TRUE

如果可能，首选第一个解决方案。

Answer 1

试试这个:

1. 第一个解决方案:

   SELECT T1.ID,T1.Name,
           GROUP_CONCAT(T2.`DAY` SEPARATOR ' ') as Day
   FROM Table1 T1 JOIN
        Table2 T2 ON T1.Id=T2.Id
   GROUP BY T1.ID,T1.Name
   

   结果:

   ID  NAME    DAY
   1   John    Thu Fri Sat Sun Mon Tue Wed
   2   Frank   Sun
   

   注意:使用 3 个字母以避免 Sat 和 Sun 之间的混淆。查看SQL Fiddle中的结果.

2. 第二种解决方案:

   SELECT T1.ID,T1.Name,
           MAX(CASE WHEN (COALESCE(TIME_FROM,TIME_TO) IS NOT NULL  AND `DAY` ='Sun') THEN 'TRUE' ELSE 'FALSE' END) as SUN,
           MAX(CASE WHEN (COALESCE(TIME_FROM,TIME_TO) IS NOT NULL  AND `DAY` ='Mon') THEN 'TRUE' ELSE 'FALSE' END) as MON,
           MAX(CASE WHEN (COALESCE(TIME_FROM,TIME_TO) IS NOT NULL  AND `DAY` ='Tue') THEN 'TRUE' ELSE 'FALSE' END) as TUE,
           MAX(CASE WHEN (COALESCE(TIME_FROM,TIME_TO) IS NOT NULL  AND `DAY` ='Wed') THEN 'TRUE' ELSE 'FALSE' END) as WED,
           MAX(CASE WHEN (COALESCE(TIME_FROM,TIME_TO) IS NOT NULL  AND `DAY` ='Thu') THEN 'TRUE' ELSE 'FALSE' END) as THU,
       MAX(CASE WHEN (COALESCE(TIME_FROM,TIME_TO) IS NOT NULL  AND `DAY` ='Fri') THEN 'TRUE' ELSE 'FALSE' END) as FRI,
       MAX(CASE WHEN (COALESCE(TIME_FROM,TIME_TO) IS NOT NULL  AND `DAY` ='Sat') THEN 'TRUE' ELSE 'FALSE' END) as SAT
   FROM Table1 T1 JOIN
        Table2 T2 ON T1.Id=T2.Id
   GROUP BY T1.ID,T1.Name
   

   说明:

   COALESCE 对于检查 TIME_FROM 和 TIME_TO 是否为 NULL 非常有用。

   结果(使用给定的示例):

   ID  NAME    SUN     MON     TUE     WED     THU     FRI     SAT
   1   John    TRUE    TRUE    FALSE   FALSE   FALSE   TRUE    TRUE
   2   Frank   TRUE    FALSE   FALSE   FALSE   FALSE   FALSE   FALSE
   

   结果为SQL Fiddle .