数据合并，汇总.......
表1：DocWait
ID(增) DocID SendID WaitUserID NodeID
1114 111
2114 222
3114 122
4114 523
5114 231
6115 3 21
表2：DocSend
SendID(增) SendUserID SendDate
1 1 2005-4-5
2 2 2006-5-7
3 5 2007-7-7
表3：UserDef
UserID(增) Name
1 用户1
2 用户2
3 用户3
....
5 用户5
问题：表1，SendID -> 表2：SendID
表2，SendUserID -> 表3：UserID
当
当前用户WaitUserID=2时显示,相同DOCID，NodeID合并
得到以下结果:
ID DocID SendID WaitUserID
1114 用户2,用户12005-4-5
4114 用户52005-4-5
2115 用户32007-7-7
[解决办法]
CREATE TABLE DocWait
(
ID INT IDENTITY(1,1),
DocID INT,
SendID INT,
WaitUserID INT,
NodeID INT
)
INSERT INTO DocWait(DocID,SendID,WaitUserID,NodeID)
SELECT 114,1,1,1 UNION ALL
SELECT 114,2,2,2 UNION ALL
SELECT 114,1,2,2 UNION ALL
SELECT 114,5,2,3 UNION ALL
SELECT 114,2,3,1 UNION ALL
SELECT 115,3,2,1
CREATE TABLE DocSend
(
SendID INT IDENTITY(1,1),
SendUserID INT,
SendDate DATETIME
)
INSERT INTO DocSend(SendUserID,SendDate)
SELECT 1, '2005-4-5 ' UNION ALL
SELECT 2, '2006-5-7 ' UNION ALL
SELECT 5, '2007-7-7 '
CREATE TABLE UserDef
(
UserID INT IDENTITY(1,1),
Name VARCHAR(20)
)
INSERT INTO UserDef (Name)
SELECT '用户1 ' UNION ALL
SELECT '用户2 ' UNION ALL
SELECT '用户3 ' UNION ALL
SELECT '用户4 ' UNION ALL
SELECT '用户5 '
CREATE FUNCTION dbo.f_strjoin(@DocID INT,@NodeID INT)
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re= ' '
SELECT @re=@re+ ', '+CAST(A.Name as varchar)
FROM UserDef A INNER JOIN DocSend B ON B.SendUserID=A.UserID
INNER JOIN DocWait C ON B.SendID=C.SendID
WHERE C.DocID=@DocID AND C.NodeID=@NodeID
RETURN(STUFF(@re,1,1, ' '))
END
SELECT A.ID,A.DocID,dbo.f_strjoin(A.DocID,A.NodeID) SendID,B.SendDate WaitUserID FROM DocWait A INNER JOIN DocSend B ON A.SendID=B.SendID WHERE A.WaitUserID=2
--
ID DocID SendID WaitUserID
----------- ----------- ---------------------------------------------------------------- -----------------------
2 114 用户1,用户2 2006-05-07 00:00:00.000
3 114 用户1,用户2 2005-04-05 00:00:00.000
6 115 用户5 2007-07-07 00:00:00.000
(3 行受影响)
