另一个透视SQL Server查询
另一个透视SQL Server查询
提问于 2011-06-24 00:31:25
不幸的是，我不能很好地理解前面的例子。
我正在尝试创建一个表格，基于多年来对棒球MVP的投票，并将其与现在可用的更客观的措施战争进行比较。我想根据这个值来看看排名前两位的球员之间的区别。下面的代码显示了几年来的许多
select season,division,player,rankMVP as MVP,WAR,rankWAR
from mlbmvpvoting
where (season = '1955' or season = '1956')
and rankWAR < 3
order by season,division,rankWAR
season division player MVP WAR rankWAR
1955 AL Mickey Mantle 5 9.5 1
1955 AL Al Kaline 2 9.0 2
1955 NL Willie Mays 4 9.3 1
1955 NL Duke Snider 2 8.9 2
1956 AL Mickey Mantle 1 12.9 1
1956 AL Early Wynn 13 8.5 2
1956 NL Duke Snider 10 7.7 1
1956 NL Hank Aaron 3 7.5 2我想做这样的东西
season division pl_1 pl_2 MVP_1 MVP_2 WAR_1 WAR_2
1955 AL Mickey Mantle Al Kaline 5 2 9.5 9.0
1955 N Willy Mays Duke Snider 4 2 9.3 8.9等
提亚
回答 2
Stack Overflow用户
回答已采纳
发布于 2011-06-24 02:08:09
使用分组和条件聚合，如下所示：
SELECT
season,
division,
pl_1 = MAX(CASE rankWAR WHEN 1 THEN player END),
pl_2 = MAX(CASE rankWAR WHEN 2 THEN player END),
MVP_1 = MAX(CASE rankWAR WHEN 1 THEN rankMVP END),
MVP_2 = MAX(CASE rankWAR WHEN 2 THEN rankMVP END),
WAR_1 = MAX(CASE rankWAR WHEN 1 THEN WAR END),
WAR_2 = MAX(CASE rankWAR WHEN 2 THEN WAR END)
FROM mlbmvpvoting
WHERE season IN ('1955', '1956')
AND rankWAR IN (1, 2)
GROUP BY season, division
ORDER BY season, divisionIN基本上比OR和<更优化，所以我相应地更改了条件。
Stack Overflow用户
发布于 2011-06-24 02:07:09
看看这样的解决方案是否适合您：
with mlbmvpvoting (season, division, player, MVP, WAR, rankWAR) as
(
select 1955, 'AL', 'Mickey Mantle', 5, 9.5, 1 union all
select 1955, 'AL', 'Al Kaline', 2, 9.0, 2 union all
select 1955, 'NL', 'Willie Mays', 4, 9.3, 1 union all
select 1955, 'NL', 'Duke Snider', 2, 8.9, 2 union all
select 1956, 'AL', 'Mickey Mantle', 1, 12.9, 1 union all
select 1956, 'AL', 'Early Wynn', 13, 8.5, 2 union all
select 1956, 'NL', 'Duke Snider', 10, 7.7, 1 union all
select 1956, 'NL', 'Hank Aaron', 3, 7.5, 2
)
select
season,
division,
pl_1 = max(case rankWAR when 1 then player end),
pl_2 = max(case rankWAR when 2 then player end),
MVP_1 = max(case rankWAR when 1 then MVP end),
MVP_2 = max(case rankWAR when 2 then MVP end),
WAR_1 = max(case rankWAR when 1 then WAR end),
WAR_2 = max(case rankWAR when 2 then WAR end)
from mlbmvpvoting
group by season, division
order by season;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/6457510
复制相关文章
相似问题
腾讯云开发者
