select t.type , s_count=COUNT(name) from (select distinct [Type] from tb) t left join ( select distinct name,[TYPE] from tb k where not exists(select * from tb where k.type=TYPe and vlaue=0) ) k on t.type=k.typ group by t.type
--测试 create table user_info ( name nvarchar(20), type char(1), value int ) --插入数据 insert into user_info select '张三','a','0' union select '李四','a','1' union select '张三','a','1' union select '张三','a','0' union select '李四','b','0' union select 'li','b','1' union select 'zhang','b','0' union select 'ma','b','1' select COUNT(name) as count,type,value from user_info where value='1' group by type,value
结果: count type value 2 a 1 2 b 1希望对你有帮助
declare @tb table(name varchar(10),type varchar(5),Value int)insert into @tb select '张三','a','0' union all select '张三','a','1' union all select '李四','a','0' union all select '李四','a','1' union all select '李四','b','1' union all select '李四','b','1' union all select '张三','b','1' union all select '张三','b','1'select type, sum(value2) as value3 from (select name,type,min(Value) as value2 from @tb group by name,type ) t group by type /* type value3 ----- ----------- a 0 b 2(2 row(s) affected) */
你的这个 不是我想要的输出啊 表里面每个 type 都有几个子状态值 我想要的是统计每种type 的子状态值都为1的 总人数
declare @tb table(name varchar(10),type varchar(5),Value int)insert into @tb select '张三','a',0 union all select '张三','a',1 union all select '李四','a',0 union all select '李四','a',1 union all select '李四','b',1 union all select '李四','b',1 union all select '张三','b',1 union all select '张三','b',1 select t.type , s_count=COUNT(name) from (select distinct [Type] from @tb) t left join ( select distinct name,[TYPE] from @tb k where not exists(select * from @tb where k.type=TYPe and value=0) ) k on t.type=k.type group by t.type /* type s_count ----- ----------- a 0 b 2*/
if object_id('tb') is not null drop table tb create table tb([name] varchar(20), [type] char(1), [value] int)insert tb select '张三', 'a', 0 union all select '张三', 'a', 1 union all select '李四', 'a', 0 union all select '李四', 'a', 1 union all select '李四', 'b', 1 union all select '李四', 'b', 1 union all select '张三', 'b', 1 union all select '张三', 'b', 1 with t as ( select [name],[type], case when sum(case when [value]=1 then 1 else 0 end)=count([value]) then 1 else 0 end flag from tb group by [name],[type] )select type, sum(flag) number from t group by [type]type number ---- ----------- a 0 b 2(2 row(s) affected)
from (select distinct [Type] from tb) t left join
(
select distinct name,[TYPE]
from tb k
where not exists(select * from tb where k.type=TYPe and vlaue=0)
) k on t.type=k.typ
group by t.type
--测试
create table user_info
(
name nvarchar(20),
type char(1),
value int
)
--插入数据
insert into user_info
select '张三','a','0' union
select '李四','a','1' union
select '张三','a','1' union
select '张三','a','0' union
select '李四','b','0' union
select 'li','b','1' union
select 'zhang','b','0' union
select 'ma','b','1' select COUNT(name) as count,type,value from user_info where value='1' group by type,value
count type value
2 a 1
2 b 1希望对你有帮助
select '张三','a','0'
union all select '张三','a','1'
union all select '李四','a','0'
union all select '李四','a','1'
union all select '李四','b','1'
union all select '李四','b','1'
union all select '张三','b','1'
union all select '张三','b','1'select
type,
sum(value2) as value3
from
(select name,type,min(Value) as value2 from @tb group by name,type ) t
group by type
/*
type value3
----- -----------
a 0
b 2(2 row(s) affected)
*/
select '张三','a',0
union all select '张三','a',1
union all select '李四','a',0
union all select '李四','a',1
union all select '李四','b',1
union all select '李四','b',1
union all select '张三','b',1
union all select '张三','b',1
select t.type , s_count=COUNT(name)
from (select distinct [Type] from @tb) t left join
(
select distinct name,[TYPE]
from @tb k
where not exists(select * from @tb where k.type=TYPe and value=0)
) k on t.type=k.type
group by t.type
/*
type s_count
----- -----------
a 0
b 2*/
create table tb([name] varchar(20), [type] char(1), [value] int)insert tb
select '张三', 'a', 0 union all
select '张三', 'a', 1 union all
select '李四', 'a', 0 union all
select '李四', 'a', 1 union all
select '李四', 'b', 1 union all
select '李四', 'b', 1 union all
select '张三', 'b', 1 union all
select '张三', 'b', 1
with t as
(
select [name],[type],
case when sum(case when [value]=1 then 1 else 0 end)=count([value]) then 1 else 0 end flag
from tb group by [name],[type]
)select type, sum(flag) number from t group by [type]type number
---- -----------
a 0
b 2(2 row(s) affected)