时间戳

首页 > 解决方案 > 如何按sql查询排序和分组？

mysql - 如何按sql查询排序和分组？

问题描述

我有数据

id Date      CatId  itemid itemname price
1  10/5/2019  1         1      ABC        20
2  10/5/2019  1         2      XYZ        30
3  10/5/2019  2         1      ABC        20
4  10/5/2019  3         1      ABC        20  

5  11/5/2019  1         2      XYZ        30
6  11/5/2019  2         1      ABC        20
7  11/5/2019  2         3      PQR        40

8  12/5/2019  3         1      ABC        20
9  12/5/2019  3         2      XYZ        30 
10  12/5/2019  1         2      XYZ        30
11  12/5/2019  2         1      ABC        20

预期结果数据

date            CatId   toal 
 10/5/2019      1           50
 10/5/2019      2           20
 10/5/2019      3           20

  11/5/2019     1       30
  11/5/2019     2       60
  12/5/2019     1       30
  12/5/2019     2       20
  12/5/2019     3       50

我想要按日期排序，然后按 catid 对价格组求和，

我尝试了多个查询，但得到了确切的解决方案。我花了这么多时间。

我尝试了以下查询

SELECT * FROM (SELECT * FROM `table`  ORDER BY `date` DESC) as tbl
 GROUP BY tbl.`catid`

SELECT *
FROM `table` 
GROUP BY `date` ORDER BY `date` DESC

SELECT * 
FROM (
    SELECT * FROM `table`  
    ORDER BY `date` DESC
) AS sub
GROUP BY `catid` ORDER BY `date` DESC

标签： mysql

解决方案

您可以在 order by 和 group by 中使用的列可以不同

您可以直接使用 group by 和 sum

select date, catid, sum(price)
from my_table 
group by date, catid
order by date, sum(price), catid

推荐阅读