select (select count(*) from table where a=1),(select count(*) from table where b=1),(select count(*) from table where c=1)
select * from (select count(*) aCount from table where a=1) a,(select count(*) bCount from table where b=1)b ,(select count(*) cCount from table where c=1) c
select top 1 (select count(a) from table where a = 1)aCount,(select count(b) from table where b = 1)bCount,(select count(c) from table where c = 1)cCount from table
select count(*) from table where a =1 select count(*) from table where b =1 select count(*) from table where c =1
也就是说把select count(*) from table where a =1 select count(*) from table where b =1 select count(*) from table where c =1和成一条语句~~~并且显示他们查询的结果~~
select count(*) as [A列1的个数] from table where a =1 union all select count(*)[B列1的个数] from table where b =1 union all select count(*)[C列1的个数] from table where c =1
--准备数据 declare @table table( a int, b int, c int )insert into @table values(1,1,1) insert into @table values(1,0,1) insert into @table values(1,1,0) insert into @table values(0,1,1) insert into @table values(1,0,0) insert into @table values(0,1,0) insert into @table values(0,0,1)select * from @table--方法1,个人比较喜欢这个 declare @counta int declare @countb int declare @countc intset @counta=0 set @countb=0 set @countc=0select @counta=@counta+(case a when 1 then 1 else 0 end) ,@countb=@countb+(case b when 1 then 1 else 0 end) ,@countc=@countc+(case c when 1 then 1 else 0 end) from @table select @counta,@countb,@countc--方法2 select (select count(a) from @table where a=1),(select count(b) from @table where b=1),(select count(c) from @table where c=1)--方法3 select ta.a,tb.b ,tc.c from ( select 1 as k, count(a) as a from @table where a=1 ) ta inner join (select 1 as k, count(b) as b from @table where b=1) tb on ta.k=tb.k inner join (select 1 as k, count(c) as c from @table where c=1) tc on ta.k=tc.k
SQL> select * from test1; A B C ---------- ---------- ---------- 1 1 1 1 2 3 3 1 1SQL> select count(*) from test1 where A=1 2 union all 3 select count(*) from test1 where B=1 4 union all 5 select count(*) from test1 where C=1; COUNT(*) ---------- 2 2 2
select count(*) from table where b =1
select count(*) from table where c =1
select count(*) from table where b =1
select count(*) from table where c =1和成一条语句~~~并且显示他们查询的结果~~
union all select count(*)[B列1的个数] from table where b =1
union all select count(*)[C列1的个数] from table where c =1
declare @table table(
a int,
b int,
c int
)insert into @table values(1,1,1)
insert into @table values(1,0,1)
insert into @table values(1,1,0)
insert into @table values(0,1,1)
insert into @table values(1,0,0)
insert into @table values(0,1,0)
insert into @table values(0,0,1)select * from @table--方法1,个人比较喜欢这个
declare @counta int
declare @countb int
declare @countc intset @counta=0
set @countb=0
set @countc=0select @counta=@counta+(case a when 1 then 1 else 0 end)
,@countb=@countb+(case b when 1 then 1 else 0 end)
,@countc=@countc+(case c when 1 then 1 else 0 end)
from @table
select @counta,@countb,@countc--方法2
select (select count(a) from @table where a=1),(select count(b) from @table where b=1),(select count(c) from @table where c=1)--方法3
select ta.a,tb.b ,tc.c
from (
select 1 as k, count(a) as a from @table where a=1 ) ta
inner join (select 1 as k, count(b) as b from @table where b=1) tb on ta.k=tb.k
inner join (select 1 as k, count(c) as c from @table where c=1) tc on ta.k=tc.k
---------- ---------- ----------
1 1 1
1 2 3
3 1 1SQL> select count(*) from test1 where A=1
2 union all
3 select count(*) from test1 where B=1
4 union all
5 select count(*) from test1 where C=1; COUNT(*)
----------
2
2
2