mysql - 如何使用同一张表中的多个 SELECT 优化 SQL 查询？

Question

如何优化此 SQL 查询？

(SELECT SUM (same_field) FROM same_table WHERE other_same_field = '0') AS a,
(SELECT SUM (same_field) FROM same_table WHERE other_same_field = '1') AS b,
(SELECT SUM (same_field) FROM same_table WHERE other_same_field = '2') AS c

Answer 1

当你想在 MySQL 中进行数据透视时，你可以在 Case 语句上进行聚合

SELECT 
    SUM ( CASE WHEN other_same_field  = 0 THEN same_field ELSE 0 END) as A,
    SUM ( CASE WHEN other_same_field  = 1 THEN same_field ELSE 0 END) as B,
    SUM ( CASE WHEN other_same_field  = 2 THEN same_field ELSE 0 END) as C
FROM same_table
WHERE other_same_field IN (0, 1, 2)