我有一表Table1 字段名为: A B C D E
字段值为: t 1 0 0 1
h 0 1 0 1
t 0 0 1 0
h 1 0 0 1
h 1 1 1 0
... ... ... ... ...
我想要的结果为: A字段的记录条数 B字段值为1的条数 C字段值为0的条数
t 2 1 2
h 3 2 1
... ... ... ...
用一条SQL语句统计.
请高手帮帮忙.小弟对SQL语言掌握不好.谢谢了!
...全文
2679打赏收藏
请教一下,如何写做下面较复杂的统计汇总SQL语句?
我有一表Table1 字段名为: A B C D E 字段值为: t 1 0 0 1 h 0 1 0 1 t 0 0 1 0 h 1 0 0 1 h 1 1 1 0 ... ... ... ... ... 我想要的结果为: A字段的记录条数 B字段值为1的条数 C字段值为0的条数 t 2 1 2 h 3 2 1 ... ... ... ... 用一条SQL语句统计. 请高手帮帮忙.小弟对SQL语言掌握不好.谢谢了!
select a='t',b=1,c=0,d=0,e=1 into #table
insert into #table values('h',0,1,0,1)
insert into #table values('t',0,0,1,0)
insert into #table values('h',1,0,0,0)
insert into #table values('h',1,1,1,1)
select a,count(a) a_count,sum(b) b_1,count(c)-sum(c) c_0 from #table group by a
drop table #table
a a_count b_1 c_0
----------------------------
t 2 1 2
h 3 2 1
create table #(a varchar(5),b int,c int ,d int,e int)
insert into # select 't' , 1, 0, 0, 1
insert into # select 'h', 0, 1, 0, 1
insert into # select 't', 0, 0, 1, 0
insert into # select 'h', 1, 0, 0, 1
insert into # select 'h', 1, 1, 1, 0
select a, count(a) A字段的记录条数, sum(case when b=1 then 1 else 0 end) B字段值为1的条数 ,sum(case when c=0 then 1 else 0 end) C字段值为0的条数 from #
group by a
order by a desc
a A字段的记录条数 B字段值为1的条数 C字段值为0的条数
----- ----------- ----------- -----------
t 2 1 2
h 3 2 1
select tba.a,
A字段的记录条数=(select count* from Table1 where a=tba.a),
B字段值为1的条数=(select count* from Table1 where a=tba.a and b=1),
C字段值为0的条数=(select count* from Table1 where a=tba.a and c=0)
from Table1 as tba group by tba.a
create table #temp
(A varchar(50),
B varchar(50),
C varchar(50)
)
insert into #temp
select 't','1','0' union all select 'h','0','1' union all select 't','0','0' union all select 'h','1','0' union all select 'h','1','1'
select * from #temp
select t.a,
(select count(*) from #temp where a=t.a) A字段的记录条数,
(select count(*) from #temp where a=t.a and b=1) B字段值为1的条数,
(select count(*) from #temp where a=t.a and c=0) C字段值为0的条数
from #temp t
------------
t 2 1 2
h 3 2 1
t 2 1 2
h 3 2 1
h 3 2 1
declare @Table1 table(A char(1),B int,C int,D int,E int)
insert @Table1
select 't',1,0,0,1
union all
select 'h',0,1,0,1
union all
select 't',0,0,1,0
union all
select 'h',1,0,0,1
union all
select 'h',1,1,1,0
select count(*) a,sum(b) b,sum(c-1)*-1 c from @Table1
group by a
-----------------------------------------------
(所影响的行数为 5 行)
a b c
----------- ----------- -----------
3 2 1
2 1 2
select tba.a,
A字段的记录条数=(select count* from Table1 where a=tba.a),
B字段值为1的条数=(select count* from Table1 where a=tba.a and b=1),
C字段值为0的条数=(select count* from Table1 where a=tba.a and c=0)
from Table1 as tba