|
最近面试遇到了一道面试题，顿时有点迷糊，只说出了思路，后来百度了一下，整理了一下思路，于是记录下来，方便以后学习。（面试题请参见附件）
相关的数据表：
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,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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
