java - 在 SQL 中获取多个计数

Question

我希望我的 sql 查询返回所有活动和过去案例的计数。我试过这样的查询，

SELECT 
    sum(case when need_By > sysdate and status not in (30,40,50) then 1 else 0 end) AS active,
    sum(case when need_by < sysdate and status not in (30,40,50) then 1 else 0 end) AS past
FROM discrepancy
GROUP BY id;

这给了我如下输出：

ACTIVE PAST
0      1
1      0
0      0
0      1
0      1

我期待的是，

ACTIVE PAST
  1      3

我应该如何更改查询以获取活动和过去记录的计数。此外，此查询将在休眠状态下执行，因此要确保 createNativeQuery 有效。

标签： javasqloraclehibernate

Answer 1

删除GROUP BY：

SELECT sum(case when need_By > sysdate and status not in (30,40,50) then 1 else 0 end) AS active,
       sum(case when need_by < sysdate and status not in (30,40,50) then 1 else 0 end) AS past
FROM discrepancy;

MySQL 也有一个方便的简写符号，所以你可以直接计算布尔表达式：

SELECT SUM(need_By > sysdate and status not in (30,40,50)) AS active,
       SUM(need_by < sysdate and status not in (30,40,50)) AS past
FROM discrepancy;

最后，您可以将 移至可以提高性能NOT IN的子句：WHERE

SELECT SUM(need_By > sysdate) AS active,
       SUM(need_by < sysdate) AS past
FROM discrepancy
WHERE status NOT IN (30, 40, 50);

编辑：

在 Oracle 中，最后一个是：

SELECT SUM(CASE WHEN need_By > sysdate THEN 1 ELSE 0 END) AS active,
       SUM(CASE WHEN need_by < sysdate THEN 1 ELSE 0 END) AS past
FROM discrepancy
WHERE status NOT IN (30, 40, 50);