Sta 相邻的取最大的日期原始数据
SELECT 100 Item ,'2010-02-01 15:20:00' Date ,4 Sta
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,38
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,0
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,2
UNION ALL
SELECT 100 ,'2010-02-02 03:57:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:57:00' ,0
UNION ALL
SELECT 101 ,'2010-02-02 03:58:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:59:00' ,2
我想要的结果 SELECT 100 Item ,'2010-02-01 15:20:00' Date ,4 Sta
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,38
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,0
UNION ALL
SELECT 100 ,'2010-02-02 03:57:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:57:00' ,0
UNION ALL
SELECT 101 ,'2010-02-02 03:59:00' ,2
SELECT 100 Item ,'2010-02-01 15:20:00' Date ,4 Sta
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,38
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,0
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,2
UNION ALL
SELECT 100 ,'2010-02-02 03:57:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:57:00' ,0
UNION ALL
SELECT 101 ,'2010-02-02 03:58:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:59:00' ,2
我想要的结果 SELECT 100 Item ,'2010-02-01 15:20:00' Date ,4 Sta
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,38
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,0
UNION ALL
SELECT 100 ,'2010-02-02 03:57:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:57:00' ,0
UNION ALL
SELECT 101 ,'2010-02-02 03:59:00' ,2
select distinct *
from
(
SELECT 100 Item ,'2010-02-01 15:20:00' Date ,4 Sta
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,38
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,0
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,2
UNION ALL
SELECT 100 ,'2010-02-02 03:57:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:57:00' ,0
UNION ALL
SELECT 101 ,'2010-02-02 03:58:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:59:00' ,2
) a
where not exists
(select 1 from
(SELECT 100 Item ,'2010-02-01 15:20:00' Date ,4 Sta
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,38
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,0
UNION ALL
SELECT 100 ,'2010-02-02 03:56:00' ,2
UNION ALL
SELECT 100 ,'2010-02-02 03:57:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:57:00' ,0
UNION ALL
SELECT 101 ,'2010-02-02 03:58:00' ,2
UNION ALL
SELECT 101 ,'2010-02-02 03:59:00' ,2
)b
where a.sta=b.sta and a.Item=b.Item and b.[date]>a.[date])/**
Item Date Sta
----------- ------------------- -----------
100 2010-02-01 15:20:00 4
100 2010-02-01 15:23:00 30
100 2010-02-01 15:23:00 38
100 2010-02-02 03:56:00 0
100 2010-02-02 03:57:00 2
101 2010-02-02 03:57:00 0
101 2010-02-02 03:59:00 2(所影响的行数为 7 行)
**/
(
select id=row_number()over(order by getdate()),* from tb
)
select
item,date,sta
from
f t
where
not exists(select 1 from f where Date=t.Date and id<t.id or (Item=t.Item and Date=t.Date and id<t.id))
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-02-04 15:59:12
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Item] int,[Date] datetime,[Sta] int)
insert [tb]
select 100,'2010-02-01 15:20:00',4 union all
select 100,'2010-02-01 15:23:00',30 union all
select 100,'2010-02-01 15:23:00',30 union all
select 100,'2010-02-01 15:23:00',38 union all
select 100,'2010-02-02 03:56:00',0 union all
select 100,'2010-02-02 03:56:00',2 union all
select 100,'2010-02-02 03:57:00',2 union all
select 101,'2010-02-02 03:57:00',0 union all
select 101,'2010-02-02 03:58:00',2 union all
select 101,'2010-02-02 03:59:00',2
--------------开始查询--------------------------
;with f as
(
select id=row_number()over(order by getdate()),* from tb
)
select
item,date,sta
from
f t
where
not exists(select 1 from f where Item=t.Item and Date=t.Date and id<t.id or (Item=t.Item and Date=t.Date and id<t.id))
----------------结果----------------------------
/*item date sta
----------- ----------------------- -----------
100 2010-02-01 15:20:00.000 4
100 2010-02-01 15:23:00.000 30
100 2010-02-02 03:56:00.000 0
100 2010-02-02 03:57:00.000 2
101 2010-02-02 03:57:00.000 0
101 2010-02-02 03:58:00.000 2
101 2010-02-02 03:59:00.000 2(7 行受影响)
*/
UNION ALL
SELECT 100 ,'2010-02-01 15:23:00' ,38
我希望是按Date Acs排序后的
(小F) 的结果不对
INSERT @a SELECT 100 ,'2010-02-01 15:20:00' ,4
UNION ALL SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL SELECT 100 ,'2010-02-01 15:23:00' ,30
UNION ALL SELECT 100 ,'2010-02-01 15:23:00' ,38
UNION ALL SELECT 100 ,'2010-02-02 03:56:00' ,0
UNION ALL SELECT 100 ,'2010-02-02 03:56:00' ,2
UNION ALL SELECT 100 ,'2010-02-02 03:57:00' ,2
UNION ALL SELECT 101 ,'2010-02-02 03:57:00' ,0
UNION ALL SELECT 101 ,'2010-02-02 03:58:00' ,2
UNION ALL SELECT 101 ,'2010-02-02 03:59:00' ,2
SELECT distinct * FROM @a a WHERE NOT exists(SELECT 1 FROM @a WHERE item=a.item AND Sta=a.Sta AND date>a.date)
ORDER BY item,date--result
/*
item date Sta
----------- ------------------------------------------------------ -----------
100 2010-02-01 15:20:00.000 4
100 2010-02-01 15:23:00.000 30
100 2010-02-01 15:23:00.000 38
100 2010-02-02 03:56:00.000 0
100 2010-02-02 03:57:00.000 2
101 2010-02-02 03:57:00.000 0
101 2010-02-02 03:59:00.000 2(所影响的行数为 7 行)*/
(树上的鸟儿) 结过也不对. 没有考虑同时出现Sta, 但是是不同时间点的数据
SELECT '100' AS Item, '2010-02-01 15:20:00' AS Date, '30' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-01 15:23:00' AS Date, '30' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-01 15:23:00' AS Date, '30' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-01 15:23:00' AS Date, '38' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 03:56:00' AS Date, '0' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 03:57:00' AS Date, '2' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 03:57:00' AS Date, '2' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 03:57:00' AS Date, '37' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 07:24:00' AS Date, '0' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 07:25:00' AS Date, '2' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 07:25:00' AS Date, '2' AS [Status]
我要的结果是 SELECT '100' AS Item, '2010-02-01 15:20:00' AS Date, '4' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-01 15:23:00' AS Date, '30' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-01 15:23:00' AS Date, '38' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 03:56:00' AS Date, '0' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 03:57:00' AS Date, '2' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 03:57:00' AS Date, '37' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 07:24:00' AS Date, '0' AS [Status] UNION ALL
SELECT '100' AS Item, '2010-02-02 07:25:00' AS Date, '2' AS [Status]