英文:
Combine two queries into one
问题
In the first query, I need to have the count for each type regardless of whether that's read or not (unread can be true or false).
在第一个查询中,我需要获取每种类型的计数,无论是否已读(未读可以为true或false)。
In the second query, I need the count for all types only for when unread is false.
在第二个查询中,我只需要计算所有类型的计数,仅当未读为false时。
Currently I am calling the database two times, once for the first query and once for the second one. Is there any way that I can call the database only once to get the results for both queries?
目前我需要两次调用数据库,一次用于第一个查询,一次用于第二个查询。有没有办法可以只调用一次数据库来获取两个查询的结果?
Please note that my queries are in HQL, I wrote the SQL version of them b/c I think its easier; so ideally I need to have a solution that works for HQL too. Thanks.
请注意,我的查询是使用HQL编写的,我编写了它们的SQL版本,因为我认为这样更容易;所以理想情况下,我需要一个适用于HQL的解决方案。谢谢。
英文:
In the first query, I need to have the count for each type regardless of whether that’s read or not (unread can be true or false). In the second query, I need the count for all types only for when unread is false. Currently I am calling the database two times, once for the first query and once for the second one. Is there anyway that I can call the database only once to get the results for both queries?
Please note that my queries are in HQL, I wrote the SQL version of them b/c I think its easier; so ideally I need to have a solution that works for HQL too. Thanks.
--Need the count regardless if is_read is true or false.
--In order to get total of all rows, I add up each total for each group in the Java code:
select type_id as type, count(distinct ta.id) as totalId
from tableA as ta
inner join tableB tb on ta.id = tb.id
inner join tableC tc on tb.col1= tc.col2
where tc.col2= 6
and tb.is_deleted = 'N'
group by type_id 
--Need the count only for is_read = false.
--This query gives the total for all unread rows so no group by needed:
select count(distinct ta.id) as totalId
from tableA as ta
inner join tableB tb on ta.id =tb.id
inner join tableC tc on tb.col1 = tc.col2
where tc.col2= 6
and tb.is_deleted = 'N'
and tb.is_read = 'N'
答案1
得分: 0
你可以尝试这个:
SELECT
ta.typeId AS 类型,
COUNT(DISTINCT ta.id) AS 总数,
COUNT(DISTINCT CASE WHEN tb.isRead = 'N' THEN ta.id END) AS 未读总数
FROM
表A ta
INNER JOIN
ta.表B tb
INNER JOIN
tb.表C tc
WHERE
tc.col2 = 6 AND tb.isDeleted = 'N'
GROUP BY
ta.typeId;
英文:
you can try this
SELECT
ta.typeId AS type,
COUNT(DISTINCT ta.id) AS totalId,
COUNT(DISTINCT CASE WHEN tb.isRead = 'N' THEN ta.id END) AS unreadTotalId
FROM
TableA ta
INNER JOIN
ta.tableB tb
INNER JOIN
tb.tableC tc
WHERE
tc.col2 = 6 AND tb.isDeleted = 'N'
GROUP BY
ta.typeId;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论