Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
GoSelect * from #T a where exists(select * from #T where Name=a.Name and ID<a.ID)为什么运行结果是ID Name Memo
----------- ---- ----
2 A A2
3 A A3
5 B B2(所影响的行数为 3 行)而不是
ID Name Memo
----------- ---- ----
1 A A1
2 A A2
4 B B1
呢?ID<a.ID 不就是1、2、4么?exists表示存在的意思么?我也越看越不明白了~哪位可以帮我分析下么,谢了。
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
GoSelect * from #T a where exists(select * from #T where Name=a.Name and ID<a.ID)为什么运行结果是ID Name Memo
----------- ---- ----
2 A A2
3 A A3
5 B B2(所影响的行数为 3 行)而不是
ID Name Memo
----------- ---- ----
1 A A1
2 A A2
4 B B1
呢?ID<a.ID 不就是1、2、4么?exists表示存在的意思么?我也越看越不明白了~哪位可以帮我分析下么,谢了。
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
GoSelect * from #T a where exists(select * from #T where Name=a.Name and ID>a.ID)drop table #t
ID Name Memo
----------- ---- ----
1 A A1
2 A A2
4 B B1(3 行受影响)
当扫描第一条记录的时候..也就是name=A,ID=1,进入exists,这看作是一个内循环..
然后再用外循环的a.name=name,然后外循环的ID是1,显然.在name相同的情况下..id没有小于1的.
所以不存在..则exists返回false.则这条记录不选取..
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
GoSelect * from #T a where ID not in (select max(id) from #T where Name=a.Name)
/*
ID Name Memo
----------- ---- ----
1 A A1
2 A A2
4 B B1(所影响的行数为 3 行)
*/Select * from #T a where ID not in (select min(id) from #T where Name=a.Name)
/*
ID Name Memo
----------- ---- ----
2 A A2
3 A A3
5 B B2(所影响的行数为 3 行)
*/drop table #t