题目:有两张表 USER ,Email .
表结构为: USER (id int ,name varchar(10));
Email(eid int ,userid int,mail varchar(10))
其中每个用户允许有N个Email地址,两张表可以通过Email.userid=USER.id关联起来;
问: 查询出Email个数大于3个的用户名。
------------------------------------------各位有效率高的方法 请赐教!
表结构为: USER (id int ,name varchar(10));
Email(eid int ,userid int,mail varchar(10))
其中每个用户允许有N个Email地址,两张表可以通过Email.userid=USER.id关联起来;
问: 查询出Email个数大于3个的用户名。
------------------------------------------各位有效率高的方法 请赐教!
where user.id=email.userid
group by USER.id,user.name
having(count(*)>3)
from [user] a
join [email] b
on a.id=b.userid
where exists(
select 1
from email
where userid=b.userid
group by mail
having count(1)>3)
from USER join Email
on user.id=email.userid
group by user.name
having count(*)>3
from [user] a
where exists(
select 1
from email
where userid=a.id
group by mail
having count(1)>3)这样应该就可以了