table1表中有字段A,B,C。A是主键。
例如 A B C
1 1 1
2 1 2
3 2 1
4 2 1
5 1 2
6 1 2
如何用一条sql语句达到下面的效果
A B C
1 1 1
2 1 2
3 2 1
5 1 2
找出B,C和上条记录不一样的记录
例如 A B C
1 1 1
2 1 2
3 2 1
4 2 1
5 1 2
6 1 2
如何用一条sql语句达到下面的效果
A B C
1 1 1
2 1 2
3 2 1
5 1 2
找出B,C和上条记录不一样的记录
调试欢乐多
where not exists(
select 1 from table1 bb where aa.a > bb.a
) or aa.c <> (
select top 1 c from table1 bb where aa.a > bb.a order by a desc
)
select aa.* from table1 aa
where not exists(
select 1 from table1 bb where aa.a > bb.a
) or aa.b <> (
select top 1 b from table1 bb where aa.a > bb.a order by a desc
) or aa.c <> (
select top 1 c from table1 bb where aa.a > bb.a order by a desc
)
找出B,C和 上条记录 不一样的记录
上面没注意,B,C都不同的话,这样:
select aa.* from table1 aa
where not exists(
select 1 from table1 bb where aa.a > bb.a
) or aa.b <> (
select top 1 b from table1 bb where aa.a > bb.a order by a desc
) or aa.c <> (
select top 1 c from table1 bb where aa.a > bb.a order by a desc
)
1 1 1
2 1 2
3 2 1
5 1 2
如果B和C都和上一條記錄不同的話也不對啊。
相关子查询,效率不高!Declare @table1 Table(A int,B int,C int)
Insert @table1 select 1,1,1
union all select 2,1,2
union all select 3,2,1
union all select 4,2,1
union all select 5,1,2
union all select 6,1,2select *
from @table1 t1
where not exists
(select 1 from @table1 where b=t1.b and c=t1.c and a=t1.a-1)