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))

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