mysql - SQL 组表值

Question

所以我有一个 SQL 查询，它由一个选择、多个子查询、多个连接和一些 where 条件组成。它看起来像:

SELECT concat(t1.name, ' ', t1.surname) AS users,
(SELECT t3.value AS col1 WHERE t6.id=343),
(SELECT t3.value AS col2 WHERE t6.id=344),
(SELECT t3.value AS col3 WHERE t6.id=345),
(SELECT t3.value AS col4 WHERE t6.id=346),
(SELECT t3.value AS col5 WHERE t6.id=347),
(SELECT t3.value AS col6 WHERE t6.id=348),
(SELECT t3.value AS col7 WHERE t6.id=349),
(SELECT t3.value AS col8 WHERE t6.id=350),
(SELECT t3.value AS col9 WHERE t6.id=351)
FROM table1 t1
JOIN table2 t2
ON t2.id_table1_user=t1.id
JOIN table3 t3
ON t2.id=t3.id_eva
JOIN table4 t4
ON t3.id_pro_q=t4.id
JOIN table5 t5
ON t4.id_pro=t5.id
JOIN table6 t6
ON t4.id_t6=t6.id
JOIN table7 t7
ON t7.id_table1_user=t1.id
WHERE t5.id=151

它给了我这张表:

现在...我的问题是 - 如何对行进行分组以便只有 2 行且没有 NULL？有办法吗？

请注意 - 我是一个完全的 SQL 初学者，所以非常欢迎任何关于改进命令或任何东西的建议。

Answer 1

你需要一个 group by 子句:

SELECT concat(t1.name, ' ', t1.surname) AS users,
       max (CASE WHEN t6.id = 343 then t3.value end) as col1,
       max (CASE WHEN t6.id = 344 then t3.value end) as col2,
       max (CASE WHEN t6.id = 345 then t3.value end) as col3,
       max (CASE WHEN t6.id = 346 then t3.value end) as col4,
       max (CASE WHEN t6.id = 347 then t3.value end) as col5,
       max (CASE WHEN t6.id = 348 then t3.value end) as col6,
       max (CASE WHEN t6.id = 349 then t3.value end) as col7,
       max (CASE WHEN t6.id = 350 then t3.value end) as col8,
       max (CASE WHEN t6.id = 351 then t3.value end) as col9
  FROM table1 t1
  JOIN table2 t2
    ON t2.id_table1_user = t1.id
  JOIN table3 t3
    ON t2.id = t3.id_eva
  JOIN table4 t4
    ON t3.id_pro_q = t4.id
  JOIN table5 t5
    ON t4.id_pro = t5.id
  JOIN table6 t6
    ON t4.id_t6 = t6.id
  JOIN table7 t7
    ON t7.id_table1_user = t1.id
 WHERE t5.id = 151
 GROUP BY concat(t1.name, ' ', t1.surname)