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