How do you choose the employee who has worked the most night shifts of all time? There are 2 tables, one with workers, the second with night shifts, in which 2 people work per shift.
Users:
| id | name |
|---|---|
| 1 | Oliver |
| 2 | Harry |
| 3 | Jacob |
Hours:
| id | NightShift1 | NightShift2 |
|---|---|---|
| 1 | 1 | 3 |
| 2 | 2 | 2 |
| 3 | 3 | 1 |
| 4 | 3 | 2 |
| 5 | 2 | 2 |
| 6 | 1 | 2 |
| 7 | 1 | 3 |
| 8 | 3 | 1 |
CodePudding user response：
To do this you can to essentially loop over the hours table twice; you do this by joining an ad hoc table specifying which shift you are looking at:
select users.id, users.name
from hours
join (select 1 position union all select 2) position
join users on users.id=if(position=1,hours.NightShift1,hours.NightShift2)
group by users.id
order by count(*) desc
limit 1
CodePudding user response：
You can UNION the Hours table on top of itself and then group by the user id to see who has the highest count:
SELECT COUNT(*) as nightshiftcount, userid
FROM (
SELECT NightShift1 as userid FROM Hours
UNION ALL SELECT NightShift2 FROM Hours
) as hrs
ORDER BY nightshiftcount DESC
LIMIT 1
If you need the name, you can just INNER JOIN to that table in that outer FROM clause and pull that column through.