时间戳

首页 > 解决方案 > 如何替换重复的子查询？

postgresql - 如何替换重复的子查询？

问题描述

我有一个查询

SELECT  name AS name, 
        count(group_name) AS all_test_cases,
        (SELECT count(*) FROM test_cases AS tc2 WHERE tc2.group_name = tg.name AND status = 'OK'  ) AS passed_test_cases,
        tg.test_value * (SELECT count(*) FROM test_cases AS tc2 WHERE tc2.group_name = name AND status = 'OK') AS total_value
  FROM test_groups AS tg 
  LEFT JOIN test_cases AS tc
     ON tg.name = tc.group_name
  GROUP BY name, test_value      
  ORDER BY total_value DESC, name ASC

如何替换重复的子查询：

tg.test_value * (SELECT count(*) FROM test_cases AS tc2 WHERE tc2.group_name = name AND status = 'OK') AS total_value

在没有存储过程的情况下在 postgres 中使用更有效的东西？

标签： postgresql

解决方案

这对你有用吗？

with cte_test_cases as (
  SELECT group_name, 
         count(*) filter (where status = 'OK') as passed_test_cases,
         count(*) as all_test_cases
    FROM test_cases  
   GROUP BY group_name
)
SELECT tg.name, 
       tc.all_test_cases,
       tc.passed_test_cases,
       tg.test_value * tc.passed_test_cases AS total_value
  FROM test_groups AS tg 
  LEFT JOIN cte_test_cases AS tc
         ON tg.name = tc.group_name
  ORDER BY total_value DESC, name ASC

推荐阅读