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
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,0select 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(所影响的行数为 2 行)
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 #tempselect 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
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 #(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, 0select a, count(a), sum(case when b=1 then 1 else 0 end) ,sum(case when c=1 then 1 else 0 end) from # group by a
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, 0select 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 desca A字段的记录条数 B字段值为1的条数 C字段值为0的条数 ----- ----------- ----------- ----------- t 2 1 2 h 3 2 1
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,0select 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(所影响的行数为 2 行)
(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 #tempselect 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
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
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, 0select a, count(a), sum(case when b=1 then 1 else 0 end) ,sum(case when c=1 then 1 else 0 end) from #
group by a
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, 0select 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 desca A字段的记录条数 B字段值为1的条数 C字段值为0的条数
----- ----------- ----------- -----------
t 2 1 2
h 3 2 1
你的回答没有加条件,我试了不行.
rookie_one(流氓会武术,谁都挡不住)
没有达到我想要的结果.
我的结果是对 记录值为 : t和h的做统计, 我所要的是:
t的名称(不重复),t的所有记录条数, t的B字段值为1的记录条数,t的C字段值为0记录条数
h名称(不重复),h所有记录条数, hB字段值为1的记录条数,hC字段值为0记录条数
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 adrop table #table
a a_count b_1 c_0
----------------------------
t 2 1 2
h 3 2 1(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 2 行)