MySQL : weekly and monthly SUM

Question

下面给出的是我的TOUR表:

    tour_id | tour_date | amount
    ------------------------------
    ABC     | 2016-03-07   | 100
    ABC     | 2016-03-15   | 200
    DEF     | 2016-03-07   | 300
    DEF     | 2016-03-15   | 100
    GHI     | 2016-03-07   | 30

我想要得到以下内容:

• 每周总计
• 每月总计。

最终数据必须如下所示:

  tour_id| weekly sum. | monthly sum.
    --------------------------------
    ABC  | 100         | 300       
    DEF  | 300         | 400   
    GHI  | 30          | 30  

Answer 1

    SELECT 
    tour_id, WEEKLY, MONTHLY
FROM
    TOUR T1
        INNER JOIN
    (SELECT 
        tour_id, SUM(amount) WEEKLY
    FROM
        TOUR
    GROUP BY tour_id , WEEK(tour_date)) WEEK ON WEEK.tour_id = T1.tour_id
        INNER JOIN
    (SELECT 
        tour_id, SUM(amount) MONTHLY
    FROM
        TOUR
    GROUP BY tour_id , DATE_FORMAT(tour_date, '%m')) MONTH ON WEEK.tour_id = MONTH.tour_id

尝试上面的查询。