mysql - MySQL Count and Convert Row to Colum Involve One Table Only
问题描述
I have a table name histories that record user activities which consists of user_id, branch_id and duration.
The table look like this
+++++++++++++++++++++++++++++++++++
id | user_id | branch_id | totHours
+++++++++++++++++++++++++++++++++++
|1 | 100 | 1 | 1 |
|2 | 199 | 1 | 1 |
|3 | 121 | 1 | 1 |
|4 | 140 | 1 | 1 |
|5 | 103 | 2 | 3 |
|6 | 107 | 2 | 1 |
|7 | 299 | 1 | 2 |
|8 | 209 | 2 | 2 |
|9 | 119 | 1 | 5 |
I would like to produce an output like this:
+++++++++++++++++++++++++++
Hours | Branch A | Branch B
+++++++++++++++++++++++++++
|1 | 4 | 1 |
|2 | 1 | 1 |
|3 | 0 | 1 |
|4 | 0 | 0 |
|5 | 1 | 0 |
I try make it using this query, but when i use group by on totHours column only, it return error because i need to include the branch_id in the group by.
Here is my query:
select totHours as Hours,
coalesce(case when branch_id = 1 then count(totHours) else 0 end) as 'Branch A',
coalesce(case when branch_id = 2 then count(totHours) else 0 end) as 'Branch B'
from histories
group by totHours, branch_id;
And if the totHours is not in the table (for example in this table 4), it will display 0 for both branch column.
Here is my db fiddle
Update: MySQL version 5.7.22
Thanks
解决方案
如果您使用 MySQL 版本 8+(或任何版本支持 windows 功能),您可以使用递归公用表表达式为您生成小时值,然后使用它LEFT JOIN
表histories
。之后,您可以SUM()
使用CASE
表达式SELECT
来生成预期的输出:
WITH RECURSIVE hours AS (
SELECT 1 AS hr, MAX(totHours) AS maxth FROM histories UNION ALL
SELECT hr+1, maxth FROM hours WHERE hr+1 <= maxth)
SELECT hours.hr,
SUM(CASE WHEN histories.branch_id=1 THEN 1 ELSE 0 END) AS Branch_A,
SUM(CASE WHEN histories.branch_id=2 THEN 1 ELSE 0 END) AS Branch_B
FROM hours
LEFT JOIN histories
ON hours.hr=histories.totHours
GROUP BY hours.hr;
如果您使用的版本不支持窗口函数,您可以创建一个子查询来表示小时数(包括缺少的小时数)。这是一种硬编码方法,您可能必须始终更新子查询以包含新的小时值(如果有):
SELECT hours.hr,
SUM(CASE WHEN histories.branch_id=1 THEN 1 ELSE 0 END) AS Branch_A,
SUM(CASE WHEN histories.branch_id=2 THEN 1 ELSE 0 END) AS Branch_B
FROM
(SELECT 1 hr UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5) AS hours
LEFT JOIN histories
ON hours.hr=histories.totHours
GROUP BY hours.hr;
编辑hours
子查询以添加更多,例如,如果您想要直到 7,您只需添加:
(SELECT 1 hr UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7) AS hours
到子查询。另一种方法是预先定义小时数并创建参考表。假设您估计小时为 100,那么最好创建一个存储 1-100 的表作为参考LEFT JOIN
推荐阅读
- angularjs - 如何访问会话变量,它是角度js中的对象数组
- elasticsearch - 如何在kibana中查看日志
- c# - 在 OIDC 身份验证期间获取关联失败异常
- scala - 在 Scala 中展平嵌套对象
- php - 在 Laravel 中是否有“验证后进行身份验证”的正确方法?
- rails-activestorage - Rails 5.2.0 images.attach 导致 InvalidSignature 错误
- libreoffice-calc - 通过“通用数据有效性表”连接两个单元格?
- python - 简单链接表所需的 SQLAlchemy group_by() 求和帮助
- c# - 如何在c#中的xml之后添加数据并在之后读取xml?
- python - Python多线程产生n个并发线程