Mysql

group_concat 和 group by 在一起

  • October 27, 2017

我正在嘗試使用group_concat,concat和其他函式編寫查詢，但我遇到了無法加入和分組數據以在 1 個單元格中表示它們的問題。

這是我的數據的方式…

table1
+-----+---------+
| id  | ...     |
+-----+---------+
| 1   |         |
| 2   |         |
| 3   |         |
+-----+---------+
table2
+-----+-----+-------+
| id  | env | infid |
+-----+-----+-------+
| 1   | p   | 10    |
| 1   | p   | 11    |
| 1   | p   | 20    |
| 1   | p   | 12    |
| 1   | D   | 21    |
| 1   | D   | 22    |
+-----+-----+-------+
table3
+-------+---------+---------+
| infid | inftype | infname |
+-------+---------+---------+
| 10    | Srv     | abc     |
| 20    | Srv     | xyz     |
| 11    | Db      | hgj     |
| 12    | Db      | kjk     |
| 21    | Srv     | pop     |
| 22    | Db      | kli     |
+-------+---------+---------+

預期的查詢結果

+-----+------------------------------+---------------------+
| id  | P                            | D                   |
+------------------------------------+---------------------+
| 1   |Srv: [abc, xyz] Db: [hgj, kjk]|Srv: [pop] Db: [Kli] |
+-----+------------------------------+---------------------+

任何幫助是極大的讚賞。

您需要的第一件事是創建每一列的凌亂查詢

查詢的第一階段

SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env;

您的樣本數據

mysql> DROP DATABASE IF EXISTS kumar_concat;
Query OK, 3 rows affected (0.03 sec)

mysql> CREATE DATABASE kumar_concat;
Query OK, 1 row affected (0.00 sec)

mysql> USE kumar_concat
Database changed
mysql> CREATE TABLE table1
   -> (
   ->     id INT NOT NULL AUTO_INCREMENT,
   ->     PRIMARY KEY (id)
   -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table1 VALUES (),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table2
   -> (
   ->     id INT NOT NULL,
   ->     env VARCHAR(10) NOT NULL,
   ->     infid INT NOT NULL,
   ->     PRIMARY KEY (id,infid)
   -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO table2 (id,env,infid) VALUES
   -> (1,'P',10), (1,'P',11), (1,'P',20),
   -> (1,'P',12), (1,'D',21), (1,'D',22);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table3
   -> (
   ->     infid INT NOT NULL,
   ->     inftype VARCHAR(10) NOT NULL,
   ->     infname VARCHAR(10) NOT NULL,
   ->     PRIMARY KEY (infid)
   -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table3 (infid,inftype,infname) VALUES
   -> (10,'Srv','abc'), (20,'Srv','xyz'), (11,'Db','hgj'),
   -> (12,'Db','kjk'), (21,'Srv','pop'), (22,'Db','kli');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

已執行查詢的第一階段

mysql> SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
   -> ORDER BY inftype DESC SEPARATOR ' ') tags
   -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
   -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
   -> FROM table1 AAA
   -> INNER JOIN table2 BBB ON AAA.id = BBB.id
   -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
   -> GROUP BY env,inftype) A GROUP BY env;
+-----+------------------------------+
| env | tags                         |
+-----+------------------------------+
| D   | Srv [pop] Db [kli]           |
| P   | Srv [abc, xyz] Db [hgj, kjk] |
+-----+------------------------------+
2 rows in set (0.02 sec)

mysql>

讓我們為每個 env 值創建每個

詢問

SELECT
   T1.tags P,T2.tags D
FROM
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T1,
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T2
WHERE T1.env='P'
AND T2.env='D';

查詢已執行

mysql> SELECT
   ->     T1.tags P,T2.tags D
   -> FROM
   -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
   -> ORDER BY inftype DESC SEPARATOR ' ') tags
   -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
   -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
   -> FROM table1 AAA
   -> INNER JOIN table2 BBB ON AAA.id = BBB.id
   -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
   -> GROUP BY env,inftype) A GROUP BY env) T1,
   -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
   -> ORDER BY inftype DESC SEPARATOR ' ') tags
   -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
   -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
   -> FROM table1 AAA
   -> INNER JOIN table2 BBB ON AAA.id = BBB.id
   -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
   -> GROUP BY env,inftype) A GROUP BY env) T2
   -> WHERE T1.env='P'
   -> AND T2.env='D';
+------------------------------+--------------------+
| P                            | D                  |
+------------------------------+--------------------+
| Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+------------------------------+--------------------+
1 row in set (0.05 sec)

mysql>

最終查詢（插入 id）

SELECT
   T1.id,T1.tags P,T2.tags D
FROM
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T1
INNER JOIN
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T2 
USING (id) WHERE T1.env='P' AND T2.env='D';

最終查詢（已插入 id）已執行

mysql> SELECT
   ->     T1.id,T1.tags P,T2.tags D
   -> FROM
   -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
   -> ORDER BY inftype DESC SEPARATOR ' ') tags
   -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
   -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
   -> FROM table1 AAA
   -> INNER JOIN table2 BBB ON AAA.id = BBB.id
   -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
   -> GROUP BY id,env,inftype) A GROUP BY id,env) T1
   -> INNER JOIN
   -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
   -> ORDER BY inftype DESC SEPARATOR ' ') tags
   -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
   -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
   -> FROM table1 AAA
   -> INNER JOIN table2 BBB ON AAA.id = BBB.id
   -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
   -> GROUP BY id,env,inftype) A GROUP BY id,env) T2
   -> USING (id) WHERE T1.env='P' AND T2.env='D';
+----+------------------------------+--------------------+
| id | P                            | D                  |
+----+------------------------------+--------------------+
|  1 | Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+----+------------------------------+--------------------+
1 row in set (0.08 sec)

mysql>

試一試 ！！！

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

相關問答

Mysql

使用 ORDER 限制 GROUP BY 和 GROUP_CONCAT 中的行數

  • September 24, 2021
檢視問答
Mysql

查詢每月有 group_concat 和 group by 需要幫助

  • April 10, 2021
檢視問答
Mysql

如何對來自相同兩個表但來自兩個不同結果集和/或條件的相同列進行分組

  • December 30, 2018
檢視問答
Mysql

MySQL 上的 COUNT()、GROUP BY 和 GROUP_CONCAT()

  • May 4, 2018
檢視問答
Mysql

如何在一個查詢中處理可能為空元素的嵌套分組

  • March 22, 2016
檢視問答
Mysql

多個帶有 WHERE 子句的“GROUP_CONCAT”

  • December 22, 2012
檢視問答