mysql使用case从2个表中获取数据

Question

我有每周数据和每日数据。我想获得所需的输出，如下所示。我需要帮助才能得到它

每周数据

+---------+------------+------------+--------+------------+
| userid  | startdate  | enddate    | weekno |runningcost |
+---------+------------+------------+--------+------------+
| 10      | 2017-03-20 | 2017-03-26 | 12     | 200        |
| 10      | 2017-03-27 | 2017-04-02 | 13     | 300        |
| 10      | 2017-04-03 | 2017-04-09 | 14     | 200        |
+---------+------------+------------+--------+------------+

每日数据

+------------+-------+---------+
| startdate  | hours | userid  |
+------------+-------+---------+
| 2017-03-20 | 1    | 10      |
| 2017-03-21 | 1    | 10      |
| 2017-03-22 | 1    | 10      |
| 2017-03-23 | 1    | 10      |
| 2017-03-24 | 1    | 10      |
| 2017-03-25 | 1    | 10      |
| 2017-03-26 | 1    | 10      |
| 2017-03-27 | 2    | 10      |
| 2017-03-28 | 2    | 10      |
| 2017-03-29 | 2    | 10      |
| 2017-03-30 | 2    | 10      |
| 2017-03-31 | 2    | 10      |
| 2017-04-01 | 2    | 10      |
| 2017-04-02 | 2    | 10      |
+----+-------+-------+---------+

输出

+---------+------------+-------+-------+-------+-------+-------+-------+-------+
| userid  | startdate  | day1  | day2  | day3  | day4  | day5  | day6  | day7  |
+---------+------------+-------+-------+-------+-------+-------+-------+-------+
| 10      | 2017-03-20 | 1     | 1     | 1     | 1     | 1     | 1     | 1     |
| 10      | 2017-03-27 | 2     | 2     | 2     | 2     | 2     | 2     | 2     |
+---------+------------+-------+-------+-------+-------+-------+-------+-------+

Answer 1

您可以使用此查询。它在 userid 上将 dailydata 连接到 weeklydata 并且每日日期在每周日期的范围内。然后它使用条件聚合来转换数据:

SELECT w.userid,
       DATE(w.startdate),
       SUM(CASE WHEN DATEDIFF(d.startdate, w.startdate) = 0 THEN hours ELSE 0 END) AS day1,
       SUM(CASE WHEN DATEDIFF(d.startdate, w.startdate) = 1 THEN hours ELSE 0 END) AS day2,
       SUM(CASE WHEN DATEDIFF(d.startdate, w.startdate) = 2 THEN hours ELSE 0 END) AS day3,
       SUM(CASE WHEN DATEDIFF(d.startdate, w.startdate) = 3 THEN hours ELSE 0 END) AS day4,
       SUM(CASE WHEN DATEDIFF(d.startdate, w.startdate) = 4 THEN hours ELSE 0 END) AS day5,
       SUM(CASE WHEN DATEDIFF(d.startdate, w.startdate) = 5 THEN hours ELSE 0 END) AS day6,
       SUM(CASE WHEN DATEDIFF(d.startdate, w.startdate) = 6 THEN hours ELSE 0 END) AS day7
FROM weeklydata w
JOIN dailydata d ON d.userid = w.userid AND d.startdate BETWEEN w.startdate AND w.enddate
GROUP BY w.userid, DATE(w.startdate)

输出:

userid  DATE(w.startdate)   day1    day2    day3    day4    day5    day6    day7
10      2017-03-20          1       1       1       1       1       1       1
10      2017-03-27          2       2       2       2       2       2       2

Demo on dbfiddle