pid是用户来源属性.cash为币.我想查出所有用户一段时间内的消费.
下语句为把一段时间内的消费用户查出来拉..
怎么修改能够把所有的用户全部显示出来?
SELECT Pid,COUNT(*) AS Num,ISNULL(SUM(PayHistory.Cash),0) AS Cash
FROM User LEFT JOIN PayHistory ON User.UserName=PayHistory.UserName
WHERE PayTime>=@StartDate AND PayTime<=@EndDate
GROUP BY Pid
ORDER BY Num DESC
下语句为把一段时间内的消费用户查出来拉..
怎么修改能够把所有的用户全部显示出来?
SELECT Pid,COUNT(*) AS Num,ISNULL(SUM(PayHistory.Cash),0) AS Cash
FROM User LEFT JOIN PayHistory ON User.UserName=PayHistory.UserName
WHERE PayTime>=@StartDate AND PayTime<=@EndDate
GROUP BY Pid
ORDER BY Num DESC
FROM User LEFT JOIN PayHistory ON User.UserName=PayHistory.UserName
WHERE PayTime>=@StartDate AND PayTime<=@EndDate
GROUP BY Pid
ORDER BY Num DESC
union
select pid,count(*),0 from User where pid not in (
select pid from User WHERE PayTime>=@StartDate AND PayTime<=@EndDate
) group by pid
FROM User LEFT JOIN PayHistory ON User.UserName=PayHistory.UserName
WHERE PayTime>=@StartDate AND PayTime<=@EndDate
GROUP BY Pid
ORDER BY Num DESC
union
select pid,count(*),0 from User where pid not in (
select pid from User WHERE PayTime>=@StartDate AND PayTime<=@EndDate
union
select '不为空,数据库中没有的一个任意数'
) group by pid为什么注意union
select '不为空,数据库中没有的一个任意数'
因为not in 后面如果没有数据的话,这个就检索不出来,所以随便加一个不存在的数据
(select distinct pid from User )t1,
(SELECT Pid,COUNT(*) AS Num,ISNULL(SUM(PayHistory.Cash),0) AS Cash
FROM User LEFT JOIN PayHistory ON User.UserName=PayHistory.UserName
WHERE PayTime>=@StartDate AND PayTime<=@EndDate
GROUP BY Pid
ORDER BY Num DESC
) t2
where t1.pid *=t2.pid
这个也ok