SQL SERVER 行转列 - SQL SERVER 条件查询 Case When结构

1.Sql Server 行转列 ,

select  count(*) as TotalCount,SchoolId,Type,
row_number()over(order by Type asc) as RowIds
from  ALL_UserLog
group by [Type] ,SchoolId 

查询前的结果集

通过SQL PRIVOT 查询


	with temp as (
		select  count(*) as TotalCount,SchoolId,Type,year(AddDate) as [Year]
		from  ALL_UserLog  
		group by [Type] ,SchoolId ,year(AddDate)
	)
 
	select [year],SchoolId,isnull(浏览,0)浏览,isnull(搜索,0)搜索,isnull(收藏,0)收藏,isnull(下载,0)下载 from temp
	pivot(
		sum(TotalCount) for Type in(浏览,搜索,收藏,下载)
	) as m

查询后的结果

2 SQL 条件查询采用case when结构

–分组查询统计总数

select count(*) as TotalCount,SchoolId,[Type],
row_number() over(order by [Type] asc) as RowIds,
max(case [Type] when '浏览' then TotalCount else 0 end ) as '浏览' ,
max(case [Type] when '搜索' then TotalCount else 0 end ) as '搜索' ,
max(case [Type] when '收藏' then TotalCount else 0 end ) as '收藏' ,
max(case [Type] when '下载' then TotalCount else 0 end ) as '下载' 
from ALL_UserLog 
group by SchoolId,[Type]
order by SchoolId

–统计某个类别的访问总数


select Count(*) from ALL_UserLog where  Modue ='M04' and
(
	 case when  AddDate between '2017-03-03'  and '2017-03-08'  then 1 else 0 end + 
	 case when [Type] = '浏览' then 1 else 0 end 
 ) >= 2

–后面>=2 是至少满足两个条件,这个可以根据需求来设定

select Count(*) from ALL_UserLog where  Modue ='M02' and
(
	 case when  AddDate between '2017-03-03'  and '2017-03-08'  then 1 else 0 end + 
	 case when [Type] = '浏览' then 1 else 0 end 
 ) >= 2

select Count(*) from ALL_UserLog where  Modue ='M03'  

select Count(*) from ALL_UserLog where  Modue ='M03' and
(
	 case when  AddDate between '2017-03-03'  and '2017-03-08'  then 1 else 0 end + 
	 case when [Type] = '浏览' then 1 else 0 end 
 ) >= 2

3.解决排序问题 1 2 3 4 5 ,,,, 是从1 10 11 12 2 到 1 2 3 4 5 的顺序。

比如第1章 第2章 第3章 ==》》第1章 第10章 第11章 第2章 第3章等问题。
采用字符串截取的方式。
sql 如下

select * from UM_Resource where CourseId='C0000308'
and TypeId='T4' 
order by convert(int, substring(name, charindex('第',name)+1,charindex('章',name)-2))

注意这里的格式写死了 要求文本的第一个第三个字符 必须是 “第” 跟 ”章“ 。
有什么其他好的方式欢迎在下面留言。谢谢大家。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值