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

[路人乙e]

有两张表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

[luoyoumou]

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','[e-mail]') 
insert into users_contact(_name,_email) values('aaa','[e-mail]') 
insert into users_contact(_name,_email) values('bbb','[e-mail]') 

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;

[luoyoumou]

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

[路人乙e]

js_szy：谢了，在下愚钝了！

[luoyoumou]

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','[e-mail]') 
insert into users_contact(_name,_email) values('aaa','[e-mail]') 
insert into users_contact(_name,_email) values('bbb','[e-mail]') 



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

[luoyoumou]

----------楼主：庸才一个!

[luoyoumou1202]

----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

[guguda2008]

一小撮不明真相的烤鸭路过

[sgtzzc]

楼主不如把测试数据贴出来，说说你要什么结果好一点

[路人乙e]

数据就是这样：
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','[e-mail]')
insert into users_contact(_name,_email) values('aaa','[e-mail]')
insert into users_contact(_name,_email) values('bbb','[e-mail]')
--10000多条

[华夏小卒]

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

[华夏小卒]

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

[SQL77]

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


就你这个循环都要花费很多时间了

[路人乙e]

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

[华夏小卒]

[Quote=引用 6 楼 sq_zhuyi 的回复:]
4楼的是不错，可我不只是要查询users._name，还有其它字段，只是没贴出来，比如users._sex,users._age
[/Quote]

那就都加上就是了哦

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
     

[navy887]

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