[SQL] 找出前后相差1分钟的资料

本来还傻傻的想用2个迴圈下去做,后来看到有人用INNER JOIN 做,这作法,真聪明!!
--找出这批ng帽子,生产前后1分钟的批号.

DROP TABLE #TMP1create table  #TMP1(CODE VARCHAR(10) ,TAG VARCHAR(30),产品 varchar(30) ,EDATE DATE,TIME1 datetime )DROP TABLE #TMP2create table  #TMP2(CODE VARCHAR(10) ,TAG VARCHAR(30),产品 varchar(30) ,EDATE DATE,TIME1 datetime ) DECLARE @SECONDS INT =60;TRUNCATE table #TMP1TRUNCATE table #TMP2DECLARE @DATE DATETIME =getdate()insert into #TMP1 select 1,'NG','帽子' ,@DATE,@DATE insert into #TMP2 select 2,'','帽子' ,@DATE,DATEADD (SECOND,30, @DATE) insert into #TMP2 select 3,'','帽子' ,@DATE,DATEADD (MINUTE,1,  @DATE) insert into #TMP2 select 3,'','帽子' ,@DATE,DATEADD (MINUTE,-1,  @DATE)insert into #TMP2 select 4,'','帽子' ,@DATE,DATEADD (MINUTE,2, @DATE) insert into #TMP2 select 5,'','手套' ,@DATE,DATEADD (SECOND,30, @DATE)  SELECT ABS(DATEDIFF(SS,A.TIME1,B.TIME1)) ,A.*,B.*  FROM #TMP1 A,#TMP2 B  WHERE A.EDATE = B.EDATE   AND A.产品 = B.产品    AND A.CODE != B.CODE   AND ABS(DATEDIFF(SS,A.TIME1,B.TIME1)) <= @SECONDS