为高手献上SQL语句，查询速度问题

Question

有两张表users(_id,_name)和users_contact(_id,_name,_email,_sent)，
一个_name对应多个_email，_sent=1表示已经发送过
想要这样的结果：_name,_sent,_nosent
下边是我写的SQL语句，但速度很慢，不到2000个user需要32秒
如何提高查询速度？create table #tb
(
_indx int,
_id int,
_name varchar(200),
_sent int default 0,
_nosent int default 0
)
insert into #tb(_indx,_id,_name) 
select row_number() over(order by _name),_id,_name from users
declare @maxrow int
set @maxrow=(select max(_indx) from #tb)
declare @row int
set @row=1
declare @name varchar(200)
while @row<=@maxrow
begin
set @name=(select _name from #tb where _indx=@row)
update #tb set _sent=(select count(*) from users_contact where _name=@name and _sent=1),
_nosent=(select count(*) from bigcomp_contact where _name=@name and _sent=0)
where _indx=@row
set @row=@row+1
end
select * from #tb
drop table #tb

Answer 1

数据就是这样：
create table users
(
_id int identity(1,1),
_name varchar(200)
)
insert into users(_name) values('aaa')
insert into users(_name) values('bbb')
--共2000条create table users_contact
(
_id int identity(1,1),
_name varchar(200),
_email varchar(200),
_sent int default 0
)
insert into users_contact(_name,_email) values('aaa','[email protected]')
insert into users_contact(_name,_email) values('aaa','[email protected]')
insert into users_contact(_name,_email) values('bbb','[email protected]')
--10000多条

Answer 2

select a._name,
_sent=sum(case when _sent=1 then 1 else 0 end),
_nosent=sum(case when _sent=1 then 0 else 1 end)
from users a
left join users_contact b
on a._id=b._id
group by a._name

Answer 3

select a._name,
_sent=sum(case when _sent=1 then 1 else 0 end),
_nosent=sum(case when _sent=1 then 0 else 1 end)
from users a
left join users_contact b
on a._name=b._name         ---修改下
group by a._name

Answer 4

while @row <=@maxrow 
begin 
set @name=(select _name from #tb where _indx=@row) 
update #tb set _sent=(select count(*) from users_contact where _name=@name and _sent=1), 
_nosent=(select count(*) from bigcomp_contact where _name=@name and _sent=0) 
where _indx=@row 
set @row=@row+1 
end 
就你这个循环都要花费很多时间了

Answer 5

4楼的是不错，可我不只是要查询users._name，还有其它字段，只是没贴出来，比如users._sex,users._age

Answer 6

那就都加上就是了哦
select a._name,a._sex,a._age         ---加 ,a._sex,a._age
_sent=sum(case when _sent=1 then 1 else 0 end),
_nosent=sum(case when _sent=1 then 0 else 1 end)
from users a
left join users_contact b
on a._name=b._name         ---修改下
group by a._name,a._sex,a._age       ---加 ,a._sex,a._age
     

Answer 7

select a._name,a._sex,a._age,
sum(case when _sent=1 then 1 else 0 end) as _sent,
sum(case when _sent=1 then 0 else 1 end) as _nosent
from users as a,users_contact as b
where a._name=b._name
group by a._name,a._sex,a._age

Answer 8

create table users 
( 
_id int identity(1,1), 
_name varchar(200) 
) 
insert into users(_name) values('aaa') 
insert into users(_name) values('bbb') 
insert into users(_name) values('ccc')
--共2000条 create table users_contact 
( 
_id int identity(1,1), 
_name varchar(200), 
_email varchar(200), 
_sent int default 0 
) 
insert into users_contact(_name,_email) values('aaa','[email protected]') 
insert into users_contact(_name,_email) values('aaa','[email protected]') 
insert into users_contact(_name,_email) values('bbb','[email protected]') select * from users_contact uc;create table #tb 
( 
_indx int identity(1,1), 
_id int, 
_name varchar(200), 
_sent int, 
_nosent int 
) delete from #tb;insert into #tb(_id,_name,_sent,_nosent) 
select u._id,u._name,isnull(t._sent,0) _sent,isnull(t._nosent,0) _nosent
from  users u left join ( select _name,
       sum(case when uc._sent=1 then 1 else 0 end) as _sent,
       sum(case when uc._sent=1 then 0 else 1 end) as _nosent
 from  users_contact uc group by uc._name ) t on u._name=t._name
order by u._name;select * from #tb;

Answer 9

--楼主：你那users表和users_contact的_id字段都用identity，这样对吗？
--那就两表不能通过_id来关联了，只能通过_name来关联？ 

Answer 10

4楼的是不对的：
如果users表中存在用户，而此用户在users_contact表中无记录行时，就会出错误的数据!
drop table users;
drop table users_contact;
create table users 
( 
_id int identity(1,1), 
_name varchar(200) 
) 
insert into users(_name) values('aaa') 
insert into users(_name) values('bbb') 
insert into users(_name) values('ccc')
--共2000条 create table users_contact 
( 
_id int identity(1,1), 
_name varchar(200), 
_email varchar(200), 
_sent int default 0 
) 
insert into users_contact(_name,_email) values('aaa','[email protected]') 
insert into users_contact(_name,_email) values('aaa','[email protected]') 
insert into users_contact(_name,_email) values('bbb','[email protected]') select a._name,
_sent=sum(case when _sent=1 then 1 else 0 end),
_nosent=sum(case when _sent=1 then 0 else 1 end)
from users a
left join users_contact b
on a._name=b._name         ---修改下
group by a._name------------------------------------------
aaa 0 2
bbb 0 1
ccc 0 1

Answer 11

----4楼的代码应该这样修改一下：---小卒的代码应该这样修改：
select a._name,
_sent=sum(case when _sent=1 then 1 else 0 end), --这样：在else语句中就包括了空值(当用户users_contact表中无记录时)
_nosent=sum(case when _sent=0 then 1 else 0 end)
from users a
left join users_contact b
on a._name=b._name         
group by a._name