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 1B表
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语句怎么写,谢谢. 没有分了,麻烦大家了.
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 1B表
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语句怎么写,谢谢. 没有分了,麻烦大家了.
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=0 then 1 else 0 end),
state2=sum(case when c_state=0 then 1 else 0 end) from A group by c_reg_id)A
on B.r_uid=A.c_reg_id
sum(case c_state when 0 then 1 else 0 end),
sum(case c_state when 1 then 1 else 0 end),
sum(case c_state when 2 then 1 else 0 end)
from 表
group by c_reg_id
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',1create 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 行)
*/
B.r_uid,
SUM(Case A.c_state When 0 Then 1 Else 0 End) As state0,
SUM(Case A.c_state When 1 Then 1 Else 0 End) As state1,
SUM(Case A.c_state When 2 Then 1 Else 0 End) As state2
From
A
Inner Join
B
On A.c_reg_id = B.r_uid
Group By
B.r_uid
Order By
Count(B.r_uid), B.r_uid
B.r_uid,
SUM(Case A.c_state When 0 Then 1 Else 0 End) As state0,
SUM(Case A.c_state When 1 Then 1 Else 0 End) As state1,
SUM(Case A.c_state When 2 Then 1 Else 0 End) As state2
From
A
Inner Join
B
On A.c_reg_id = B.r_uid
Group By
B.r_uid
Order By
(state0+state1+state2)