5
回答
多表子查询sql优化问题
利用AWS快速构建适用于生产的无服务器应用程序,免费试用12个月>>>   

请看一下语句如何优化,我自己看得都有点别扭,但有想不到有什么更好的方法

想要实现的效果是统计产品订购排名,total-订购量,to_number-会员推荐量,r_number-成功推荐量,nyl_vas_orders-订购表(to_number字段不为空是为推荐记录,rstate=1是推荐成功)

select
count(nvo.id) as total,
(
    select count(nvo1.id)
    from nyl_vas_orders as nvo1
    where nvo1.to_number!='' and nvo1.app_id 
    in (
        select na1.id from nyl_archives as na1 where na1.typeid=na.typeid)
    )
as to_number,
(
    select count(nvo2.id)
        from nyl_vas_orders as nvo2
        where nvo2.to_number!='' and nvo2.rstate = 1 and nvo2.app_id 
        in (
            select na1.id from nyl_archives as na1 where na1.typeid=na.typeid)
        )
as r_number,
nat.typename,nat.id as type_id from nyl_vas_orders as nvo
left join nyl_archives as na on nvo.app_id=na.id
left join nyl_arctype as nat on na.typeid=nat.id
group by na.typeid
order by total desc
limit 10

举报
haigou20
发帖于5年前 5回/978阅
顶部