34,590
社区成员
发帖
与我相关
我的任务
分享
create table ta (TTime date,TStatus tinyint)
insert ta
select '2011-11-11',1 union all
select '2011-11-12',1 union all
select '2011-11-13',0 union all
select '2011-11-14',0 union all
select '2011-11-15',1 union all
select '2011-11-16',1 union all
select '2011-11-17',0 union all
select '2011-11-18',0
select DISTINCT B.ttime,B.tstatus from
(select ROW_NUMBER() over(order by ttime) rn,* from ta )a
join
(select (ROW_NUMBER() over(order by ttime)-1) rn,* from ta )b on a.rn >b.rn and a.ttime>b.ttime AND B.rn >0
order by B.ttime
/*
ttime tstatus
2011-11-12 1
2011-11-13 0
2011-11-14 0
2011-11-15 1
2011-11-16 1
2011-11-17 0
*/
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'TableA')
BEGIN
DROP TABLE TableA
END
GO
CREATE TABLE TableA
(
TTime VARCHAR(100),
TStatus INT
)
GO
INSERT INTO TableA
SELECT '2011-11-11', 1 UNION
SELECT '2011-11-12', 1 UNION
SELECT '2011-11-13', 0 UNION
SELECT '2011-11-14', 0 UNION
SELECT '2011-11-15', 1 UNION
SELECT '2011-11-16', 1 UNION
SELECT '2011-11-17', 0 UNION
SELECT '2011-11-18', 0
GO
WITH tba AS
(
SELECT TTime,Tstatus,RN=ROW_NUMBER() OVER (ORDER BY TTime)
FROM TableA
)
SELECT A.TTime,A.Tstatus
FROM tba AS A INNER JOIN tba AS B ON A.RN = CASE WHEN A.TStatus = 1 THEN B.RN - 1
WHEN A.TStatus = 0 THEN B.RN + 1 END AND B.TStatus = CASE WHEN A.TStatus = 1 THEN 0
WHEN A.TStatus = 0 THEN 1 END
SELECT A.TTime,A.Tstatus
FROM TableA AS A INNER JOIN TableA AS B ON A.TTime = CASE WHEN A.TStatus = 1 THEN DATEADD(DAY,-1,B.TTime)
WHEN A.TStatus = 0 THEN DATEADD(DAY,1,B.TTime) END AND B.TStatus = CASE WHEN A.TStatus = 1 THEN 0
WHEN A.TStatus = 0 THEN 1 END
;with cte as(
select rn=row_number() over(order by getdate()),* from tablea
)
select TTime,TStatus from cte t
where exists(
select 1
from cte
where tstatus!=t.tstatus
and (rn=t.rn+1 or rn=t.rn-1)
)
/**
TTime TStatus
----------------------- -----------
2011-11-12 00:00:00.000 1
2011-11-13 00:00:00.000 0
2011-11-14 00:00:00.000 0
2011-11-15 00:00:00.000 1
2011-11-16 00:00:00.000 1
2011-11-17 00:00:00.000 0
(6 行受影响)
**/
select FDate,value from (select FDate,value,
case when isnull((select top 1 value from dt as d1
where d1.FDate < dt.FDate order by FDate ),value) <> value or
isnull((select top 1 value from dt as d1
where d1.FDate > dt.FDate order by FDate desc ),value) <> value
then 1 else 0 end as Status
from dt) as ddf where Status = 1
if object_id('[TableA]') is not null drop table [TableA]
go
create table [TableA]([TTime] datetime,[TStatus] int)
insert [TableA]
select '2011-11-11',1 union all
select '2011-11-12',1 union all
select '2011-11-13',0 union all
select '2011-11-14',0 union all
select '2011-11-15',1 union all
select '2011-11-16',1 union all
select '2011-11-17',0 union all
select '2011-11-18',0
go
;with cte as(
select rn=row_number() over(order by getdate()),* from tablea
)
select TTime,TStatus from cte t
where exists(
select 1
from cte
where tstatus!=t.tstatus
and (tstatus=0 and rn=t.rn+1 or tstatus=1 and rn=t.rn-1)
)
/**
TTime TStatus
----------------------- -----------
2011-11-12 00:00:00.000 1
2011-11-13 00:00:00.000 0
2011-11-16 00:00:00.000 1
2011-11-17 00:00:00.000 0
(4 行受影响)
**/
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'TableA')
BEGIN
DROP TABLE TableA
END
GO
CREATE TABLE TableA
(
TTime VARCHAR(100),
TStatus INT
)
GO
INSERT INTO TableA
SELECT '2011-11-11', 1 UNION
SELECT '2011-11-12', 1 UNION
SELECT '2011-11-13', 0 UNION
SELECT '2011-11-14', 0 UNION
SELECT '2011-11-15', 1 UNION
SELECT '2011-11-16', 1 UNION
SELECT '2011-11-17', 0 UNION
SELECT '2011-11-18', 0
GO
SELECT A.TTime,A.Tstatus
FROM TableA AS A INNER JOIN TableA AS B ON A.TTime = DATEADD(DAY,-1,B.TTime) AND A.TStatus = 1 AND B.TStatus = 0
UNION
SELECT A.TTime,A.Tstatus
FROM TableA AS A INNER JOIN TableA AS B ON A.TTime = DATEADD(DAY,1,B.TTime) AND A.TStatus = 0 AND B.TStatus = 1
TTime Tstatus
2011-11-12 1
2011-11-13 0
2011-11-16 1
2011-11-17 0