例如 原始数据是 ,需要保留每一个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: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]
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]
from tb
--要考虑date的话,在case when 那里加上即可
Create table T(item varchar(10), [date] datetime, status varchar(10))
insert into T
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: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] GOdeclare @tmp int,@status varchar(10)
set @tmp=0
select id=identity(int,1,1),*, tmp=0 into #T from TUpdate #T
set @tmp=case when status=@status
then @tmp
else @tmp+1 end,
@status=status,
tmp=@tmp
select item,date,status from #T A
where not exists(select 1 from #T where tmp=A.tmp and id<A.id)
drop table #T
我用的是 游标 SET NOCOUNT ON
SELECT *,id=row_number()over(PARTITION BY Item order by Date ASC ) INTO #B
FROM (
SELECT DISTINCT *
from (
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: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] ) t
)a DECLARE @i INT
DECLARE @p_Date SMALLDATETIME
DECLARE @p_Status TINYINT
DECLARE @c_Date SMALLDATETIME
DECLARE @c_Status TINYINT
DECLARE @p_Item INT
DECLARE @H TABLE (Item INT,
Date SMALLDATETIME,
[Status] TINYINT,
id TINYINT)
DECLARE MyCur CURSOR FOR
SELECT DISTINCT Item
FROM #B
OPEN MyCur
FETCH NEXT FROM MyCur INTO @p_Item
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i=1
WHILE(@i<=(SELECT MAX(id) FROM #B WHERE Item = @p_Item) )
BEGIN
IF @i=1
BEGIN
INSERT INTO @H
SELECT Item ,Date ,[Status],id
FROM #B
WHERE Item = @p_Item
AND id =@i
END
SELECT @p_Date = Date ,@p_Status=[Status] FROM #B WHERE id =@i AND Item = @p_Item
SELECT @c_Date = Date ,@c_Status=[Status] FROM #B WHERE id =@i+1 AND Item = @p_Item
IF (@p_Status =@c_Status AND @p_Date <> @c_Date )
BEGIN
UPDATE @H
SET Date =@c_Date
WHERE [Status] = @c_Status
AND Item = @p_Item
AND id =@i-1
END
ELSE
BEGIN
INSERT INTO @H
SELECT Item ,Date ,[Status],id
FROM #B
WHERE id = @i AND @i>1
AND Item = @p_Item
END
SET @i=@i+1
END
FETCH NEXT FROM MyCur
INTO @p_Item
END
CLOSE MyCur
DEALLOCATE MyCur
SELECT DISTINCT Item ,Date ,[Status] FROM @H ORDER BY Date
SELECT * FROM #B