科技网

当前位置: 首页 >智能

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

智能
来源: 作者: 2019-12-05 07:05:31

问题:

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

北京市大兴区红星医院怎么样
运城市第三医院
德阳治疗输卵管堵塞医院
治癫痫病湖南哪家医院效果好
金华治疗包皮过长医院

相关推荐