mysql - 在 SQL 中编写 CASE 语句以包含多列

Question

我有一个相当直接的查询，如下所示:

SELECT
  max(case when site_id = 1 then cast(REPLACE(REPLACE(REPLACE(salary, '$',''),' ',''), ',', '') as UNSIGNED) end) fd_salary,
  max(case when site_id = 2 then cast(REPLACE(REPLACE(REPLACE(salary, '$',''),' ',''), ',', '') as UNSIGNED) end) dd_salary,
  max(case when site_id = 3 then cast(REPLACE(REPLACE(REPLACE(salary, '$',''),' ',''), ',', '') as UNSIGNED) end) ss_salary
FROM mytable 
WHERE gamedate = '2014-07-01'
GROUP BY player_id;

这按预期工作。我在 mytable 中有一个 salary 列，我试图将 player_id 的所有 salaries 放入一行当我运行我的 select 语句时，因为现在它们存储在自己的行中。我的问题如下:除了 salary 之外，如何向此 select 语句中再添加一列？除了 salary 列之外，我还希望能够对 position 列执行相同的操作。我不确定我是否可以将它混合到现有的案例陈述中，或者我是否必须为每个玩家添加三个案例陈述。如果我不将它与 salary 结合起来，它可能看起来像:

SELECT max(case when site_id = 1 then position end) fd_position, ...

等有没有办法将这些组合成一个查询？

Answer 1

尽管它看起来像，case 是一个标量函数，其计算结果为单个标量值。我自己，因为我讨厌重复打字，所以我会这样做:

select t.player_id ,
       max( case t.site_id when 1 then t.salary   else 0 end ) as fd_salary   ,
       max( case t.site_id when 1 then t.position else 0 end ) as fd_position ,
       max( case t.site_id when 2 then t.salary   else 0 end ) as dd_salary   ,
       max( case t.site_id when 2 then t.position else 0 end ) as dd_position ,
       max( case t.site_id when 3 then t.salary   else 0 end ) as ss_salary   ,
       max( case t.site_id when 3 then t.position else 0 end ) as ss_position
from ( select player_id ,
              site_id   ,
              gamedate  ,
              cast(REPLACE(REPLACE(REPLACE(salary,'$',''),' ',''), ',', '') as UNSIGNED) as salary ,
              cast(position as UNSIGNED) as position
       from mytable
     ) t
where t.gamedate = '2014-07-01'
  and t.side_id between 1 and 3
group by t.player_id