表:orders,字段:orderID,orderdate(订购日期),orderValue(订购金额),EmployeeID(员工编号)我用下面sql语句:
select Datepart(weekday,orderdate) as DayNum,isnull(sum(Orders.OrderMoney),0) as DaySale from [orders]
where DateDiff(wk, orderdate, GetDate()) = 0 group by Datepart(weekday,orderdate)
得到如下格式数据:
星期 金额
1 29999
2 4000
4 50000
5 50000
6 50000
没有周末和周三的数据
现在要求如下格式的数据,当天没有订购的金额也要显示出来:
1 29999
2 4000
3 0
4 50000
5 50000
6 50000
7 0
在线等答案
select Datepart(weekday,orderdate) as DayNum,isnull(sum(Orders.OrderMoney),0) as DaySale from [orders]
where DateDiff(wk, orderdate, GetDate()) = 0 group by Datepart(weekday,orderdate)
得到如下格式数据:
星期 金额
1 29999
2 4000
4 50000
5 50000
6 50000
没有周末和周三的数据
现在要求如下格式的数据,当天没有订购的金额也要显示出来:
1 29999
2 4000
3 0
4 50000
5 50000
6 50000
7 0
在线等答案
select '1' daynum
union
select '2'
union
select '3'
union
select '4'
union
select '5'
union
select '6'
union
select '7') a left join
(select Datepart(weekday,orderdate) as DayNum,isnull(sum(Orders.OrderMoney),0) as DaySale from [orders]
where DateDiff(wk, orderdate, GetDate()) = 0 group by Datepart(weekday,orderdate)
) b
where a.daynum = b.datanum