美女总裁的贴身保安,最好听的嗨歌,新会虫草花
最近面试遇到了一道面试题,顿时有点迷糊,只说出了思路,后来百度了一下,整理了一下思路,于是记录下来,方便以后学习。(面试题请参见附件)
相关的数据表:
1.score表
2.[user]表
sql语句如下:
--方法一:静态sql
select * from
(select uid,name, score,scorename from score,[user] where score.uid=[user].id) as sourcetable
pivot(avg(score)for scorename in ([英语], [数学])) as a
--方法二:动态sql
declare @s nvarchar(4000)
select @s = isnull(@s + ',', '') + quotename(scorename)
from (select distinct scorename from score) as a ---列名不要重复
declare @sql nvarchar(4000)
set @sql='
select r.* from
(select uid,name,scorename,score from score,[user] where score.uid=[user].id) as t
pivot
(
max(t.score)
for t.scorename in ('+@s+')
) as r'
exec( @sql)
--方法三:case when
select
row_number() over(order by [user].id) as 编号,
uid as 用户编号,
name as 姓名,
max(case scorename when '英语' then score else 0 end) 英语,
max(case scorename when '数学' then score else 0 end) 数学
from score,[user] where score.uid=[user].id
group by uid,[user].id,name
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!!
点击进行留言回复
相关文章:
-
-
详解SQL游标的用法
类型: 1.普通游标 只有next操作 2.滚动游标 有多种操作1.普通游标declare @username varchar(20),@userid varc...
[阅读全文]
-
-
-
-
-
-
-
-
-
网友评论