合并两个查询为一个。

huangapple go评论114阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2023年8月11日 02:12:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/76878337.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定