mysql - 具有两个排序值的 Group by 的 SQL 查询并计算相等值

Question

例如我有下面的表格数据:

    Date          branch     sale     profit
    Jan 1, 2014    A         10          5
    Jan 2, 2014    A         11          6
    Jan 3, 2014    A         20          9
    Feb 1, 2014    A         11          4
    Feb 2, 2014    B         20          10
    Feb 3, 2014    C         12          6
    Mar 1, 2014    A         25          15
    Mar 2, 2014    B         60          30
    Mar 3, 2014    B         40          20

我已成功按月对这些数据进行分组，并对销售额和利润求和。请参阅下面我的 sql 查询。

SELECT DATE_FORMAT(date,'%b') as date, SUM(sale) as sale, SUM(profit) as profit FROM sales_report GROUP BY MONTH(date) ORDER BY MONTH(date)

我在下面有一个结果值

Date    branch     sale     profit
Jan      A         41          20
Feb      A         43          20
Mar      A         125         65

我想在这里实现的是选择最新日期的分支并按升序保留月份而且我还想添加字段来计算分支。我想要下面的这些结果。请帮忙

Date    branch     sale     profit   Count
Jan      A         41          20      1
Feb      C         43          20      3
Mar      B         125         65      2

Answer 1

我认为您需要 count(distinct) 作为计数列。对于分支，可以使用group_concat()/substring_index():

SELECT year(date), month(date),
       substring_index(group_concat(branch order by date desc), ',', 1) as last_branch,
       SUM(sale) as sale, SUM(profit) as profit,
       count(distinct branch) as NumBranches
FROM sales_report
GROUP BY year(date), month(date)
ORDER BY min(date);