mysql - 如何选择mysql中每列的前3个值

嗨，我有一张这样的桌子，里面有那天的轮班表。我需要按班次分类

+------------+----------+-----------------+
| shift_date | name     | shift           |
+------------+----------+-----------------+
| 2016-07-01 | Swamy    | Morning Shift   |
| 2016-07-01 | Hitesh   | Morning Shift   |
| 2016-07-01 | Naresh   | Afternoon Shift |
| 2016-07-01 | Rambabu  | Afternoon Shift |
| 2016-07-01 | Srinivas | Night Shift     |
| 2016-07-01 | Ahmed    | Night Shift     |
| 2016-07-01 | Naveen   | Week Off        |
| 2016-07-01 | Karthik  | Week Off        |
+------------+----------+-----------------+

我需要一个查询来得到

+------------+---------+-----------+----------+---------+
| shift_date | Morning | Afternoon | Night    | WeekOff |
+------------+---------+-----------+----------+---------+
| 2016-07-01 | Swamy   | Naresh    | Srinivas | Naveen  |
| 2016-07-01 | Hitesh  | Rambabu   | Ahmed    | Karthik |
+------------+---------+-----------+----------+---------+

我写了这个问题

 select
     shift_date,
     CASE shift
       WHEN shift <>'Morning Shift'
       then name
     end as Morning,
     CASE shift
       WHEN shift<>'Afternoon Shift'
       then name
     end as Afternoon,
     CASE shift
       WHEN shift<>'Night Shift'
       then name
     end as Night,
     CASE shift
       WHEN shift<>'Week Off'
       then name
     end as WeekOff
 from roster
 where shift_date ='2016-7-1'
 order by morning desc ,afternoon desc, night desc ,weekoff desc;

但结果是

+------------+---------+-----------+----------+---------+
| shift_date | Morning | Afternoon | Night    | WeekOff |
+------------+---------+-----------+----------+---------+
| 2016-07-01 | Swamy   | NULL      | NULL     | NULL    |
| 2016-07-01 | Hitesh  | NULL      | NULL     | NULL    |
| 2016-07-01 | NULL    | Rambabu   | NULL     | NULL    |
| 2016-07-01 | NULL    | Naresh    | NULL     | NULL    |
| 2016-07-01 | NULL    | NULL      | Srinivas | NULL    |
| 2016-07-01 | NULL    | NULL      | Ahmed    | NULL    |
| 2016-07-01 | NULL    | NULL      | NULL     | Naveen  |
| 2016-07-01 | NULL    | NULL      | NULL     | Karthik |
+------------+---------+-----------+----------+---------+

我需要结果

+------------+---------+-----------+----------+---------+
| shift_date | Morning | Afternoon | Night    | WeekOff |
+------------+---------+-----------+----------+---------+
| 2016-07-01 | Swamy   | Naresh    | Srinivas | Naveen  |
| 2016-07-01 | Hitesh  | Rambabu   | Ahmed    | Karthik |
+------------+---------+-----------+----------+---------+

最佳答案

select m.shift_date  ,Morning , Afternoon
from (select shift_date,name as Morning  from roster where shift = 'Morning Shift') as m,
LEFT JOIN (select shift_date,name as Afternoon  from roster where shift = 'Afternoon Shift') as n on m.shift_date = n.shift_date

在你的问题中，所有的轮班日期都是一样的。
因此，使用此join on date还将获得shift_date的名称。
这不是一个适当的解决办法。
希望能有所帮助

关于mysql - 如何选择mysql中每列的前3个值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38432790/