SQL语句积累2:行列转换、case when筛选条件
1.
declare @sql varchar(8000)
set @sql='select name'
set @sql=@sql+',sum(case subject when'''+subject+'''then result else 0 end)['+subject']'from (select distinct subject from t_person)as t_person
set @sql=@sql+'from t_person group by name'
exec(@sql)
行列转换:
2.
select * from t_person
ID name subject result
1 张三 语文 80
2 张三 数学 90
3 张三 物理 85
4 李四 语文 85
5 李四 物理 82
6 李四 英语 90
7 李四 政治 70
8 王五 英语 90
select name,
sum(case when subject='语文' then result else 0 end)as '语文',
sum(case when subject='数学' then result else 0 end)as '数学',
sum(case when subject='物理' then result else 0 end)as '物理',
sum(case when subject='政治' then result else 0 end)as '政治',
sum(case when subject='英语' then result else 0 end)as '英语'
from t_person group by name
name 语文 数学 物理 政治 英语
李四 85 0 82 70 90
王五 0 0 0 0 90
张三 80 90 85 0 0
3.
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [year]
,[month]
,[amount]
FROM [Test].[dbo].[t_contry]
year month amount
1996 1 200
1996 2 300
1996 3 400
1996 5 500
1997 1 20
1997 2 30
1997 3 40
1997 5 50
select year,
sum(case when month=1 then amount else 0 end )as 'm1',
sum(case when month=2 then amount else 0 end )as 'm2',
sum(case when month=3 then amount else 0 end)as 'm3',
sum(case when month=5 then amount else 0 end)as 'm4'
from t_contry group by year
year m1 m2 m3 m4
1996 200 300 400 500
1997 20 30 40 50
1.连接查询中的控制(case when的用法)
两张表:
A:
Id int primary key,
Personid int not null,
Name nchar(10),
Age nchar(10),
Address nchar(10),
Memo nchar(10)
B:
Id int primary key,
Personid nchar(10) not null,
Photo nchar(10)
Addtime nchar(10)
数据如下:
要求:查询出以下结果
要求:a表中memo为”第一阶段”的B表中photo中取该人的“合格的照片”,若a表中
Memo为“第二阶段”则B表中取该人的“新照片”.
答案:
select a.name,a.memo, b.photo from a left join b on a.personid=b.personid
where photo=(case memo when '第一阶段' then '合格的照片' when '第二阶段' then '新照片' end)
知识:
select a.name,a.memo, b.photo from a cross join b //交叉连接(两表行数乘积)
select a.name,a.memo, b.photo from a inner join b on a.personid=b.personid//内连接(两表的笛卡尔积)有重复
select a.name,a.memo, b.photo from a full join b on a.personid=b.personid//全外连接=左外连接union 右外连接