mysql将多个记录分组或转换为单个记录

js4nwp54 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(417)

这个问题在这里已经有答案了：

带if条件的mysql sum查询（2个答案）
11个月前关门了。
例如，我有一个mysql表

Date    | Branch | shift_time | Total Order | Avg Price
20-06-08  | A      | morning    | 4           | 5.6
20-06-08  | A      | night      | 3           | 3.4
20-06-08  | B      | morning    | 2           | 2.7
20-06-08  | B      | night      | 6           | 5.9 
20-06-09  | A      | morning    | 9           | 8.9 
20-06-09  | A      | night      | 4           | 6.9

列shift\u time是一个枚举，将是常量，我们需要按分支将其转换为每个日期的单个记录

Date    | Branch | morning_total_order | morning_price | night_total_order | night_avg_price 
20-06-08  | A      | 4                   | 5.6           | 3                 | 3.4
20-06-08  | B      | 2                   | 2.7           | 6                 | 5.9
20-06-09  | A      | 9                   | 8.9           | 4                 | 6.9

我尝试使用groupconcat，但该查询将轮班时间与数据合并。我们希望列标题上有这个。也许我们需要在什么时候使用case。但我不确定。

sql mysql select group-concat concat

来源：https://stackoverflow.com/questions/62318501/mysql-grouping-or-converting-multiple-records-into-1-single-record

1条答案

按热度按时间

elcex8rz1#

可以使用条件聚合生成所需的结果：

SELECT Date, Branch,
       SUM(CASE WHEN shift_time = 'morning' THEN `Total Order` ELSE 0 END) AS morning_total_order,
       SUM(CASE WHEN shift_time = 'morning' THEN `Avg Price` * `Total Order` ELSE 0 END) /
       SUM(CASE WHEN shift_time = 'morning' THEN `Total Order` ELSE 0 END) AS morning_avg_price,
       SUM(CASE WHEN shift_time = 'night' THEN `Total Order` ELSE 0 END) AS night_total_order,
       SUM(CASE WHEN shift_time = 'night' THEN `Avg Price` * `Total Order` ELSE 0 END) /
       SUM(CASE WHEN shift_time = 'night' THEN `Total Order` ELSE 0 END) AS night_avg_price
FROM shifts
GROUP BY Date, Branch

输出：

Date        Branch  morning_total_order     morning_avg_price   night_total_order   night_avg_price
20-06-08    A       4                       5.6                 3                   3.4
20-06-08    B       2                       2.7                 6                   5.9
20-06-09    A       9                       8.9                 4                   6.9

sqlfiddle演示

赞(0）回复(0）举报 2021-07-29

我来回答

mysql将多个记录分组或转换为单个记录

1条答案

相关问题

热门标签

最新问答