mysql - 年度 SQL 总计以及设置期间

Question

我有以下 SQL 数据库:表名

date        fname   surname     points  display
2015-08-08  John    Lennon      5       1
2015-08-16  Ringo   Starr       2       1
2015-08-24  Paul    McCartney   3       1
2015-10-07  George  Harrison    1       1
2015-10-15  John    Lennon      3       1
2015-10-23  Ringo   Starr       5       1
2015-12-07  George  Harrison    1       1
2015-12-14  Ringo   Starr       5       1
2015-12-22  George  Harrison    3       1
2016-02-03  Paul    McCartney   4       1
2016-02-13  John    Lennon      5       1
2016-02-20  Paul    McCartney   1       1
2016-04-04  Ringo   Starr       2       1
2016-04-09  George  Harrison    2       1
2016-04-20  John    Lennon      5       1

目前我只是使用这个数据库来提取当前年份的总分，其中 display = '1' 并使用以下代码:

SELECT id, fname, surname, points, CONCAT(surname,forename) 'fullname', SUM(points) AS total_points
FROM tablename 
WHERE date >= '2016-01-01' AND display ='1' 
GROUP BY fname, surname
ORDER BY total_points DESC, surname ASC, forename ASC

但是我想在结果中添加一条额外的数据，给我过去 15 天的分数。我可以使用以下方法自行拉取:

SELECT id, fname, surname, points, CONCAT(surname,forename) 'fullname', SUM(points) AS fifteenday_points
FROM tablename 
WHERE date >= '2016-04-08' AND display ='1' 
GROUP BY fname, surname
ORDER BY total_points DESC, surname ASC, forename ASC

那么，我该怎么做呢？我需要使用某种连接吗？如果是这样，由于 WHERE 语句不同，我该从哪里开始呢？或者我可以对第一个查询进行简单的添加，以返回十五天的点数据吗？

Answer 1

您可以通过以下方式加入他们:

SELECT fname, surname, CONCAT(surname,fname) AS fullname,  SUM(points) AS total_points, fifteenday_points
FROM tablename JOIN (
    SELECT CONCAT(surname,forename) 'fullname', SUM(points) AS fifteenday_points
    FROM tablename 
    WHERE date >= NOW() - INTERVAL 15 DAY  AND display ='1' 
    GROUP BY fullname) AS fifteen_days
    ON tablename.fullname = fifteen_days.fullname
WHERE YEAR(date) = YEAR(NOW()) AND display ='1' 
GROUP BY fullname
ORDER BY total_points DESC, fullname ASC

我还更改了数据条件