时间戳

首页 > 解决方案 > 在一个表的两个表中求和孩子

mysql - 在一个表的两个表中求和孩子

问题描述

我有 3 张桌子：

a (id,date,ckey) b(id,a.ckey,hht,hha) c(id,a.ckey,date_ini,date_fin)

其中 B 将所有要完成的活动及其各自的时间保存在 2 个位置 (hht,hha)，而 c 保存已执行的活动及其初始日期和最终日期（以确定执行的时间减去日期）。

现在我需要知道，对于 A 中的每条记录，您分配了多少小时 (B) 以及您完成了多少小时 (C)

实际上我有这个：

A：

+----------+----------+------------+
|    id    |   date   |     ckey   |
+----------+----------+------------+
|    1     |2018-01-20|     18     |
|----------|----------|------------|

乙：

+----------+----------+--------+--------+
|    id    |  a.ckey  |  hht   |   hht  |
+----------+----------+--------+--------+
|    1     |    18    |    2   |    3   |
|    2     |    18    |    2   |    5   |
|    3     |    18    |    0   |    7   |
+----------+----------+--------+--------+

C：

+----------+----------+----------------------+----------------------+
|    id    |  a.ckey  |        date_ini      |        date_fin      |
+----------+----------+----------------------+----------------------+
|    1     |    18    | 2019-01-23 13:30:00  | 2019-01-23 14:00:00  |
|    1     |    18    | 2019-01-23 14:00:00  | 2019-01-23 14:30:00  |
+----------+----------+----------------------+----------------------+

我需要这个：

+----------+----------+----------------------+----------------------+
|    id    |  a.ckey  |         hours        |         hours2       |
+----------+----------+----------------------+----------------------+
|    1     |    18    |           19         |           1          |
+----------+----------+----------------------+----------------------+

我明白了：

+----------+----------+----------------------+----------------------+
|    id    |  a.ckey  |         hours        |         hours2       |
+----------+----------+----------------------+----------------------+
|    1     |    18    |           38         |           37.5       |
+----------+----------+----------------------+----------------------+

这是我的查询：

SELECT 
  (b.hht+b.hha) AS hours, 
  (SUM(b.hht+b.hha) - 
    FORMAT(IFNULL((TIMESTAMPDIFF(MINUTE, c.date_ini, c.date_fin)/60),0),2)) AS hours2
FROM a 
LEFT JOIN b ON a.key=b.akey 
INNER JOIN c ON a.key=c.akey 
GROUP a.ckey

标签： mysqlsqljoin

解决方案

因为您在表中有多行，b并且c对于每个值，ckey您需要在子查询中进行聚合，否则您会得到重复的行，从而导致不正确的总和。

SELECT a.id, a.key, b.hours, FORMAT(c.minutes/60, 2) AS hours2
FROM a
LEFT JOIN (SELECT akey, SUM(hht+hha) AS hours
           FROM b
           GROUP BY akey) b ON b.akey = a.key
LEFT JOIN (SELECT akey, SUM(TIMESTAMPDIFF(MINUTE, date_ini, date_fin)) AS minutes
           FROM c
           GROUP BY akey) c ON c.akey = a.key
ORDER BY a.id

输出：

id  key     hours   hours2
1   18      19      1.00

SQLFiddle 上的演示

推荐阅读