select username,joindate,lstlogindate,supticket,referral,onlinetime
,(select count(*) from buylog where a.id = usedid ) as sumcnt ,isnull((select sum(gross)
from buylog
where a.id = usedid),0)
as tmpgross from v_userdetail as a
where referral = @id
order by supticket desc我用SQL 2005的执行计划看了一下
这条语句红色部分分别要读两次表
为了改进,我把两条语句中的变量合在一起形成了如下语句
select username,joindate,lstlogindate,supticket,referral,onlinetime
,(select count(*) as sumcnt,isnull(sum(gross),0) as sumgross from buylog where a.id = usedid ) from v_userdetail as a
where referral = @id
order by supticket desc
但是该语句由于在select中包含两列,所以sql报错
然后既然要包含一页,我有改写成
select username,joindate,lstlogindate,supticket,referral,onlinetime,sumcnt,sumgross
from v_userdetail as a inner join
(select count(*) as sumcnt,sum(gross) as sumgross,b.usedid as idc
from buylog as b where a.id = b.usedid group by b.usedid ) as c
on a.id = c.idc
where referral = @id
order by supticket desc
这次虽然成功了,但是我在显示执行计划时看到由于红色语句没有用where导致了表扫描没用索引,这样性能反而没前面的高,请问各位还有什么好方法能把对buylog两列的统计只用一次查询就可实现,希望大家能帮忙,小生先谢过
,(select count(*) from buylog where a.id = usedid ) as sumcnt ,isnull((select sum(gross)
from buylog
where a.id = usedid),0)
as tmpgross from v_userdetail as a
where referral = @id
order by supticket desc我用SQL 2005的执行计划看了一下
这条语句红色部分分别要读两次表
为了改进,我把两条语句中的变量合在一起形成了如下语句
select username,joindate,lstlogindate,supticket,referral,onlinetime
,(select count(*) as sumcnt,isnull(sum(gross),0) as sumgross from buylog where a.id = usedid ) from v_userdetail as a
where referral = @id
order by supticket desc
但是该语句由于在select中包含两列,所以sql报错
然后既然要包含一页,我有改写成
select username,joindate,lstlogindate,supticket,referral,onlinetime,sumcnt,sumgross
from v_userdetail as a inner join
(select count(*) as sumcnt,sum(gross) as sumgross,b.usedid as idc
from buylog as b where a.id = b.usedid group by b.usedid ) as c
on a.id = c.idc
where referral = @id
order by supticket desc
这次虽然成功了,但是我在显示执行计划时看到由于红色语句没有用where导致了表扫描没用索引,这样性能反而没前面的高,请问各位还有什么好方法能把对buylog两列的统计只用一次查询就可实现,希望大家能帮忙,小生先谢过
username,joindate,lstlogindate,supticket,referral,onlinetime,
isnull(count(b.usedid),0) as sumcnt ,
isnull(sum(b.gross) ,0) as tmpgross
from
v_userdetail a
left join
buylog b
on
a.id=b.usedid
where
referral = @id
order by
supticket desc
select username,joindate,lstlogindate,supticket,referral,onlinetime,isnull(sumcnt,0) as sumcnt,isnull(sumgross ,0) as sumgross
from v_userdetail as a
left join
(select b.usedid as idc ,count(*) as sumcnt,sum(gross) as sumgross
from buylog as b group by b.usedid ) as c
on a.id = c.idc
where referral = @id
order by supticket desc
select username,joindate,lstlogindate,supticket,referral,onlinetime,sumcnt,sumgross
from v_userdetail as a inner join
(select count(*) as sumcnt,sum(gross) as sumgross,b.usedid as idc
from buylog as b where a.id = b.usedid and referral = @id group by b.usedid ) as c
on a.id = c.idc
order by supticket desc
1、用连接代替子查询;
2、用case语句代替查询内容的分类信息;
3、减少连接表的数量;
4、使用临时表或表变量。
消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 'v_userdetail.Username' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
playwarcraft 的方法执行的确行数返回变少了,原来只要用left join就行啦,谢谢dawugui 的方法执行后报错
消息 4104,级别 16,状态 1,第 1 行
无法绑定由多个部分组成的标识符 "a.id"。大家还有什么方法吗?