SQL中聚合函数+Case的使⽤
怎么把两个相同的sql语句⽤多表连接的⽅法给连接起来呢？
⽐如users表中，state字段，表⽰⽤户状态，1表⽰可⽤，0表⽰不可⽤，如何在⼀张表中显⽰出可⽤⼈数与不可⽤⼈数？表中还有字段userid,username1、使⽤ sum + case
declare @t table
(userid int,username varchar( 8),state int) insert into @t
select 1, ' zhangsan ', 0 union all select 2, ' lisi ', 1 union all
select 3, ' wangwu ', 1 union all select 4, ' liuliu ', 0 union all select 5, ' chenqi ', 0 union all select 6, ' wuba ', 0
declare @可⽤⼈数 int, @不可⽤⼈数 int select
@可⽤⼈数 = sum( case state when 0 then 1 else 0 end), @不可⽤⼈数 = sum( case state when 1 then 1 else 0 end) from @t
select *, @可⽤⼈数 as 可⽤⼈数, @不可⽤⼈数 as 不可⽤⼈数 from @t /*
userid username state 可⽤⼈数 不可⽤⼈数----------- -------- ----------- ----------- -----------1 zhangsan 0 4 22 lisi 1 4 2
3 wangwu 1 4 24 liuliu 0 4 25 chenqi 0 4 26 wuba 0 4 2*/
2、使⽤count + case
select count( case when state = 1 then 1 else 0 end) as ' 可⽤ ', count( case when state = 0 then 1 else 0 end) as ' 不可⽤ ', count( 1) as ' 总⼈数 ' from users
