日度归档:2018年5月9日

mssql 按日期分组(group by)查询统计的时候,没有数据补0的解决办法


摘要:
下文讲述一次报表制作的需求,
需制作一个月的销量的数据汇总,如果其中某一天没有数据,那么就补0处理


例:

 /*
统计2018-4月份的销量统计,
无数据的天补0
*/
---建立基础数据
create table saleInfo(dateInfo datetime,qty int )
go

insert 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_values 
where type='p' 
and number >=0 
and dateadd(day,number,'2018-4-1') between '2018-4-1' and dateadd(day,-1,'2018-5-1')

) as a 
left join 
(
 ---按天汇总的销量数据
 select convert(varchar,dateInfo,111) as d,sum(qty) as qty from saleInfo group by  convert(varchar,dateInfo,111)
) as b 
 on  convert(varchar,a.d,111) = b.d 



go
truncate table saleInfo
drop table saleInfo 

mssql sqlserver master数据库spt_values表简介


摘要:
下文讲述spt_values的常用场景


spt_values在mssql中主要保存一些枚举值,此表是从sybase继承过来,通常我们称此表为”常量表”。

  ----表数据展示
  select * from master..spt_values
  ----连续数据展示
  select * from master..spt_values where type='P'

mssql_sqlserver_master_spt_values展示

mssql_sqlserver_master_spt_values展示


mssql_sqlserver_master_spt_values连续数据展示

mssql_sqlserver_master_spt_values连续数据展示


在spt_values表中,我们常使用0~2047个自然数,应用于连续时间的临时生成
例1:
生成2018年的连续月份

 ----从2018-1-1开始截取,只取12个月份
    select 
      left(convert(varchar(10),dateadd(month,number,'2018-01-01'),120),7) as [年月样式]
     from master..spt_values 
     where   
     number <=11 
     and type='p'
 
mssql_sqlserver_master_spt_values连续月份获取

mssql_sqlserver_master_spt_values连续月份获取

mssql sqlserver 将指定时间转换为hh:mm样式的方法分享


摘要:
下文将分享sql server中,将时间数据类型转换为hh:mm,
类似程序中toString(“hh:mm”)的操作方法分享


 --实现方法:
--1.获取time部分
--2.取左边开始的5位字符得到hh:mm样式
select 
 convert(varchar,getdate(),108)
,left(convert(varchar,getdate(),108),5) 
 
mssql_sqlserver_获取time转换hh:mm样式

time转换为hh:mm
date转换为hh:mm