时间戳

首页 > 解决方案 > 如何从加入 SQL 表中按日期对 Id 进行汇总和计数

mysql - 如何从加入 SQL 表中按日期对 Id 进行汇总和计数

问题描述

我需要帮助，我有 2 个表station_levy和market_levy我正在尝试对两个表中完成的所有交易进行分组，查找总数并计算按日期分组的交易数量。

表格如下

station_levy

| station_levy_id | amount_tendered | transaction_datetime |
| --------------- | --------------- | -------------------- |
|        1        |       10        |       2021-02-01     |
|        2        |        5        |       2021-02-01     |
|        3        |       10        |       2021-02-03     |
|        4        |       10        |       2021-02-04     |

market_levy

| market_levy_id  | amount_tendered | transaction_datetime |
| --------------- | --------------- | -------------------- |
|        1        |        2        |       2021-02-01     |
|        2        |        3        |       2021-02-02     |
|        3        |        2        |       2021-02-03     |
|        4        |        2        |       2021-02-04     |

我试过了，但它没有按日期分组

SELECT transaction_datetime, amount_tendered
FROM market_levy

UNION ALL

SELECT transaction_datetime, amount_tendered
FROM station_levy 
GROUP BY DATE(transaction_datetime)

ORDER BY DATE(transaction_datetime)

预期结果


| trans_count     | total_tendered  | transaction_datetime |
| --------------- | --------------- | -------------------- |
|        3        |       17        |       2021-02-01     |
|        1        |        3        |       2021-02-02     |
|        2        |       12        |       2021-02-03     |
|        2        |       12        |       2021-02-04     |

标签： mysqlsql

解决方案

UNION您在子查询中的表并在此之上进行聚合和分组：

select count(1) as trans_count
       ,sum(amount_tendered) as amount_tendered
       ,DATE(transaction_datetime) as transaction_datetime
from
  (select station_levy_id as trans, amount_tendered, transaction_datetime
  from station_levy
  union all
  select market_levy_id as trans, amount_tendered, transaction_datetime
  from market_levy) temp
group by DATE(transaction_datetime)

在SQL Fiddle中查看结果

推荐阅读