sql - 我怎样才能重写这些子查询

Question

在这样一个巨大的 SQL 查询中:

SELECT
  ...
  (SELECT COUNT(*) FROM table1 WHERE field = 'bar' AND table1.table0_id = table0.id)
  (SELECT COUNT(*) FROM table1 WHERE field = 'foobar' AND table1.table0_id = table0.id)
  (SELECT COUNT(*) FROM table1 WHERE field = 'foo' AND table1.table0_id = table0.id)
  ...
FROM table0;

有没有办法避免对 table0 的每次迭代运行 3 个查询？

谢谢

Answer 1

您可以使用条件聚合来简化您的查询

这是正确的方法

SELECT coalesce(b_count,0),
       coalesce(fb_count,0),
       coalesce(f_count,0)
FROM   table0 
       LEFT JOIN (SELECT table1.table0_id, 
                         Count(CASE WHEN field = 'bar' THEN 1 END) AS b_count, 
                         Count(CASE WHEN field = 'foobar' THEN 1 END) AS fb_count, 
                         Count(CASE WHEN field = 'foo' THEN 1 END) AS f_count, 
                  FROM   table1 
                  WHERE  field IN ( 'bar', 'foobar', 'foo' ) 
                  GROUP  BY table1.table0_id) table1 
              ON table1.table0_id = table0.id