教你快速解决“一个多对多”的疑难问题

A表
syno seid date bnid num
667 10229 2006072816:57:41 12 3
667 10029 2006072819:10:28 12 7
667 20007 2006072820:01:26 12 8
667 10229 2006073007:11:22 12 9
667 10319 2006073111:09:56 12 4
667 10229 2006080110:56:38 12 1
667 10229 2006080211:06:38 12 6
B表
syno beid riqi bnid 
667 12007 2006072815:08:40 12
667 12011 2006072818:16:45 12
667 12012 2006073009:10:42 12
结果应该是：
syno beid seid sum(num)
667 12007 10229 3
667 12011 10029 7
667 12011 10229 9
667 12011 20007 8
667 12012 10229 7
667 12012 10319 4

CREATE TABLE Temp1 (syno int,seid int,[date] varchar(16),bnid int,num int)
CREATE TABLE Temp2 (syno int,beid int,riqi varchar(16),bnid int)
INSERT INTO Temp1 
SELECT 667, 10229, ''2006072816:57:41'', 12, 3 UNION ALL
SELECT 667, 10029, ''2006072819:10:28'' ,12 ,7 UNION ALL
SELECT 667, 20007, ''2006072820:01:26'' ,12 ,8 UNION ALL
SELECT 667, 10229, ''2006073007:11:22'' ,12 ,9 UNION ALL
SELECT 667, 10319, ''2006073111:09:56'' ,12 ,4 UNION ALL
SELECT 667, 10229, ''2006080110:56:38'' ,12 ,1 UNION ALL
SELECT 667, 10229, ''2006080211:06:38'' ,12 ,6
INSERT INTO Temp2 
SELECT 667 ,12007, ''2006072815:08:40'', 12 UNION ALL
SELECT 667 ,12011, ''2006072818:16:45'' ,12 UNION ALL
SELECT 667, 12012, ''2006073009:10:42'' ,12
go
--建立函数
CREATE FUNCTION Get_beid(@syno int,@bnid int ,@date varchar(16))
RETURNS int
AS 
BEGIN
DECLARE @RETURN int 
SET @RETURN=(SELECT Top 1 beid FROM Temp2 WHERE syno=@syno AND bnid=
@bnid AND riqi<=@date ORDER BY riqi DESC)
RETURN @RETURN
END
go
--计算过程
SELECT syno,beid,seid,sum(num) FROM (
SELECT syno,dbo.Get_beid(syno,bnid,[date]) AS beid,seid,num FROM Temp1 
) AS SUM_T
GROUP BY syno,beid,seid
ORDER BY syno,beid,seid
--删除测试表
DROP TABLE Temp1,Temp2
DROP FUNCTION Get_beid