Mysql
查詢每月有 group_concat 和 group by 需要幫助
我被困在連接表查詢上，該查詢每月顯示涉及 GROUP BY 和 GROUP_CONCAT 的數據。
這是一個簡單的客戶端表（本文底部的 DDL 和 DML）：
id | Name 1 | Sony 2 | Toshiba 3 | Apple 4 | LG 5 | Uco然後是事件表
id | client_id | date_start 1 | 1 | 2017-01-12 18:44:42 2 | 1 | 2017-01-13 18:44:42 3 | 1 | 2017-01-14 18:44:42 4 | 1 | 2017-02-12 18:44:42 5 | 1 | 2017-03-12 18:44:42 6 | 1 | 2017-07-12 18:44:42 7 | 2 | 2017-02-12 18:44:42 8 | 2 | 2017-03-12 18:44:42 9 | 2 | 2017-04-12 18:44:42 10 | 3 | 2017-01-12 18:44:42 11 | 3 | 2017-01-14 18:44:42 12 | 3 | 2017-01-20 18:44:42 13 | 3 | 2017-03-12 18:44:42 14 | 3 | 2017-05-12 18:44:42 15 | 3 | 2017-06-12 18:44:42 16 | 4 | 2017-07-12 18:44:42 17 | 4 | 2017-07-20 18:44:42 18 | 5 | 2017-09-12 18:44:42 19 | 5 | 2017-10-12 18:44:42 20 | 5 | 2017-03-12 18:44:42期望的結果如下。字元串編號，例如 Jan/Apple 上的 (10-01-12) 格式為 id-month-day。
到目前為止，我所做的是使用 case when 每月拆分結果：
select * from ( select e.id, c.name as client, (CASE WHEN MONTH(e.date_start) = 1 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jan, (CASE WHEN MONTH(e.date_start) = 2 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as feb, (CASE WHEN MONTH(e.date_start) = 3 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as mar, (CASE WHEN MONTH(e.date_start) = 4 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as apr, (CASE WHEN MONTH(e.date_start) = 5 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as may, (CASE WHEN MONTH(e.date_start) = 6 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jun, (CASE WHEN MONTH(e.date_start) = 7 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jul, (CASE WHEN MONTH(e.date_start) = 8 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as aug, (CASE WHEN MONTH(e.date_start) = 9 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as sep, (CASE WHEN MONTH(e.date_start) = 10 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as oct, (CASE WHEN MONTH(e.date_start) = 11 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as nov, (CASE WHEN MONTH(e.date_start) = 12 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as `dec` from event as e left join client as c on c.id=e.client_id group by month(date_start),client order by client ) t
但是上面的查詢需要最後按客戶分組。如何將按客戶結果分組的結果顯示在上面所需的表格中，並以逗號作為分隔符？
第二部分是每月統計每個數據的總和。沒那麼重要，我真的需要讓第一部分工作。
這是數據和表的 SQL。
CREATE TABLE `client` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `client` WRITE; INSERT INTO `client` (`id`, `name`) VALUES (1,'Sony'), (2,'Toshiba'), (3,'Apple'), (4,'LG'), (5,'Uco'); UNLOCK TABLES; CREATE TABLE `event` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `client_id` int(11) unsigned DEFAULT NULL, `date_start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `client_id` (`client_id`), KEY `date_start` (`date_start`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; LOCK TABLES `event` WRITE; INSERT INTO `event` (`id`, `client_id`, `date_start`) VALUES (1,1,'2017-01-12 18:44:42'), (2,1,'2017-01-13 18:44:42'), (3,1,'2017-01-14 18:44:42'), (4,1,'2017-02-12 18:44:42'), (5,1,'2017-03-12 18:44:42'), (6,1,'2017-07-12 18:44:42'), (7,2,'2017-02-12 18:44:42'), (8,2,'2017-03-12 18:44:42'), (9,2,'2017-04-12 18:44:42'), (10,3,'2017-01-12 18:44:42'), (11,3,'2017-01-14 18:44:42'), (12,3,'2017-01-20 18:44:42'), (13,3,'2017-03-12 18:44:42'), (14,3,'2017-05-12 18:44:42'), (15,3,'2017-06-12 18:44:42'), (16,4,'2017-07-12 18:44:42'), (17,4,'2017-07-20 18:44:42'), (18,5,'2017-09-12 18:44:42'), (19,5,'2017-10-12 18:44:42'), (20,5,'2017-03-12 18:44:42'); UNLOCK TABLES;
術語和方法
這種轉換——行到列——被稱為透視。通常將數據與它們的聚契約時進行數據透視，這似乎也是您的情況的要求。在 SQL 中，您可以將這兩個操作作為一個邏輯步驟進行。其他 SQL 產品甚至為數據透視提供了特殊的語法擴展，但是有一種方法可以使用更通用的語法來做到這一點，至少每個主要的 RDBMS 都支持這種語法，包括 MySQL。
該方法稱為條件聚合，您幾乎成功了。在查詢中作為 CASE 表達式實現的條件應該放在聚合函式內部，並且條件檢查的條件（
MONTH(e.date_start)在您的情況下）需要從 GROUP BY中排除。所以，而不是
SELECT CASE WHEN MONTH(e.date_start) = 1 THEN GROUP_CONCAT(...), ... FROM ... GROUP BY MONTH(e.date_start), client它應該是
SELECT GROUP_CONCAT(**CASE WHEN MONTH(e.date_start) = 1 THEN** ...), ... FROM ... GROUP BY **~~MONTH(e.date_start),~~** client排除部分可能看起來違反直覺——畢竟，您打算獲取每月數據。但是，您應該記住，在 SQL 中，您正在對行進行分組。在您的情況下，一行是一個客戶 - 因此，分組應該只按客戶。您可以說每月分組是隱式的，因為它僅通過條件聚合實現。
無論如何，最後一行呢？最後一行是特殊的，不僅僅是因為它是一個匯總行，因此代表了整個集合的聚合數據。在我看來，它更特別，因為它包含完全不同的數據：計數而不是串聯的字元串。
基於這個事實，對我來說，考慮一個不同的邏輯步驟——一個單獨的 SELECT——來獲取最後一行的結果似乎很自然。然後，這兩個結果集將在 UNION ALL 運算符的幫助下合併為一個。在我看來，這種方法可以使邏輯清晰：輸出中的不同類型的數據將由查詢的不同分支來解釋。清晰的邏輯最終意味著易於維護。
解決方案
因此，考慮到上述所有因素，完整的查詢可能如下所示：
SELECT c.name AS client, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 1 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jan, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 2 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS feb, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 3 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS mar, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 4 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS apr, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 5 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS may, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 6 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jun, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 7 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jul, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 8 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS aug, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 9 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS sep, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 10 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS oct, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 11 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS nov, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 12 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS `dec` FROM event AS e INNER JOIN client AS c ON e.client_id = c.id GROUP BY c.name UNION ALL SELECT NULL, COUNT(MONTH(e.date_start) = 1 OR NULL), COUNT(MONTH(e.date_start) = 2 OR NULL), COUNT(MONTH(e.date_start) = 3 OR NULL), COUNT(MONTH(e.date_start) = 4 OR NULL), COUNT(MONTH(e.date_start) = 5 OR NULL), COUNT(MONTH(e.date_start) = 6 OR NULL), COUNT(MONTH(e.date_start) = 7 OR NULL), COUNT(MONTH(e.date_start) = 8 OR NULL), COUNT(MONTH(e.date_start) = 9 OR NULL), COUNT(MONTH(e.date_start) = 10 OR NULL), COUNT(MONTH(e.date_start) = 11 OR NULL), COUNT(MONTH(e.date_start) = 12 OR NULL) FROM event AS e ;或者，也許，像這樣，如果我們想通過消除一些程式碼的重複來使它看起來不那麼麻煩：
SELECT client, GROUP_CONCAT(CASE month WHEN 1 THEN item END SEPARATOR ',') AS jan, GROUP_CONCAT(CASE month WHEN 2 THEN item END SEPARATOR ',') AS feb, GROUP_CONCAT(CASE month WHEN 3 THEN item END SEPARATOR ',') AS mar, GROUP_CONCAT(CASE month WHEN 4 THEN item END SEPARATOR ',') AS apr, GROUP_CONCAT(CASE month WHEN 5 THEN item END SEPARATOR ',') AS may, GROUP_CONCAT(CASE month WHEN 6 THEN item END SEPARATOR ',') AS jun, GROUP_CONCAT(CASE month WHEN 7 THEN item END SEPARATOR ',') AS jul, GROUP_CONCAT(CASE month WHEN 8 THEN item END SEPARATOR ',') AS aug, GROUP_CONCAT(CASE month WHEN 9 THEN item END SEPARATOR ',') AS sep, GROUP_CONCAT(CASE month WHEN 10 THEN item END SEPARATOR ',') AS oct, GROUP_CONCAT(CASE month WHEN 11 THEN item END SEPARATOR ',') AS nov, GROUP_CONCAT(CASE month WHEN 12 THEN item END SEPARATOR ',') AS `dec` FROM ( SELECT c.name AS client, MONTH(e.date_start) AS month, CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) AS item FROM event AS e INNER JOIN client AS c ON e.client_id = c.id ) AS derived GROUP BY client UNION ALL SELECT NULL, COUNT(month = 1 OR NULL), COUNT(month = 2 OR NULL), COUNT(month = 3 OR NULL), COUNT(month = 4 OR NULL), COUNT(month = 5 OR NULL), COUNT(month = 6 OR NULL), COUNT(month = 7 OR NULL), COUNT(month = 8 OR NULL), COUNT(month = 9 OR NULL), COUNT(month = 10 OR NULL), COUNT(month = 11 OR NULL), COUNT(month = 12 OR NULL) FROM ( SELECT MONTH(e.date_start) AS month FROM event AS e ) AS derived ;如果您對公式不是很熟悉，可以將
A = B OR NULL其視為CASE WHEN A = B THEN 1 ELSE NULL END. 有關其實際工作原理的詳細資訊，請參閱此 Stack Overflow 問題：選擇
儘管有所有關於清晰邏輯和可維護性的精彩討論，您可能仍然希望能夠將查詢實現為單個 SELECT。儘管我們已經設法通過減少程式碼重複來簡化初始版本，但
MONTH(date_start)表達式仍然必須在查詢中指定兩次，因為每個 SELECT 分支都需要它，那麼為什麼不嘗試消除重複呢？如果這還不足以成為一個理由，那麼替代解決方案可能會更快，甚至可能會很明顯。也許得到的查詢看起來不會太難看。最後，有一個簡單明了的選擇就好了。那麼，如何使用WITH ROLLUP重寫查詢，以便客戶端詳細資訊和匯總行都由相同的 SELECT 語句生成（沒有任何 UNION ALL 類型的作弊）？
好吧，您可以使用以前的解決方案作為原型。該查詢的一部分在客戶端上執行組連接。另一部分計算整個集合中的行數。現在，如果您想進行單部分查詢，則單部分必須在兩個級別上執行這兩項操作。
然後應該由另一組條件來確定在哪個級別顯示哪種資訊。
考慮到上述幾點，這裡是我對單步查詢的嘗試：
SELECT client, IF(client IS NULL, COUNT(month = 1 OR NULL), GROUP_CONCAT(CASE month WHEN 1 THEN item END SEPARATOR ',')) AS jan, IF(client IS NULL, COUNT(month = 2 OR NULL), GROUP_CONCAT(CASE month WHEN 2 THEN item END SEPARATOR ',')) AS feb, IF(client IS NULL, COUNT(month = 3 OR NULL), GROUP_CONCAT(CASE month WHEN 3 THEN item END SEPARATOR ',')) AS mar, IF(client IS NULL, COUNT(month = 4 OR NULL), GROUP_CONCAT(CASE month WHEN 4 THEN item END SEPARATOR ',')) AS apr, IF(client IS NULL, COUNT(month = 5 OR NULL), GROUP_CONCAT(CASE month WHEN 5 THEN item END SEPARATOR ',')) AS may, IF(client IS NULL, COUNT(month = 6 OR NULL), GROUP_CONCAT(CASE month WHEN 6 THEN item END SEPARATOR ',')) AS jun, IF(client IS NULL, COUNT(month = 7 OR NULL), GROUP_CONCAT(CASE month WHEN 7 THEN item END SEPARATOR ',')) AS jul, IF(client IS NULL, COUNT(month = 8 OR NULL), GROUP_CONCAT(CASE month WHEN 8 THEN item END SEPARATOR ',')) AS aug, IF(client IS NULL, COUNT(month = 9 OR NULL), GROUP_CONCAT(CASE month WHEN 9 THEN item END SEPARATOR ',')) AS sep, IF(client IS NULL, COUNT(month = 10 OR NULL), GROUP_CONCAT(CASE month WHEN 10 THEN item END SEPARATOR ',')) AS oct, IF(client IS NULL, COUNT(month = 11 OR NULL), GROUP_CONCAT(CASE month WHEN 11 THEN item END SEPARATOR ',')) AS nov, IF(client IS NULL, COUNT(month = 12 OR NULL), GROUP_CONCAT(CASE month WHEN 12 THEN item END SEPARATOR ',')) AS `dec` FROM ( SELECT c.name AS client, MONTH(e.date_start) AS month, CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) AS item FROM event AS e INNER JOIN client AS c ON e.client_id = c.id ) AS derived GROUP BY client WITH ROLLUP ;如您所見，查詢在客戶端級別和整個集合級別都計算 COUNT 和 GROUP_CONCAT。但是每對結果都放在一個 IF 函式中，因此最終每列中只返回一個或另一個結果。
檢查的條件是
client IS NULL。如果client碰巧為空，則意味著目前組代表整個集合，在這種情況下，每個 IF 函式都會選擇 COUNT 結果。當clientvalue 不為 null 時，這意味著我們處於客戶端級別，並且每組行代表一個特定的客戶端。在這種情況下，返回 GROUP_CONCAT 結果，這是根據要求，因為對於客戶端，我們必須顯示連接的字元串。這兩種解決方案都可以在dbfiddle.uk找到。
附加說明
在我上面的解釋中，我試圖專注於解決方案以及它們是如何工作的。為了避免分心，我在我的程式碼中允許了一些值得一提的反模式。
- 隱式轉換數據時依賴優先級規則。
函式 COUNT() 和 GROUP_CONCAT() 的結果是不同的。一個返回一個整數，另一個返回一個字元串。當您嘗試將這些不同類型的值放入單個列時，伺服器必須決定將哪種類型轉換為其他類型。了解這些規則是件好事，但您不應該在生產程式碼中依賴它們。那隻是不好的做法。
在上面的查詢中，COUNT 和 GROUP_CONCAT 要麼位於同一查詢的不同分支的同一列中，要麼位於在兩者之間進行選擇的相同條件中。在每種情況下，MySQL 都需要應用其類型優先規則。為避免這種情況，您可以將每個 COUNT 顯式轉換為字元串：
CAST(COUNT(...) AS char)
- GROUP_CONCAT 中缺少 ORDER BY。
如果您省略了 ORDER BY，您只是在說您不關心查詢是否一次返回字元串 as
A,B,C，另一次返回 as ，然後返回B,A,CasC,B,A。如果您希望結果是可預測的，請始終指定 ORDER BY 並始終使用足夠的標準來避免平局。在上面的查詢中，這些行已經很長了，為了美觀起見，我故意省略了 ORDER BY。這個問題可以通過這樣的 ORDER BY 輕鬆解決：
ORDER BY item ASC更具體地說，在 GROUP_CONCAT 中，它將像這樣使用：
GROUP_CONCAT(CASE month WHEN 12 THEN item END **ORDER BY item ASC** SEPARATOR ',')