刚才那个帖子,我想再加一列!

[eyan_810810]

我有两个表，一个data表存文章，一个user表存人员信息
类似这样
data表
charid（文章id） input(上报人) status（1发表、0上报）topicid(栏目id)
1 AA 0 硬件
2 BB 1 软件
3 CC 1 软件
4 DD 1 软件
5 EE 1 硬件
6 FF 0 耗材
7 GG 0 周边

user
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 1 1 软件
IBM 1 0 硬件
SUN 2 2 软件
ABC 1 1 硬件
ABC 1 0 耗材
LTD 1 0 周边

[wgqqgw]

select t1.corpid,
sum(case status when 0 then 1 else 0 end) as 上报数量,
sum(case status when 1 then 1 else 0 end) as 发表数量,
t1.topicid
from user t1, data t2
where t1.userid=t2.input
group by t1.corpid, t1.topicid

[paoluo]

Select
C.corpid,
Count(C.corpid) As 上报数量,
SUM(Case D.status When 1 Then 1 Else 0 End) As 发表数量,
D.topicid
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,
D.topicid

[paoluo]

--創建測試環境
Create Table data
(charid Int,
input Varchar(10),
status Bit,
topicid Nvarchar(10))
Insert data Select 1, 'AA', 0, N'硬件'
Union All Select 2, 'BB', 1, N'软件'
Union All Select 3, 'CC', 1, N'软件'
Union All Select 4, 'DD', 1, N'软件'
Union All Select 5, 'EE', 1, N'硬件'
Union All Select 6, 'FF', 0, N'耗材'
Union All Select 7, 'GG', 0, N'周边'

Create 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 As 公司名,
Count(C.corpid) As 上报数量,
SUM(Case D.status When 1 Then 1 Else 0 End) As 发表数量,
D.topicid 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,
D.topicid
Order By
C.corpid,
D.topicid
GO
--刪除測試環境
Drop Table data, [user]
--結果
/*
公司名 上报数量 发表数量 栏目
ABC 1 0 耗材
ABC 1 1 硬件
IBM 1 0 硬件
IBM 1 1 软件
LTD 1 0 周边
SUN 2 2 软件
*/

[昵称被占用了]

select
isnull(b.corpid,c.corpid) as 公司名,
count(distinct a.charid) as 上报数量,
sum(a.status) as 发表数量, --投机取巧一下
a.topicid
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),a.topicid
order by count(distinct a.charid) desc ,sum(a.status) desc

[paoluo]

wgqqgw(小强) ( ) 信誉：99 2007-07-27 15:35:48 得分: 0


select t1.corpid,
sum(case status when 0 then 1 else 0 end) as 上报数量,
sum(case status when 1 then 1 else 0 end) as 发表数量,
t1.topicid
from user t1, data t2
where t1.userid=t2.input
group by t1.corpid, t1.topicid


-----

wgqqgw(小强)沒有看樓主的上一貼，這麼寫得不到樓主的結果。

[eyan_810810]

谢谢!

[昵称被占用了]

又不要排序要求了?

[eyan_810810]

崇拜一下各位大侠!结贴了!

[wgqqgw]

是，我写错了，没有看清楚题目就乱写了，sorry楼主。