1 6 --- 1 6
2 7 --- 2 7
3 6 --- 3 6
4 8 --- 4 8
a b这个唯一化语句的删除机理是什么啊,半年前就晕,现在看了大家的,还是不明白
a b 表是如何选择的啊!!DECLARE @t table(id int,uid int,email varchar(20))
insert @t (
[id],
[uid],
[email]
)
select 1,6,'aaa' union all
select 2,7,'bbb' union all
select 3,6,'aaa' union all
select 4,8,'ddd' SELECT * FROM @tSELECT * from @t t where
exists(select * from @t where t.uid = uid and t.email = email and t.id>id)select * from @t
2 7 --- 2 7
3 6 --- 3 6
4 8 --- 4 8
a b这个唯一化语句的删除机理是什么啊,半年前就晕,现在看了大家的,还是不明白
a b 表是如何选择的啊!!DECLARE @t table(id int,uid int,email varchar(20))
insert @t (
[id],
[uid],
[email]
)
select 1,6,'aaa' union all
select 2,7,'bbb' union all
select 3,6,'aaa' union all
select 4,8,'ddd' SELECT * FROM @tSELECT * from @t t where
exists(select * from @t where t.uid = uid and t.email = email and t.id>id)select * from @t
exists(select * from @t where t.uid = uid and t.email = email and t.id>id)
当有UID、EMAIL相同但ID小于本条记录的时候读出记录
也就等价于输出以UID、EMAIL分组的ID不是最小的所有记录
exists(select * from @t where t.uid = uid and t.email = email and t.id>id)
进行比较呀,自身连接,看条件不同的那列,以及表别名,这是查询相同的那些,ID大的那些
select * from @t where t.uid = uid and t.email = email and t.id>id这个是一个查询
然后外面再嵌套了一层
exists表示满足条件 所以可以来判断
子查询返回true,则主句(记录)记录符合要求输出
子查询返回false,则主句(记录)不符合输出要求
SELECT * from @t t where --
exists(select * from @t where t.uid = uid and t.email = email and t.id>id)把外面的表当成表t
在t中找到第一条记录,满足表中所有的记录中,uid,email跟他相当,并且id都小于这条记录的
也就是没有比他更大的,所有这条记录必是uid,email都相同中。id最大的那条记录
SELECT * from @t t where
exists(select 1 from @t where t.uid = uid and t.email = email and t.id>id)
建议*改成1