如何根据两行条件选择列值？

Question

Name      Username    Day worked  Time period  
----------------------------------------------
John Doe  john        Sunday      8
Tom       tom         Monday      10
Jane Doe  jane        Tuesday     5
John Doe  john        Friday      9

Query:

SELECT username FROM table WHERE (day_worked='Sunday' AND time_period>5) AND (day_worked='Friday' and time_period>8)

Here I am applying conditions for two columns, user who worked on Sunday more than 5 hours and Friday more than 8 hours. So obviously John Doe (Username: john) meets the condition and it should be outputted.

However this does not return anything. May be the logic I am using is wrong. Any ideas?

Answer 1

You can do this by a bit of cross-tabulation magic. First off, cross tabulate all of your users by Sunday and Friday:

SELECT
  username,
  SUM(CASE WHEN `Day worked` = 'Sunday' THEN `Time Period` END) As Sunday,
  SUM(CASE WHEN `Day worked` = 'Friday' THEN `Time Period` END) AS Friday
FROM table
GROUP BY username

This query will yield the following:

username  Sunday  Friday
--------  ------  ------
jane      NULL    NULL
john      8       9
tom       NULL    NULL

By pushing this result down into a subquery you can easily pick out who worked more than 5 hours on Sunday and more than 8 hours on Friday:

 SELECT username
 FROM (
   SELECT
     username,
      SUM(CASE WHEN `Day worked` = 'Sunday' THEN `Time Period` END) As Sunday,
      SUM(CASE WHEN `Day worked` = 'Friday' THEN `Time Period` END) AS Friday
    FROM table
    GROUP BY username
  ) SunFri
WHERE SunFri.Sunday > 5 AND SunFri.Friday > 8