时间戳

首页 > 解决方案 > MySQL如何将两行合二为一

mysql - MySQL如何将两行合二为一

问题描述

你好堆栈溢出，

我正在使用带有下表的 mysql 8：

create table usage_info
(
    id bigint auto_increment
        primary key,
    amount double not null,
    timestamp datetime null,
    type varchar null
);

示例数据：

id  amount  timestamp             type
--------------------------------------
1   0.123   2020-06-30 12:40:54   A
2   0.098   2020-06-30 15:47:51   B
3   0.456   2020-06-30 16:40:54   A
4   0.101   2020-06-30 17:47:51   B
5   0.123   2020-07-01 12:40:54   A
6   0.098   2020-07-01 15:47:51   B
7   0.456   2020-07-01 16:40:54   A
8   0.101   2020-07-01 17:47:51   B

我可以使用 2 种不同的方法获得每天每种类型的总数

方法一。

select total, type, date from (
select sum(u.amount) as total, u.type, DATE_FORMAT(u.timestamp, "%a-%c-%d-%y") as date
from usage_info u
group by DATE_FORMAT(u.timestamp, "%a-%c-%d-%y"), u.type) as temp;

结果：

total    type    date
---------------------
0.579    A       Tue-6-30-20
0.199    B       Tue-6-30-20
0.579    A       Wed-7-01-20
0.199    B       Wed-8-01-20

方法2。

select case
           when type = 'A' then total
           end as A,
       case
           when type = 'B' then total
           end as B,
       date
from (
         select sum(u.amount) as total, u.type, DATE_FORMAT(u.timestamp, "%a-%c-%d-%y") as date
         from usage_info u
         group by DATE_FORMAT(u.timestamp, "%a-%c-%d-%y"), u.type
     ) as results;

给出：

A        B       date
----------------------------
0.579    null    Tue-6-30-20
null     0.199   Tue-6-30-20
0.579    null    Wed-7-01-20
null     0.199   Wed-7-01-20

但我希望结果看起来像：

A        B       date
----------------------------
0.579    0.199   Tue-6-30-20
0.579    0.199   Wed-7-01-20

谢谢你的时间！

标签： mysqlsql

解决方案

只需使用条件聚合：

select sum(case when type = 'A' then u.amount end) as total_a,
       sum(case when type = 'B' then u.amount end) as total_b,
       DATE_FORMAT(u.timestamp, "%a-%c-%d-%y") as date
from usage_info u
group by DATE_FORMAT(u.timestamp, '%a-%c-%d-%y');

推荐阅读