日期:2014-05-17  浏览次数:20517 次

高分,在线等一条表联的sql语句
有两张表,用户名cus_user与订单表D_order,订单表里有cus_userId的外键
现在要求是:
查出来有订单的用户名及订单数量,按数量从大到小排,如果数据不够10条,则以其它用户补充,订单数量显示为0按用户的编号排。

SQL code

select cu.loginName, count(*) as num from d_order o left join cus_user cu on o.cus_userId=cu.autoId 
group by cu.loginName order by num desc



这是在我的sql语句,只能查出来8条数据,但是用户还有很多,都是没有下过订单的,就是怎么样能从这些没有下过订单的用户里面从最后取两个(也就是按编号倒排)放到一起。

------解决方案--------------------
select top 10 cu.loginName, isnull(count(o.cus_userid),0) as num
from d_order o right join cus_user cu on o.cus_userId=cu.autoId
group by cu.loginName order by num desc, cu.loginName
------解决方案--------------------
SQL code
--1
select cu.loginName, isnull(count(o.cus_userid),0) as num   
from d_order o right join cus_user cu on o.cus_userId=cu.autoId
and  (orderType='ORDER_PRODUCT' or orderType='ORDER_GOODS') and orderStatusForCus='JYWC' 
group by cu.loginName order by num desc, cu.loginName 
--2

SELECT  cu.loginName, isnull(count(o.cus_userid),0) as num   FROM cus_user cu LEFT JOIN 
(
SELECT  cus_userId FROM d_order WHERE (orderType='ORDER_PRODUCT' or orderType='ORDER_GOODS') and orderStatusForCus='JYWC'
) o
on o.cus_userId=cu.autoId
group by cu.loginName order by num desc, cu.loginName