我有两个表,一个data表存文章,一个user表存人员信息
类似这样
data表
charid(文章id) input(上报人) status(是否发表1为发表、0为上报)
1 AA 0
2 BB 1
3 CC 1
4 DD 1
5 EE 1
6 FF 0
7 GG 0user
userid(用户id) corpid(公司名) headuser(上级人员)
AA IBM null
BB null AA
CC SUN null
DD null CC
EE ABC null
FF null EE
GG LTD null 要得到的视图是
公司名 上报数量 发表数量
IBM 2 1
SUN 2 2
ABC 2 1
LTD 1 0这个sql怎么做?
类似这样
data表
charid(文章id) input(上报人) status(是否发表1为发表、0为上报)
1 AA 0
2 BB 1
3 CC 1
4 DD 1
5 EE 1
6 FF 0
7 GG 0user
userid(用户id) corpid(公司名) headuser(上级人员)
AA IBM null
BB null AA
CC SUN null
DD null CC
EE ABC null
FF null EE
GG LTD null 要得到的视图是
公司名 上报数量 发表数量
IBM 2 1
SUN 2 2
ABC 2 1
LTD 1 0这个sql怎么做?
这样
公司名 上报数量 发表数量
SUN 2 2
IBM 2 1
ABC 2 1
LTD 1 0
isnull(b.corpid,c.corpid) as 公司名,
count(distinct a.charid) as 上报数量,
sum(case status when 1 then 1 else 0 end) as 发表数量
from data a inner join user b
on a.input=b.userid
left join user c on b.headuser=c.userid
group by isnull(b.corpid,c.corpid)
isnull(b.corpid,c.corpid) as 公司名,
count(distinct a.charid) as 上报数量,
sum(case status when 1 then 1 else 0 end) as 发表数量
from data a inner join user b
on a.input=b.userid
left join user c on b.headuser=c.userid
group by isnull(b.corpid,c.corpid)
order by count(distinct a.charid) desc ,sum(case status when 1 then 1 else 0 end) desc
C.corpid,
SUM(Case D.status When 0 Then 1 Else 0 End) As 上报数量,
SUM(Case D.status When 1 Then 1 Else 0 End) As 发表数量
From
(
Select corpid, userid From [user] Where headuser Is Null
Union
Select A.corpid, B.userid From [user] A Inner Join [user] B On A.userid = B.headuser
) C
Left Join
data D
On C.userid =D.input
Group By
C.corpid
PS:
結果好象有問題
Create Table data
(charid Int,
input Varchar(10),
status Bit)
Insert data Select 1, 'AA', 0
Union All Select 2, 'BB', 1
Union All Select 3, 'CC', 1
Union All Select 4, 'DD', 1
Union All Select 5, 'EE', 1
Union All Select 6, 'FF', 0
Union All Select 7, 'GG', 0Create Table [user]
(userid Varchar(10),
corpid Varchar(10),
headuser Varchar(10))
Insert [user] Select 'AA', 'IBM', null
Union All Select 'BB', null, 'AA'
Union All Select 'CC', 'SUN', null
Union All Select 'DD', null, 'CC'
Union All Select 'EE', 'ABC', null
Union All Select 'FF', null, 'EE'
Union All Select 'GG', 'LTD', null
GO
--測試
Select
C.corpid,
SUM(Case D.status When 0 Then 1 Else 0 End) As 上报数量,
SUM(Case D.status When 1 Then 1 Else 0 End) As 发表数量
From
(
Select corpid, userid From [user] Where headuser Is Null
Union
Select A.corpid, B.userid From [user] A Inner Join [user] B On A.userid = B.headuser
) C
Left Join
data D
On C.userid =D.input
Group By
C.corpid
GO
--刪除測試環境
Drop Table data, [user]
--結果
/*
corpid 上报数量 发表数量
ABC 1 1
IBM 1 1
LTD 1 0
SUN 0 2
*/
Select
C.corpid,
Count(C.corpid) As 上报数量,
SUM(Case D.status When 1 Then 1 Else 0 End) As 发表数量
From
(
Select corpid, userid From [user] Where headuser Is Null
Union
Select A.corpid, B.userid From [user] A Inner Join [user] B On A.userid = B.headuser
) C
Left Join
data D
On C.userid =D.input
Group By
C.corpid
Create Table data
(charid Int,
input Varchar(10),
status Bit)
Insert data Select 1, 'AA', 0
Union All Select 2, 'BB', 1
Union All Select 3, 'CC', 1
Union All Select 4, 'DD', 1
Union All Select 5, 'EE', 1
Union All Select 6, 'FF', 0
Union All Select 7, 'GG', 0Create Table [user]
(userid Varchar(10),
corpid Varchar(10),
headuser Varchar(10))
Insert [user] Select 'AA', 'IBM', null
Union All Select 'BB', null, 'AA'
Union All Select 'CC', 'SUN', null
Union All Select 'DD', null, 'CC'
Union All Select 'EE', 'ABC', null
Union All Select 'FF', null, 'EE'
Union All Select 'GG', 'LTD', null
GO
--測試
Select
C.corpid,
Count(C.corpid) As 上报数量,
SUM(Case D.status When 1 Then 1 Else 0 End) As 发表数量
From
(
Select corpid, userid From [user] Where headuser Is Null
Union
Select A.corpid, B.userid From [user] A Inner Join [user] B On A.userid = B.headuser
) C
Left Join
data D
On C.userid =D.input
Group By
C.corpid
GO
--刪除測試環境
Drop Table data, [user]
--結果
/*
corpid 上报数量 发表数量
ABC 2 1
IBM 2 1
LTD 1 0
SUN 2 2
*/
如果公司为空也要看headuser
------
語句裡有考慮這個的。
/* 创建表
Create Table data
(charid Int,
input Varchar(10),
status Bit)
Insert data Select 1, 'AA', 0
Union All Select 2, 'BB', 1
Union All Select 3, 'CC', 1
Union All Select 4, 'DD', 1
Union All Select 5, 'EE', 1
Union All Select 6, 'FF', 0
Union All Select 7, 'GG', 0Create Table [user]
(userid Varchar(10),
corpid Varchar(10),
headuser Varchar(10))
Insert [user] Select 'AA', 'IBM', null
Union All Select 'BB', null, 'AA'
Union All Select 'CC', 'SUN', null
Union All Select 'DD', null, 'CC'
Union All Select 'EE', 'ABC', null
Union All Select 'FF', null, 'EE'
Union All Select 'GG', 'LTD', null
*/Select
C.corpid,
--SUM(Case D.status When 0 Then 1 Else 0 End)
Count(*) As 上报数量,
SUM(Case D.status When 1 Then 1 Else 0 End) As 发表数量
From
(
Select corpid, userid From [user] Where headuser Is Null
Union All
Select A.corpid, B.userid From [user] B Inner Join [user] A on A.userid = B.headuser and B.corpid is Null
) C
Left Join
data D
On C.userid =D.input
Group By
C.corpid即可