--> 生成测试数据: @tb
DECLARE @tb TABLE (NM varchar(20),TM DATETIME,Data INT)
INSERT INTO @tb
SELECT'ST0100', '2008-10-10 00:23:00',10 UNION ALL
SELECT 'ST0100','2008-10-10 04:22:00',20 UNION ALL
SELECT'ST0100', '2008-10-10 08:05:00',30 UNION ALL
SELECT 'ST0100','2008-10-10 08:35:00',35 UNION ALL
SELECT 'ST0100','2008-10-10 12:00:00',40 UNION ALL
SELECT 'ST0100','2008-10-10 16:30:00',50 UNION ALL
SELECT'ST0100', '2008-10-10 20:48:00',60 UNION ALL
SELECT 'ST0100','2008-10-11 00:50:00',70 union ALL
SELECT 'ST0100','2008-10-11 04:20:00',80 需要得到的结果是
ST0100 2008-10-10 00:00:00.000 10
ST0100 2008-10-10 04:00:00.000 20
ST0100 2008-10-10 08:00:00.000 35
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 16:00:00.000 50
ST0100 2008-10-10 20:00:00.000 60
ST0100 2008-10-11 00:00:00.000 70
ST0100 2008-10-11 04:00:00.000 801、不是整点的数据整编为整点
2、如果有重复数据,则取时间大的记录感谢小F的帮助
DECLARE @tb TABLE (NM varchar(20),TM DATETIME,Data INT)
INSERT INTO @tb
SELECT'ST0100', '2008-10-10 00:23:00',10 UNION ALL
SELECT 'ST0100','2008-10-10 04:22:00',20 UNION ALL
SELECT'ST0100', '2008-10-10 08:05:00',30 UNION ALL
SELECT 'ST0100','2008-10-10 08:35:00',35 UNION ALL
SELECT 'ST0100','2008-10-10 12:00:00',40 UNION ALL
SELECT 'ST0100','2008-10-10 16:30:00',50 UNION ALL
SELECT'ST0100', '2008-10-10 20:48:00',60 UNION ALL
SELECT 'ST0100','2008-10-11 00:50:00',70 union ALL
SELECT 'ST0100','2008-10-11 04:20:00',80 需要得到的结果是
ST0100 2008-10-10 00:00:00.000 10
ST0100 2008-10-10 04:00:00.000 20
ST0100 2008-10-10 08:00:00.000 35
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 16:00:00.000 50
ST0100 2008-10-10 20:00:00.000 60
ST0100 2008-10-11 00:00:00.000 70
ST0100 2008-10-11 04:00:00.000 801、不是整点的数据整编为整点
2、如果有重复数据,则取时间大的记录感谢小F的帮助
NM,
convert(varchar(13),TM,120)+':00:00.000' as TM,
Data
from
@tb
NM,
convert(varchar(13),TM,120)+':00:00.000' as TM,
Data
from
@tb t
where
not exists(select 1 from @tb where NM=t.NM and convert(varchar(13),TM,120)=convert(varchar(13),t.TM,120) and data>t.data)/**
NM TM Data
-------------------- ----------------------- -----------
ST0100 2008-10-10 00:00:00.000 10
ST0100 2008-10-10 04:00:00.000 20
ST0100 2008-10-10 08:00:00.000 35
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 16:00:00.000 50
ST0100 2008-10-10 20:00:00.000 60
ST0100 2008-10-11 00:00:00.000 70
ST0100 2008-10-11 04:00:00.000 80(8 行受影响)
**/
DECLARE @tb TABLE (NM varchar(20),TM DATETIME,Data INT)
INSERT INTO @tb
SELECT'ST0100', '2008-10-10 00:23:00',10 UNION ALL
SELECT 'ST0100','2008-10-10 04:22:00',20 UNION ALL
SELECT'ST0100', '2008-10-10 08:05:00',30 UNION ALL
SELECT 'ST0100','2008-10-10 08:35:00',35 UNION ALL
SELECT 'ST0100','2008-10-10 12:00:00',40 UNION ALL
SELECT 'ST0100','2008-10-10 16:30:00',50 UNION ALL
SELECT'ST0100', '2008-10-10 20:48:00',60 UNION ALL
SELECT 'ST0100','2008-10-11 00:50:00',70 union ALL
SELECT 'ST0100','2008-10-11 04:20:00',80 select
nm,cast(tm as datetime) ,data
from
@tb t
where
not exists(select 1 from @tb where nm=t.nm and ltrim(datepart(hh,tm))=ltrim(datepart(hh,t.tm)) and data<t.data)
/*nm data
-------------------- ----------------------- -----------
ST0100 2008-10-10 00:23:00.000 10
ST0100 2008-10-10 04:22:00.000 20
ST0100 2008-10-10 08:05:00.000 30
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 16:30:00.000 50
ST0100 2008-10-10 20:48:00.000 60(6 行受影响)*/
INSERT INTO @tb
SELECT'ST0100', '2008-10-10 00:23:00',10 UNION ALL
SELECT 'ST0100','2008-10-10 04:22:00',20 UNION ALL
SELECT'ST0100', '2008-10-10 08:05:00',30 UNION ALL
SELECT 'ST0100','2008-10-10 08:35:00',35 UNION ALL
SELECT 'ST0100','2008-10-10 12:00:00',40 UNION ALL
SELECT 'ST0100','2008-10-10 16:30:00',50 UNION ALL
SELECT'ST0100', '2008-10-10 20:48:00',60 UNION ALL
SELECT 'ST0100','2008-10-11 00:50:00',70 union ALL
SELECT 'ST0100','2008-10-11 04:20:00',80
SELECT NM,
TM=CONVERT(VARCHAR(13),TM,120)+':00:00.000',
Data=MAX(Data)
FROM @tb
GROUP BY NM,CONVERT(VARCHAR(13),TM,120)+':00:00.000'
/*
NM TM Data
-------------------- ----------------------- -----------
ST0100 2008-10-10 00:00:00.000 10
ST0100 2008-10-10 04:00:00.000 20
ST0100 2008-10-10 08:00:00.000 35
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 16:00:00.000 50
ST0100 2008-10-10 20:00:00.000 60
ST0100 2008-10-11 00:00:00.000 70
ST0100 2008-10-11 04:00:00.000 80
*/
DECLARE @tb TABLE (NM varchar(20),TM DATETIME,Data INT)
INSERT INTO @tb
SELECT'ST0100', '2008-10-10 00:23:00',10 UNION ALL
SELECT 'ST0100','2008-10-10 04:22:00',20 UNION ALL
SELECT'ST0100', '2008-10-10 08:05:00',30 UNION ALL
SELECT 'ST0100','2008-10-10 08:35:00',35 UNION ALL
SELECT 'ST0100','2008-10-10 12:00:00',40 UNION ALL
SELECT 'ST0100','2008-10-10 16:30:00',50 UNION ALL
SELECT'ST0100', '2008-10-10 20:48:00',60 UNION ALL
SELECT 'ST0100','2008-10-11 00:50:00',70 union ALL
SELECT 'ST0100','2008-10-11 04:20:00',80 select
nm,cast(tm as datetime) ,data
from
@tb t
where
not exists(select 1 from @tb where nm=t.nm and convert(varchar(14),TM,120)=convert(varchar(14),t.TM,120) and data<t.data)
/*nm data
-------------------- ----------------------- -----------
ST0100 2008-10-10 00:23:00.000 10
ST0100 2008-10-10 04:22:00.000 20
ST0100 2008-10-10 08:05:00.000 30
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 16:30:00.000 50
ST0100 2008-10-10 20:48:00.000 60
ST0100 2008-10-11 00:50:00.000 70
ST0100 2008-10-11 04:20:00.000 80(8 行受影响)
*/
DECLARE @tb TABLE (NM varchar(20),TM DATETIME,Data INT)
INSERT INTO @tb
SELECT'ST0100', '2008-10-10 00:23:00',10 UNION ALL
SELECT 'ST0100','2008-10-10 04:22:00',20 UNION ALL
SELECT'ST0100', '2008-10-10 08:05:00',30 UNION ALL
SELECT 'ST0100','2008-10-10 08:35:00',35 UNION ALL
SELECT 'ST0100','2008-10-10 12:00:00',40 UNION ALL
SELECT 'ST0100','2008-10-10 16:30:00',50 UNION ALL
SELECT'ST0100', '2008-10-10 20:48:00',60 UNION ALL
SELECT 'ST0100','2008-10-11 00:50:00',70 union ALL
SELECT 'ST0100','2008-10-11 04:20:00',80 select
nm,cast(convert(varchar(10),tm,120) as datetime) as tm ,data
from
@tb t
where
not exists(select 1 from @tb where nm=t.nm and convert(varchar(14),TM,120)=convert(varchar(14),t.TM,120) and data<t.data)
/*nm tm data
-------------------- ----------------------- -----------
ST0100 2008-10-10 00:00:00.000 10
ST0100 2008-10-10 00:00:00.000 20
ST0100 2008-10-10 00:00:00.000 30
ST0100 2008-10-10 00:00:00.000 40
ST0100 2008-10-10 00:00:00.000 50
ST0100 2008-10-10 00:00:00.000 60
ST0100 2008-10-11 00:00:00.000 70
ST0100 2008-10-11 00:00:00.000 80(8 行受影响)
*/
NM,
convert(varchar(13),TM,120)+':00:00.000' as TM,
Data
from
@tb t
where
not exists(select 1
from @tb
where NM=t.NM
and convert(varchar(13),TM,120)=convert(varchar(13),t.TM,120)
and TM>t.TM --如果有重复数据,则取时间大的记录
)
这个例子data也是递增的,所以data和tm都合适,但时间判定最好