php - 如果 DATE 相同，则获取列的总计 MYSQL

Question

我对我需要的查询感到震惊..

   DATE        UPSRC         UPDST         DNSRC          DNDST

   2013-07-04  1.021        1.541          0.017          23.65
   2013-07-04  1.561        9.55           4.27           5.65
   2013-07-04  41.0451      7.641          9.67           2.65
   2013-07-03  7.51         3.541          3.47           2.65
   2013-07-03  1.021        1.541          2.67           23.65
   2013-07-03  1.561        9.55           34.47          5.65
   2013-07-02  61.0451      4.641          9.57           7.09
   2013-07-02  7.51         4.541          6.67           9.65
   2013-07-02  1.021        6.541          2.543          23.345
   2013-07-01  1.561        2.55           4.367          1.65
   2013-07-01  43.0451      3.641          9.67           7.65
   2013-07-01  7.51         1.541          6.627          9.65

这是使用这个查询生成的

SELECT *
FROM flows_monthly_summary
        WHERE DATE >= '2013-07-01' 
        AND DATE<='2013-07-04' 
        ORDER BY DATE DESC

现在，我想获取 (UPSRC+UPDST) 列的总计作为 UPSTREAM 和 (DNSRC+DNDST) 列的总计作为 DOWNSTREAM 像这样的日期:

DATE                      UPSTREAM                            DOWNSTREAM
2013-07-04       [TOTAL OF UPSRC+UPDST COLUMNS]    [TOTAL OF DNSRC+DNDST COLUMNS]
2013-07-03       [TOTAL OF UPSRC+UPDST COLUMNS]    [TOTAL OF DNSRC+DNDST COLUMNS]
2013-07-02       [TOTAL OF UPSRC+UPDST COLUMNS]    [TOTAL OF DNSRC+DNDST COLUMNS]
2013-07-01       [TOTAL OF UPSRC+UPDST COLUMNS]    [TOTAL OF DNSRC+DNDST COLUMNS]

为此我需要什么查询？谢谢!

Answer 1

SELECT `Date`,Sum(UPSRC+UPDST) as  UPSTREAM ,Sum(DNSRC+DNDST) as DOWNSTREAM
FROM flows_monthly_summary
        WHERE DATE >= '2013-07-01' 
        AND DATE<='2013-07-04' 
group by [Date]
        ORDER BY DATE DESC

建议您不要使用像 Date 这样的关键字作为您的列名，这是个坏习惯