数据内容如下:
表名:TableA
TTime TStatus
2011-11-11 1
2011-11-12 1
2011-11-13 0
2011-11-14 0
2011-11-15 1
2011-11-16 1
2011-11-17 0
2011-11-18 0要求结果:
2011-11-12 1
2011-11-13 0
2011-11-16 1
2011-11-17 0
功能描述:取出TStatus变化前的一条和变化后的一条,这种记录有很多。我现在是取出后一个个的对比,感觉速度很慢,大家有没有高速的方法?
表名:TableA
TTime TStatus
2011-11-11 1
2011-11-12 1
2011-11-13 0
2011-11-14 0
2011-11-15 1
2011-11-16 1
2011-11-17 0
2011-11-18 0要求结果:
2011-11-12 1
2011-11-13 0
2011-11-16 1
2011-11-17 0
功能描述:取出TStatus变化前的一条和变化后的一条,这种记录有很多。我现在是取出后一个个的对比,感觉速度很慢,大家有没有高速的方法?
解决方案 »
- sqlserver2005
- SQL SERVER 2005 安装
- sql server是否支持特殊符号的密码
- 请教一条SQL语句
- 【请问我WIN7装了SQL2005之后可以直接再装2008吗?还是要先删除SQL SERVER2005??】
- sqlserver2000安装问题
- sql
- 请问 mssql 7.0 中的 Desktop Edition 是什么意思呀?
- 我现在主要学的是VFP,但是似乎VFP的知名道远不及VB,SQLServer,vc++,请问VFP,VB,SQLServer,vc++在数据库方面哪一个好用,各自有什么特
- 在数据库设计中,"数据依赖"描述的什么?
- 一下SQL语句有什么问题?
- 能不能使用类似row_number()进行分类编号
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 = 1TTime Tstatus
2011-11-12 1
2011-11-13 0
2011-11-16 1
2011-11-17 0
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 行受影响)
**/
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
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 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
EXEC(@STRSQL)
open DoQuKou_cur1
fetch next from DoQuKou_cur1 into @ttime,@tstatus
while(@@fetch_status=0)
begin
fetch next from DoQuKou_cur1 into @ttime1,@tstatus1
if (@@fetch_status=0)
begin
if(@tstatus<>@tstatus1)
begin
--得到两条记录
end
end
SET @ttime=@ttime1
SET @tstatus=@tstatus1
end
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
GOWITH 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
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',0select 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
*/
居然被你无视了,果断撤离,NND
WITH tba AS
(
SELECT TTime,Tstatus,RN=ROW_NUMBER() OVER (ORDER BY TTime)
FROM Ta
)SELECT A.TTime,A.Tstatus FROM tba AS A
INNER JOIN tba AS B ON (A.RN = B.RN -1 or A.RN = B.RN +1)
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 ta
)
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)
)
hb1122和zhaowenzhong和结果相差太大