合并（结果集）的“两个查询与聚合和组”由“可能？ [英] Is combining (result sets) of "two queries with aggregate and group by" possible?

问题描述

1

Select id,count(*) as totalX FROM my_table WHERE x_factor = 1 GROUP BY id

结果集：

ResultSet:

 id        totalX
 --------- --------------
         9             34
        10              6
        11             21
        12              3  

2

Select id,count(*) as totalY FROM my_table WHERE y_factor = 1 GROUP BY id

ResultSet 2：

ResultSet 2:

 id        totalY
 --------- --------------
         9             334
        10              56
        11             251
        12              93 

有没有办法实现这一点：

 id        totalX        totalY         
 --------- --------------  --------------  
         9             34              334 
        10              6               56 
        11             21              251 
        12              3               93 

我希望在RHEL 5上使用Sybase 12.5的解决方案，我也想知道这是否可以在任何其他数据库系统中使用。

I would like the solution for Sybase 12.5 on RHEL 5, also I would like to know if this is possible in any other database system.

---感谢您的回答 -

---thanks for the answer(s)--

Comparing EXECUTION TIME: (For a certain query) 
 Query 1:
Execution Time 61.
SQL Server cpu time: 6100 ms.  SQL Server elapsed time: 12133 ms.

Query 2:
Execution Time 53.
SQL Server cpu time: 5300 ms.  SQL Server elapsed time: 12090 ms.

Query X(1+2):
Execution Time 84.
SQL Server cpu time: 8400 ms.  SQL Server elapsed time: 21456 ms.

推荐答案

通过使用CASE / WHEN作为列和求和一个基于true / false的1或0，你可以在同一个查询中得到两个...此外，如果你想在另一个列中的某个值的总和，那么你可以做同样的事情......只需将其替换为真值而不是1.

By using a CASE/WHEN for the column and summing a 1 or 0 based on true/false, you can get both in the same query... Additionally, you could do the same thing if you wanted the sum of some value in another column... just substitute that for the true value instead of 1.

select 
      id,
      sum( CASE WHEN x_factor = 1 THEN 1 ELSE 0 END ) as X_Count, 
      sum( CASE WHEN y_factor = 1 THEN 1 ELSE 0 END ) as Y_Count
  from
      yourTable
  group by
      id

这篇关于合并（结果集）的“两个查询与聚合和组”由“可能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！