日度归档:2020年1月11日

mssql sqlserver 如何将多个sum结果相加


摘要:
下文讲述sqlserver中将多个sum结果相加的方法分享,如下所示:
实验环境:sql server 2008 r2


实现思路:
将sum查询出的结果,作为子查询进行二次查询,就可以达到将两个sum进行相加的目的

 create table [maomao365.com](qtyA int,qtyB int)
go
insert [maomao365.com] values(1,20),
(80,20),(60,30),(20,10),(30,60) 
go

select * from 
(
select sum(qtyA) as A,
sum(qtyB) as B
 from [maomao365.com]
 ) as t 

 go
----输出------
191	140
  
go
 truncate table [maomao365.com]
 drop     table [maomao365.com]

mssql sqlserver 如何获取签到日期的连续段及连续天数呢?


摘要:
下文讲述sqlserver中获取连续时间段及所对应的天数的方法分享,如下所示:
实验环境:sql server 2008 R2

create table [maomao365.com](rq datetime)
go
insert [maomao365.com] values('2020.1.1')
insert [maomao365.com] values('2020.1.2')
insert [maomao365.com] values('2020.1.3')
insert [maomao365.com] values('2020.1.6')
insert [maomao365.com] values('2020.1.7')
insert [maomao365.com] values('2020.1.10')
insert [maomao365.com] values('2020.1.11')
insert [maomao365.com] values('2020.1.12')
insert [maomao365.com] values('2020.1.19')
insert [maomao365.com] values('2020.1.20')
insert [maomao365.com] values('2020.1.22')
insert [maomao365.com] values('2020.1.23')
insert [maomao365.com] values('2020.1.28')
go

SELECT 连续签到起始日期 = MIN(rq)
, 连续签到终止日期 = MAX(rq)
, 签到天数 = MAX(id1) - MIN(id1) + 1
, 距上一次签到天数 = CASE a.id1 - a.id2
WHEN -1 THEN 0
ELSE MAX(datediff(d, rq2, rq))
END
FROM (
SELECT id1 = datediff(d, '2010-01-01', rq)
, id2 = (
SELECT COUNT(1)
FROM [maomao365.com]
WHERE rq <= a.rq ), rq2 = ( SELECT MAX(rq) FROM [maomao365.com] WHERE rq < a.rq ) , * FROM [maomao365.com] a ) a GROUP BY a.id1 - a.id2 go truncate table [maomao365.com] drop table [maomao365.com]

mssql_sqlserver_获取连续签到信息

mssql_sqlserver_获取连续签到信息

select 如何对列进行求和操作呢?


摘要:
下文讲述select查询数据时,对列进行求和操作的方法分享,如下所示:


实现思路:
方法1:
使用group by 对列进行聚合汇总
方法2:
使用开窗函数over在数据表新增一个汇总

例:
对数据表”maomao365.com”中的qty列,按照sort进行汇总操作

create table [maomao365.com]
(sort varchar(20),
qty int)
go
---生成基础数据
insert into [maomao365.com]
(sort,qty)values
('maomao',100),('maomao',80),
('sqlserver',20),('sqlserver',60),
('mysql',30)
go

---方法1:使用group by生成汇总数据
select sort,sum(qty) as qtyAll
 from [maomao365.com]
 group by sort 

---方法2:使用over函数生成汇总数据
select * from 
(
select sort,
sum(qty) over(partition by sort)  
as qtyAll,
row_number() over(partition by sort order by sort )
as keyId
 from [maomao365.com]
 ) as t where t.keyId  =1 
  
go
truncate table [maomao365.com]
drop     table [maomao365.com]
go