zje 总金额
sfrq 收费日期select SUM(zje) as zje ,CONVERT(VARCHAR(10),sfrq,120) as rq
from 表1
where datediff(dd,sfRQ,GETDATE())<=7 --收费日期跟今天7天内
GROUP BY CONVERT(VARCHAR(10),sfrq,120)
order by CONVERT(VARCHAR(10),sfrq,120) desc 本来是想这样写的,但这样写的话如果一天没收费,那天的日期就漏了,我希望统计出来的数据是,就算没收费,日期也能正常出来,只是费用为0,正常7条数据,不管有没费用都能显示
sfrq 收费日期select SUM(zje) as zje ,CONVERT(VARCHAR(10),sfrq,120) as rq
from 表1
where datediff(dd,sfRQ,GETDATE())<=7 --收费日期跟今天7天内
GROUP BY CONVERT(VARCHAR(10),sfrq,120)
order by CONVERT(VARCHAR(10),sfrq,120) desc 本来是想这样写的,但这样写的话如果一天没收费,那天的日期就漏了,我希望统计出来的数据是,就算没收费,日期也能正常出来,只是费用为0,正常7条数据,不管有没费用都能显示
--测试数据
;WITH tab(Date,Price)AS(
select '2017-04-04 02:09:42.393',160 union all
select '2017-04-05 02:09:42.393',200 union all
select '2017-04-07 02:09:42.393',300
)
--测试数据结束
SELECT DATEADD(DAY, -number, GETDATE()) AS Date ,
ISNULL(Price, 0) AS Price
FROM master..spt_values
LEFT JOIN tab ON DATEDIFF(DAY, DATEADD(DAY, -number, GETDATE()),
tab.Date) = 0
WHERE type = 'P'
AND number < 7;