高手请进，复杂的SQL查询

Question

參照:
合并分拆表_整理贴1
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

Answer 1

2000使用函数，
2005使用xml.
具体例子参考楼上

Answer 2

use test
go
set nocount on ;
if not object_id('Tempdb..#UserInfo') is null
drop table #UserInfo
Go
Create table #UserInfo([UserID] int,[UserName] nvarchar(3),[LoginState] int,[DeptID] int)
Insert #UserInfo
select 1,N'aaa',0,1 union all
select 2,N'bbb',1,1 union all
select 3,N'ccc',0,1 union all
select 4,N'ddd',1,1 union all
select 5,N'eee',1,2 union all
select 6,N'fff',1,2 union all
select 7,N'ggg',0,2 union all
select 8,N'hhh',1,3
Goif not object_id('Tempdb..#DeptInfo') is null
drop table #DeptInfo
Go
Create table #DeptInfo([DeptID] int,[DeptName] nvarchar(3))
Insert #DeptInfo
select 1,N'开发部' union all
select 2,N'市场部'
Goif not object_id('Tempdb..#ProjectInfo') is null
drop table #ProjectInfo
Go
Create table #ProjectInfo([ProjectID] int,[DeptIDs] nvarchar(4),[UserIDs] nvarchar(11))
Insert #ProjectInfo
select 1,N'1/2/',N'1,2,3,/5,6/' union all
select 2,N'3/',N'8,/'
Go
;with roy as 
(select 
[ProjectID],
[DeptIDs]=cast(left([DeptIDs],charindex('/',[DeptIDs])-1) as nvarchar(100)),
Split=cast(stuff([DeptIDs],1,charindex('/',[DeptIDs]),'') as nvarchar(100)),
[UserIDs]=cast(left([UserIDs],charindex('/',[UserIDs])-1) as nvarchar(100)),
Split2=cast(stuff([UserIDs],1,charindex('/',[UserIDs]),'') as nvarchar(100))
from #ProjectInfo
union all
select
[ProjectID],
[DeptIDs]=cast(left(Split,charindex('/',Split)-1) as nvarchar(100)),
Split= cast(stuff(Split,1,charindex('/',Split),'') as nvarchar(100)),
[UserIDs]=cast(left(Split2,charindex('/',Split2)-1) as nvarchar(100)),
Split2= cast(stuff(Split2,1,charindex('/',Split2),'') as nvarchar(100))  
from roy where split>''
)
select 
a.[DeptIDs],b.[DeptName],count(c.[UserID]) as TotalNum,sum(case when c.[LoginState]=1 then 1 else 0 end) as OnlineNum
from 
roy a
join
#DeptInfo b on a.[DeptIDs]=b.[DeptID]
join
#UserInfo c on ','+a.[UserIDs]+',' like '%,'+rtrim(c.[UserID])+',%'
group by a.[DeptIDs],b.[DeptName]
order by a.[DeptIDs] option (MAXRECURSION 0)
DeptIDs                                                                                              DeptName TotalNum    OnlineNum
---------------------------------------------------------------------------------------------------- -------- ----------- -----------
1                                                                                                    开发部      3           1
2                                                                                                    市场部      2           2