SQL Server 统计 行转列 百分比 日期

1、按【年】统计

select year(created_time) 年,count(*) 数量
from sys_user
group by year(created_time)

2、按【季度】统计

select datename(quarter,created_time) 季度,count(*) total
from sys_user
where year(created_time) = '2020' 
group by datename(quarter,created_time) 

3、按【月】统计

select year(created_time) 年,month(created_time) 月,count(*) 数量
from sys_user
group by year(created_time),month(created_time)
GO

select month(created_time) 月份,count(*) 数量 
from sys_user 
where year(created_time) = '2020' 
group by month(created_time) 
GO

4、按【日期】统计

select convert(nvarchar(10),created_time,23),count(*) total
from sys_user
where 1=1 
--and DateDiff(dd,created_time,getdate())<=7 --7天内
--and DateDiff(mm,created_time,getdate())=0 --本月
group by convert(nvarchar(10),created_time,23)
order by convert(nvarchar(10),created_time,23) asc

5、输出【本月】全部日期

with t as 
( 
    select convert(varchar(10),dateadd(dd,number,convert(varchar(8),getdate(),120)+'01'),120) as dt
    from master..spt_values 
    where type='P' 
    and dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,getdate()),120)+'01')
)
select * from t t1
GO

with t as 
( 
    select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) day from 
    (select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1, 
    (select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2 
    where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%'
)
select * from t t1
GO

6、输出【本周】全部日期

with t as 
( 
    select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 0),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 1),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 2),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 3),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 4),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 5),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 6),120)
)
select * from t t1


--案例
with t as 
( 
    select rq=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 0),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 1),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 2),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 3),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 4),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 5),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 6),120)
)
select 
  t1.*,
  'total' = 
  case
    when total is null then '0'
    else total
  end
from t t1
left join 
(
select top 10 convert(nvarchar(10),created_time,23) rq,count(*) total
from base_patient
where 1=1 
and created_time >= DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) and created_time <= DATEADD(day,1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 6))
group by convert(nvarchar(10),created_time,23)
order by convert(nvarchar(10),created_time,23) asc
) t2 on t2.rq = t1.rq

--参考
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
select DATEADD(day,1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 6))

select convert(nvarchar(10),DATEADD(wk, DATEDIFF(wk,0,getdate()), 0),23)
select convert(nvarchar(10),DATEADD(day,1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)),23)

7、输出【7天前】全部日期

with t as 
( 
    select dateadd(day,number,convert(date,dateadd(DD,-7,getdate()),23)) as RQ
    from master.dbo.spt_values 
	where type ='P'
    and number <= DATEDIFF(day, convert(date,dateadd(DD,-6,getdate()),23), GETDATE())
)
select * from t t1
GO

--案例
with t as 
( 
    select dateadd(day,number,convert(date,dateadd(DD,-7,getdate()),23)) as rq
    from master.dbo.spt_values 
	where type ='P'
    and number <= DATEDIFF(day, convert(date,dateadd(DD,-6,getdate()),23), GETDATE())
)

select 
  t1.*,
  'total' = 
  case
    when total is null then '0'
    else total
  end
from t t1
left join 
(
select top 10 convert(nvarchar(10),created_time,23) rq,count(*) total
from sys_user
where DateDiff(dd,created_time,getdate())<=7 --7天内
group by convert(nvarchar(10),created_time,23)
order by convert(nvarchar(10),created_time,23) asc
) t2 on t2.rq = t1.rq
GO

8、性别统计（case when then）行转列

--行专列性别统计
select sum(男) as 男,sum(女) as 女 from (
select sex,
sum(case when sex = '男' then 1 else 0 end) '男',
sum(case when sex = '女' then 1 else 0 end) '女'
from base_patient
group by sex) as s

--多列显示
select sex_group,count(*) as total from (
select top 100
    'sex_group' = 
    case 
		when sex = '男' then '男'
        when sex = '女' then '女'
        else '' 
    end
from base_patient
order by sex) t
group by sex_group
 
--多列显示
select sex,
sum(case when sex = '男' then 1 else 0 end) '男',
sum(case when sex = '女' then 1 else 0 end) '女'
from base_patient
group by sex

9、年龄段统计

--列表
select real_name,age,
    'age group' = 
    case 
        when age is null then '' 
		when age between 0 and 6 then '婴幼儿'
		when age between 7 and 12 then '少儿' 
		when age between 13 and 17 then '青少年' 
		when age between 18 and 45 then '青年' 
        when age BETWEEN 46 and 69 then '中年'
		when age > 69 then '老年'
        else '' 
    end
from sys_user 
order by age
GO

--统计
select age_group,count(*) from (
select top 100 real_name,age,
    'age_group' = 
    case 
        when age is null then '' 
		when age between 0 and 6 then '婴幼儿'
		when age between 7 and 12 then '少儿' 
		when age between 13 and 17 then '青少年' 
		when age between 18 and 45 then '青年' 
        when age BETWEEN 46 and 69 then '中年'
		when age > 69 then '老年'
        else '' 
    end
from sys_user
order by age) t
group by age_group

10、年龄段统计

--行转列
select sum(婴儿) as 婴儿,sum(幼儿) as 幼儿,sum(小) as 小,sum(初) as 初,sum(高) as 高,sum(大) as 大 from (
  select age,
  sum(case when age between 1 and 3 then 1 else 0 end) as 婴儿,
  sum(case when age between 4 and 6 then 1 else 0 end) as 幼儿,
  sum(case when age between 7 and 12 then 1 else 0 end) as 小,
  sum(case when age between 13 and 15 then 1 else 0 end) as 初,
  sum(case when age between 16 and 18 then 1 else 0 end) as 高,
  sum(case when age between 19 and 22 then 1 else 0 end) as 大
from sys_user
group by age) as s

--多列
select age,
  sum(case when age between 1 and 3 then 1 else 0 end) as 婴儿,
  sum(case when age between 4 and 6 then 1 else 0 end) as 幼儿,
  sum(case when age between 7 and 12 then 1 else 0 end) as 小,
  sum(case when age between 13 and 15 then 1 else 0 end) as 初,
  sum(case when age between 16 and 18 then 1 else 0 end) as 高,
  sum(case when age between 19 and 22 then 1 else 0 end) as 大
from sys_user
group by age

11、学号    姓名    语文     数学     英语     总分  平均分

select stuid as 学号,name as 姓名,
sum(case when subject='语文' then score else 0 end) as 语文,
sum(case when subject='数学' then score else 0 end) as 数学,
sum(case when subject='英语' then score else 0 end) as 英语,
sum(score) as 总分,(sum(score)/count(*)) as 平均分
from stuscore
group by stuid,name 
order by 总分 desc

12、百分百

--公式
select cast(round(总个数/cast(总金额 as float) * 100,2) as varchar(10)) + '%'

--案例
select cast(round(3/cast(61 as float) * 100,2) as varchar(10)) + '%'

--案例
select cast(round(count1/cast(total as float) * 100,2) as varchar) + '%' 
from (select sum(money) as total,count(money) as count1 from base_asset)t,base_asset

*
*
*