大型网站建站,有什么网站可以做3d,大学网站建设多少钱,网站搭建教程导航类摘要:下文讲述一次报表制作的需求#xff0c;需制作一个月的销量的数据汇总#xff0c;如果其中某一天没有数据#xff0c;那么就补0处理例:/*统计2018-4月份的销量统计#xff0c;无数据的天补0*/---建立基础数据create table saleInfo(dateInfo datetime,qty int )goinse…摘要:下文讲述一次报表制作的需求需制作一个月的销量的数据汇总如果其中某一天没有数据那么就补0处理例:/*统计2018-4月份的销量统计无数据的天补0*/---建立基础数据create table saleInfo(dateInfo datetime,qty int )goinsert into saleInfo (dateinfo,qty)values(2018-4-1,5)insert into saleInfo (dateinfo,qty)values(2018-4-2,8)insert into saleInfo (dateinfo,qty)values(2018-4-2,18)insert into saleInfo (dateinfo,qty)values(2018-4-3,10)insert into saleInfo (dateinfo,qty)values(2018-4-8,9)insert into saleInfo (dateinfo,qty)values(2018-4-10,6)insert into saleInfo (dateinfo,qty)values(2018-4-15,28)insert into saleInfo (dateinfo,qty)values(2018-4-20,152)insert into saleInfo (dateinfo,qty)values(2018-4-21,36)insert into saleInfo (dateinfo,qty)values(2018-4-22,72)insert into saleInfo (dateinfo,qty)values(2018-4-28,20)insert into saleInfo (dateinfo,qty)values(2018-4-4,12)go--按日期汇总但是无销售数据的天数无法出现---select convert(varchar,dateInfo,111) as d,sum(qty) as qty from saleInfo group by convert(varchar,dateInfo,111)----采用系统常量表spt_values表辅助补0操作select convert(varchar,a.d,111) as [日期],isnull(b.qty,0) as [销量]from(---生成4月份所有的天数select dateadd(day,number,2018-4-1) as d from master..spt_valueswhere typepand number 0and dateadd(day,number,2018-4-1) between 2018-4-1 and dateadd(day,-1,2018-5-1)) as aleft join(---按天汇总的销量数据select convert(varchar,dateInfo,111) as d,sum(qty) as qty from saleInfo group by convert(varchar,dateInfo,111)) as bon convert(varchar,a.d,111) b.dgotruncate table saleInfodrop table saleInfo
