sql 语句两表查询后根据一个表中的字段排重问题，sql排重问题

用户表：
ID　　Name　　Email
1　　 张三　　　[e-mail]
2　　 李四　　　[e-mail]
3　　 王二　　　[e-mail]

报名表（这个表用于记录某个用户报名了某个培训班）：
ID　　U_UserID（与用户表的ID关联）　 U_PeiXunBanID（与培训班关联的ID）
1　　 1　　　　　　　　　　　　　　　　200
2　　 2　　　　　　　　　　　　　　　　200
3　　 2　　　　　　　　　　　　　　　　200
4　　 1　　　　　　　　　　　　　　　　200
5　　 3　　　　　　　　　　　　　　　　200　
6　　 1　　　　　　　　　　　　　　　　201 

现在我想查找出报名了id为200的培训班的所有人（搜索结果人数不能重复），并且把用户重复的邮箱进行排重，我使用了下面的语句，如果数据多的时候执行的就超级慢，请问我应该如何优化？

select * from 用户表 where ID in ( select  ID from 用户表 left JOIN 报名表 ON ID = U_UserID where U_PeiXunBanID = 200 group by ID) and ID in (select max(ID) from 用户表 where Email<>'' GROUP BY Email) ;

如果把后面排重Email的语句去掉那么执行的速度就很快，
去掉这句话：and ID in (select max(ID) from 用户表 where Email<>'' GROUP BY Email) 执行速度就很快。

请高人指点！！！

9 个解决方案

#1

感觉你的sql逻辑有点混乱。
试试这个

select distinct s.*
from 用户表 s left JOIN 报名表 ON s.ID = U_UserID
where U_PeiXunBanID=200 and 
      not exists(select 1 from 用户表  where s.Email=Email and id>s.ID)

2	李四	[e-mail]
3	王二	[e-mail]

#2

select * from
(
    select *,ROW_NUMBER() over (partition by Email order by ID desc) as rn
    from [用户表]
) t1 
inner join
(
    select distinct [U_UserID],[U_PeiXunBanID] from [报名表]
    where [U_PeiXunBanID]=200
) t2
on t1.ID = t2.ID

#3

引用 1 楼 HEROWANG 的回复:

感觉你的sql逻辑有点混乱。
试试这个

select distinct s.*
from 用户表 s left JOIN 报名表 ON s.ID = U_UserID
where U_PeiXunBanID=200 and 
      not exists(select 1 from 用户表  where s.Email=Email and id>s.ID)

2	李四	[e-mail]
3	王二	[e-mail]

s 是什么呢？

#4

s是给用户表取的别名

#5

SELECT T.ID,T.Email,T.Name FROM (
	SELECT MAX(ID) ID,Email,MAX(Name) Name FROM 用户表
	GROUP BY Email
) T WHERE ID IN (
	SELECT U_UserID FROM 报名表
	WHERE U_PeiXunBanID=200
)

#6

3楼的执行速度很快，2楼的好像慢一些，还有个问题就是新添加的列可以用执行where条件吗？

下面的语句会报错，说a3附近有错误。 但是a3可以用来order by，为什么不可以用作where呢？

select a1,a2,(a1+a2) as a3 from aaa where a3<100

#7

select a1,a2,(a1+a2) as a3 from aaa where (a1+a2)<100

#8

引用 7 楼 HEROWANG 的回复:

select a1,a2,(a1+a2) as a3 from aaa where (a1+a2)<100

多谢但是我想这样用，不知道有没有好办法：

select ID,Name,Email ROW_NUMBER() over (partition by Email order by ID desc) as rn  from 学员表 where rn<2;

想用as出来的新列rn做where，不知道能不能做到。

#9

可以这样

select * from
(select ID,Name,Email ROW_NUMBER() over (partition by Email order by ID desc) as rn  from 学员表 ) K
where rn<2;