教你快速解决“一个多对多”的疑难问题
来源：岁月联盟
时间：2008-01-28
问题：
A表syno seid date bnid num667 10229 2006072816:57:41 12 3667 10029 2006072819:10:28 12 7667 20007 2006072820:01:26 12 8667 10229 2006073007:11:22 12 9667 10319 2006073111:09:56 12 4667 10229 2006080110:56:38 12 1667 10229 2006080211:06:38 12 6B表syno beid riqi bnid 667 12007 2006072815:08:40 12667 12011 2006072818:16:45 12667 12012 2006073009:10:42 12结果应该是：syno beid seid sum(num)667 12007 10229 3667 12011 10029 7667 12011 10229 9667 12011 20007 8667 12012 10229 7667 12012 10319 4 |
说明：
A表B表syno，bnid相关，相同syno在A表中的记录日期date总是小于B表riqi，
要求：两个表相连后按照syno,beid,seid分组，计算num值，
假如B表中相同的syno有多个记录，计算时要比较A表B表中的日期，
判定A表中的日期记录在B表中的时间段。
解决方法（参考示例：
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 ALLSELECT 667, 10029, ''2006072819:10:28'' ,12 ,7 UNION ALLSELECT 667, 20007, ''2006072820:01:26'' ,12 ,8 UNION ALLSELECT 667, 10229, ''2006073007:11:22'' ,12 ,9 UNION ALLSELECT 667, 10319, ''2006073111:09:56'' ,12 ,4 UNION ALLSELECT 667, 10229, ''2006080110:56:38'' ,12 ,1 UNION ALLSELECT 667, 10229, ''2006080211:06:38'' ,12 ,6INSERT INTO Temp2 SELECT 667 ,12007, ''2006072815:08:40'', 12 UNION ALLSELECT 667 ,12011, ''2006072818:16:45'' ,12 UNION ALLSELECT 667, 12012, ''2006073009:10:42'' ,12go--建立函数CREATE FUNCTION Get_beid(@syno int,@bnid int ,@date varchar(16))RETURNS intAS BEGINDECLARE @RETURN int SET @RETURN=(SELECT Top 1 beid FROM Temp2 WHERE syno=@syno AND bnid=@bnid AND riqi<=@date ORDER BY riqi DESC)RETURN @RETURNENDgo--计算过程SELECT syno,beid,seid,sum(num) FROM (SELECT syno,dbo.Get_beid(syno,bnid,[date]) AS beid,seid,num FROM Temp1 ) AS SUM_TGROUP BY syno,beid,seidORDER BY syno,beid,seid--删除测试表DROP TABLE Temp1,Temp2DROP FUNCTION Get_beid |
