比如有 A B 表
A 表
ID,NAME
1,A
2,B
3,C
B 表
FK_ID,TYPE,VALUE1,VALUE2
1,cpu,100,90
1,mem,90,89
2,cpu,20,22
2,mem,100,99
3,cpu,90,60
3,mem,20,20
求这样的结果
A_ID,A_NAME,A_CPU_RECORD,A_MEM_RECORD
1,A,'100/90',"90/89"
2,B,'20/22',"100/99"
查询结果:
A 表
ID,NAME
1,A
2,B
3,C
B 表
FK_ID,TYPE,VALUE1,VALUE2
1,cpu,100,90
1,mem,90,89
2,cpu,20,22
2,mem,100,99
3,cpu,90,60
3,mem,20,20
求这样的结果
A_ID,A_NAME,A_CPU_RECORD,A_MEM_RECORD
1,A,'100/90',"90/89"
2,B,'20/22',"100/99"
3,C,'90/60',"20/20"
代码如下:
SELECT a.id AS a_id,
a.name AS a_name,
MAX(CASE WHEN TYPE='cpu' THEN CONCAT(b.value1,'/',b.value2) END) AS A_CPU_RECORD,
MAX(CASE WHEN TYPE='mem' THEN CONCAT(b.value1,'/',b.value2) END) AS A_MEM_RECORD
FROM
(
SELECT 1 AS ID,'A' AS NAME UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C'
)a
INNER JOIN
(
SELECT 1 FK_ID,'cpu' TYPE,'100' VALUE1,'90' VALUE2 UNION ALL
SELECT 1,'mem','90','89' UNION ALL
SELECT 2,'cpu','20','22' UNION ALL
SELECT 2,'mem','100','99' UNION ALL
SELECT 3,'cpu','90','60' UNION ALL
SELECT 3,'mem','20','20'
)b
ON a.id = b.fk_id
GROUP BY a.id,a.name
查询结果: