logo标签列表

如何从同一张表中使用不同的where子句两次获取sum()？

sql
4
SELECT 
    sum(qty) as accept,
    (SELECT sum(qty) 
     FROM pile 
     WHERE pf=false) as reject 
FROM pile 
WHERE pf=true;

这是我目前使用的SQL查询语句，但我猜想它不是最佳实践？ 我之前用过另一种选择：SELECT sum(qty) FROM pile GROUP BY pf 但我需要将它们作为列而不是行。

是否有其他解决方案？

5个回答
8
单次通过表格。 
SELECT 
    sum(CASE WHEN pf = TRUE THEN qty ELSE 0 END) as accept,
    sum(CASE WHEN pf = FALSE THEN qty ELSE 0 END) as reject
FROM pile;
2
SELECT pileTrue.sumTrue as accept, pileFalse.sumFalse as reject
FROM
(SELECT sum(qty) sumFalse FROM pile WHERE pf=false) as pileFalse,
(SELECT sum(qty) sumTrue  FROM pile WHERE pf=true ) as pileTrue
1

在我看来，这并没有更好，但更易读。

SELECT
accept = (SELECT sum(qty) FROM pile WHERE pf = true),
reject = (SELECT sum(qty) FROM pile WHERE pf = false)
0
  check it



  select max(accept), max(v2 ) 
    from
    (
              SELECT 
                    sum(qty) as accept,null v2  
                FROM pile 
                WHERE pf=true
                union
                 SELECT null accept,sum(qty) v2  
                     FROM pile 
                     WHERE pf=false
    )
    group by accept,v2
0
SELECT (SELECT SUM(qty)
          FROM pile
         WHERE pf = true) AS accept,
       (SELECT SUM(qty)
          FROM pile
         WHERE pf = false) AS reject
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接