英文:
getting multiple counts in SQL
问题
我希望我的 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
我应该如何更改查询以获取活动和过去记录的计数。此查询将在 Hibernate 中执行，因此希望确保 createNativeQuery 能够正常工作。
英文:
I want my sql query to return me count of all active and past cases. I tried a query like this,
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;
This gives me output as follows:
ACTIVE PAST
0 1
1 0
0 0
0 1
0 1
What i am expecting is ,
ACTIVE PAST
1 3
How should I change the query to get the count of active and past records. Also this query will be executed in hibernate and so want to make sure the createNativeQuery works .
答案1
得分: 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);
英文:
Remove the 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 also has a convenient short-hand notation so you can count the boolean expressions directly:
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;
And finally, you can move the NOT IN to a WHERE clause which could improve performance:
SELECT SUM(need_By > sysdate) AS active,
SUM(need_by < sysdate) AS past
FROM discrepancy
WHERE status NOT IN (30, 40, 50);
EDIT:
In Oracle, the last would be:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论