---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (carno VARCHAR(1),address VARCHAR(5),outdate DATETIME)
INSERT INTO @T
SELECT 'A','地点1','2009-04-14 02:00:00.000' UNION ALL
SELECT 'A','地点1','2009-04-14 02:20:00.000' UNION ALL
SELECT 'A','地点1','2009-04-14 10:00:00.000' UNION ALL
SELECT 'A','地点1','2009-04-14 16:00:00.000' UNION ALL
SELECT 'B','地点2','2009-04-14 07:10:00.000' UNION ALL
SELECT 'B','地点2','2009-04-14 07:22:00.000'--SQL查询如下:;WITH Liang AS
(
SELECT *,[minute]=DATEDIFF(minute,0,outdate)/30
FROM @T
)
SELECT A.carno,A.address,A.outdate
FROM Liang AS A
WHERE NOT EXISTS(SELECT * FROM Liang
WHERE carno=A.carno AND address=A.address
AND [minute]=A.[minute] AND outdate<A.outdate)/*
carno address outdate
----- ------- -----------------------
A 地点1 2009-04-14 02:00:00.000
A 地点1 2009-04-14 10:00:00.000
A 地点1 2009-04-14 16:00:00.000
B 地点2 2009-04-14 07:10:00.000(4 行受影响)
*/
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (carno VARCHAR(1),address VARCHAR(5),outdate DATETIME)
INSERT INTO @T
SELECT 'A','地点1','2009-04-14 02:00:00.000' UNION ALL
SELECT 'A','地点1','2009-04-14 02:20:00.000' UNION ALL
SELECT 'A','地点1','2009-04-14 10:00:00.000' UNION ALL
SELECT 'A','地点1','2009-04-14 16:00:00.000' UNION ALL
SELECT 'B','地点2','2009-04-14 07:10:00.000' UNION ALL
SELECT 'B','地点2','2009-04-14 07:22:00.000'--SQL查询如下:;WITH Liang AS
(
SELECT *,[minute]=DATEDIFF(minute,0,outdate)/30
FROM @T
)
SELECT A.carno,A.address,A.outdate
FROM Liang AS A
WHERE NOT EXISTS(SELECT * FROM Liang
WHERE carno=A.carno AND address=A.address
AND [minute]=A.[minute] AND outdate<A.outdate)/*
carno address outdate
----- ------- -----------------------
A 地点1 2009-04-14 02:00:00.000
A 地点1 2009-04-14 10:00:00.000
A 地点1 2009-04-14 16:00:00.000
B 地点2 2009-04-14 07:10:00.000(4 行受影响)
*/
Create table cardate(carno varchar(02),address nvarchar(10),outdate datetime)
insert into cardate
select 'A' , N'地点1' , '2009-04-14 02:00:00'
union all select 'A' , N'地点1' , '2009-04-14 02:20:00'
union all select 'A' , N'地点1' , '2009-04-14 10:00:00'
union all select 'A' , N'地点1' , '2009-04-14 16:00:00'
union all select 'B' , N'地点2' , '2009-04-14 07:10:00'
union all select 'B' , N'地点2' , '2009-04-14 07:22:00'
GOselect * from cardate as A
where
outdate > isnull((select dateadd(minute,30,max(outdate))
from cardate where carno=A.carno
and address=A.address
and outdate<A.outdate),'1900-1-1')/*
carno address outdate
----- ---------- -----------------------
A 地点1 2009-04-14 02:00:00.000
A 地点1 2009-04-14 10:00:00.000
A 地点1 2009-04-14 16:00:00.000
B 地点2 2009-04-14 07:10:00.000
*/drop table T
; WITH Liang AS
这是什么语法?,怎么没见过啊?
;WITH Liang AS
(
SELECT *,[minute]=DATEDIFF(minute,0,outdate)/30
FROM @T
)
相当于
SELECT *,[minute]=DATEDIFF(minute,0,outdate)/30 into Liang
FROM @T
间隔小于30分钟 为什么要用 DATEDIFF(minute,0,outdate)/30 ??
go
create table [cardate]([carno] varchar(1),[address] varchar(5),[outdate] datetime)
insert [cardate]
select 'A','地点1','2009-04-14 02:00:00.000' union all
select 'A','地点1','2009-04-14 02:20:00.000' union all
select 'A','地点1','2009-04-14 10:00:00.000' union all
select 'A','地点1','2009-04-14 16:00:00.000' union all
select 'B','地点2','2009-04-14 07:10:00.000' union all
select 'B','地点2','2009-04-14 07:22:00.000'
go
--select * from [cardate]select * from cardate t
where not exists(select 1 from cardate where carno=t.carno and [address]=t.address and outdate<t.outdate and datediff(mi,outdate,t.outdate)<30)
/*
carno address outdate
----- ------- -----------------------
A 地点1 2009-04-14 02:00:00.000
A 地点1 2009-04-14 10:00:00.000
A 地点1 2009-04-14 16:00:00.000
B 地点2 2009-04-14 07:10:00.000(4 行受影响)
*/