我的代码中包含此SQL，子查询的重复使我担心，这样做的效率不尽如人意。
我的数据库有一个features，responses和participants的表。参与者会为每个功能评分（D，P，B，I，R，Q中的一个）。
SELECT f.id, f.name,
(SELECT COUNT(r.id) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS nr_r,
(SELECT SUM(r.is_D) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_D,
(SELECT SUM(r.is_P) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_P,
(SELECT SUM(r.is_B) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_B,
(SELECT SUM(r.is_I) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_I,
(SELECT SUM(r.is_R) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_R,
(SELECT SUM(r.is_Q) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_Q,
(SELECT (is_D + is_P)/(is_D + is_P + is_B + is_I)) as yay,
(SELECT (is_P + is_B)/(is_D + is_P + is_B + is_I)) as boo
FROM features f
WHERE f.is_deleted IS NULL AND f.id_survey=:id_project
ORDER BY f.id ASC;
该查询的输出是总计表，看起来像
577 App registration 989 36 21 38 201 42 6 0.1926 0.1993
578 Login PIN 989 279 118 137 394 41 20 0.4278 0.2748
579 Manage all services 989 287 207 127 331 23 14 0.5189 0.3508
580 Rewards 989 344 157 64 386 19 19 0.5268 0.2324
581 Offers 989 226 93 37 542 72 19 0.3552 0.1448
查询当前需要4.4387秒才能运行具有989个参与者，14个功能（因此有13846个个人评分）的数据集。肯定会发臭。
有没有编写该查询的更有效方法？
这对GROUP BY有用吗？
最佳答案
如果我了解您的查询权利，那么您在每个子查询中都具有相同的谓词，那么您可以轻松地用联接替换所有子选择并进行分组依据。聚合（yay和boo）的计算是在外部级别进行的：
SELECT id, name, nr_r, is_D, ...
, (is_D + is_P)/(is_D + is_P + is_B + is_I) yay
, (is_P + is_B)/(is_D + is_P + is_B + is_I) boo
FROM (
SELECT f.id, f.name
, count(r.id) AS nr_r
, sum(r.is_D) as is_D
, ...
FROM features f
LEFT JOIN responses r
ON r.id_feature = f.id
AND r.is_deleted IS NULL
LEFT JOIN participants p
ON r.id_participant = p.id
AND p.is_ignored IS NULL
AND p.category LIKE :p_category
WHERE f.is_deleted IS NULL
AND f.id_survey=:id_project
GROUP BY f.id, f.name
) AS T
ORDER BY ...