多個帶有 WHERE 子句的“GROUP_CONCAT”

在表中

id    name      type           info
1     BMW       car            yes
2     Reno      car            no
3     IBM       electronics    no
4     Sony      electronics    yes
5     Mazda     car            yes

我GROUP_CONCAT用來獲取每個的列表type，但我想將連接的列分隔為按列分類的多個列info。它應該是這樣的

SELECT type,
      GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_with_info
      GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_without_info
      FROM table1 GROUP BY type

如何引入WHERE子句或使用其他方法返回多個連接列？

SELECT type,
      GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END 
                    ORDER BY id ASC SEPARATOR ' ') AS list_with_info,
      GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END 
                    ORDER BY id ASC SEPARATOR ' ') AS list_without_info      
FROM table1
GROUP BY type ;

在 SQL-Fiddle 測試：test-1

---

如果您想要兩行而不是一行的結果，那會更容易，只需GROUP BY兩者type, info：

SELECT 
   type, info,
   GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
     AS list     
FROM table1
GROUP BY type
      , info ;

這也可以用於提供您想要的每類型一行的格式：

SELECT
   type,
   MIN( CASE WHEN info = 'yes' THEN list END )
     AS list_with_info,
   MIN( CASE WHEN info = 'no' THEN list END )
     AS list_without_info    
FROM
   ( SELECT 
         type, info,
         GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
           AS list     
     FROM table1
     GROUP BY type
            , info 
   ) AS grp 
GROUP BY type ;

在 SQL-Fiddle 測試：test-2

上述兩個查詢將受益於索引(type, info, name)

---

以下內容將受益於 上的索引(info, type, name)：

SELECT
   dt.type,
   grpy.list  AS list_with_info,
   grpn.list  AS list_without_info    
FROM
   ( SELECT DISTINCT type
     FROM table1
   ) AS dt
 LEFT JOIN
   ( SELECT 
         type,
         GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
           AS list     
     FROM table1
     WHERE info = 'yes'
     GROUP BY type 
   ) AS grpy
     ON grpy.type = dt.type
 LEFT JOIN
   ( SELECT 
         type,
         GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
           AS list     
     FROM table1
     WHERE info = 'no'
     GROUP BY type 
   ) AS grpn
     ON grpn.type = dt.type ;

在 SQL-Fiddle 測試：test-3

引用自：https://dba.stackexchange.com/questions/30946