请教一个分类统计的问题.不用存储过程!!!!!!!急!!!!!!!!!
A表
c_id c_name c_reg_id c_state
1 aaaa user1 1
2 bbbb user2 0
3 cccc user1 1
4 dddd user3 2
5 eeee user3 1
B表
r_id r_uid
1 user1
2 user2
3 user3
想得到如下结果
r_uid state0 state1 state2
user1 0 2 0
user2 1 0 0
user3 0 1 1
就是说state0,state1,state2是对每个用户三个状态数量的统计,然后按总数量(state0+state1+state2)排序.
请问SQL语句怎么写,谢谢. 没有分了,麻烦大家了.
有误,修改下
drop table A,B
go
create table A(c_id int,c_name varchar(10),c_reg_id varchar(10),c_state int)
insert into A
select 1, ‘aaaa ‘, ‘user1 ‘,1
union all select 2, ‘bbbb ‘, ‘user2 ‘,0
union all select 3, ‘cccc ‘, ‘user1 ‘,1
union all select 4, ‘dddd ‘, ‘user3 ‘,2
union all select 5, ‘eeee ‘, ‘user3 ‘,1
create table B(r_id int,r_uid varchar(10))
insert into B
select 1, ‘user1 ‘
union all select 2, ‘user2 ‘
union all select 3, ‘user3 ‘
select B.r_uid,
A.state0,
A.state1,
A.state2
from B
left join (select c_reg_id,
state0=sum(case when c_state=0 then 1 else 0 end),
state1=sum(case when c_state=1 then 1 else 0 end),
state2=sum(case when c_state=2 then 1 else 0 end) from A group by c_reg_id)A
on B.r_uid=A.c_reg_id
/*
r_uid state0 state1 state2
———- ———– ———– ———–
user1 0 2 0
user2 1 0 0
user3 0 1 1
（所影响的行数为 3 行）
*/
