在Join子句中使用Count Distinct子查询的SQL查询速度较慢
在Join子句中使用Count Distinct子查询的SQL查询速度较慢
提问于 2019-01-16 08:41:53
使用下面的查询，在连接情况下，如果没有这个子查询，它会工作得很好。有什么更有效的方法可以做到这一点吗？目前还不清楚这是否是因为子查询正在为项中的所有记录运行，并且没有遵守where = cms.orderno...谢谢你的帮助！
子查询：
select count(distinct(ship_to))
from items
where cms.orderno = items.orderno and ship_to <> 0完整查询：
select
cms.custnum,
cms.odr_date,
cms.orderno,
internetid,
ltrim(rtrim(cust.firstname))+' '+ltrim(rtrim(cust.lastname)) as recipient,
ord_total,
ordertype,
order_st2,
websitestatdesc,
holdcode
from
cms
join statdesc on cms.order_st2 = statdesc.statcode
join items on cms.orderno = items.orderno
join cust on
case
when (
select count(distinct(ship_to))
from items
where cms.orderno = items.orderno and ship_to <> 0
) = 1 then items.ship_to
when cms.shipnum <> 0 then cms.shipnum
else cms.custnum
end = cust.custnum
where
cms.custnum = 3300
and statdesc.stattype = 'O'
group by
cms.orderno,
cms.custnum,
cms.odr_date,
internetid,
firstname,
lastname,
ord_total,
ordertype,
order_st2,
websitestatdesc,
holdcode
order by cms.orderno desc回答 1
Stack Overflow用户
回答已采纳
发布于 2019-01-16 09:37:39
首先，您对表items中的列orderno有索引吗？我想你不会返回太多行...所以这里缺失的索引可能是个杀手。
然后，我倾向于修改查询，以避免需要group by，如下所示(当然，假设我理解您的意图)：
select
cms.custnum
, cms.odr_date
, cms.orderno
, internetid
, ltrim(rtrim(cust.firstname))+' '+ltrim(rtrim(cust.lastname)) as recipient
, ord_total
, ordertype
, order_st2
, websitestatdesc
, holdcode
from (
select *, (
select count(distinct(ship_to))
from items
where cms.orderno = items.orderno and ship_to <> 0
) distinct_ship_to_count
, (
select top 1 ship_to
from items
where cms.orderno = items.orderno and ship_to <> 0
) distinct_ship_to
from cms
) cms
join statdesc on cms.order_st2 = statdesc.statcode
--join items on cms.orderno = items.orderno
join cust on
case
when cms.distinct_ship_to_count = 1 then cms.distinct_ship_to
when cms.shipnum <> 0 then cms.shipnum
else cms.custnum
end = cust.custnum
where cms.custnum = 3300
and statdesc.stattype = 'O'
--group by cms.orderno, cms.custnum, cms.odr_date, internetid, firstname, lastname, ord_total, ordertype, order_st2, websitestatdesc, holdcode
order by cms.orderno desc页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54208877
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号