在条件相同的表中为具有相同条件的多重查询编写单个查询

小编典典

在条件相同的表中为具有相同条件的多重查询编写单个查询

sql

select column1,column2,column3 from table1 where column5=0 and column6=0
select column1,column2,column3 from table1 where column5!=0 and column6!=0

这是两个sql语句，它们从同一table1中读取数据。有没有办法编写一个返回相同数据的查询？

我想在单个查询中获得（column5 = 0 AND column6 = 0）和（column5！= 0 AND column6！= 0）的单个结果。

as example:
select column1 as c1,column2 as c2,column3 as c3 from table1 where column5=0 and  column6=0
union
select column1 as c1_,column2 as c2_,column3 as c3_ from table1 where column5!=0 and column6!=0

阅读 139

2021-05-16

共1个答案

小编典典

select 
sum(
 case when (column5=0 or column6=0 ) 
 then 1 else 0 end 
   ) as c1 ,

 sum(
 case when (column5=0 or column6=0 ) 
 then 1 else 0 end 
   ) as c2 ,
 sum(
 case when (column5=0 or column6=0 ) 
 then 1 else 0 end 
  ) as c3 ,

sum(
 case when (column5!=0 or column6!=0 ) 
 then 1 else 0 end 
) as c1_ ,

  sum(
 case when (column5!=0 or column6!=0 ) 
 then 1 else 0 end 
  ) as c2_ ,
 sum(
 case when (column5!=0 or column6!=0)  
 then 1 else 0 end 
   ) as c3_ ,

from table1

2021-05-16