在一个表的两个表中求和儿童

问题描述

我有3张桌子:

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

其中B将所有要完成的活动及其各自的小时数保留在2个位置(hht，hha)中，而c保留以其初始日期和最终日期进行的活动(确定执行的小时数减去日期).

where B keeps all the activities to be done and their respective hours in 2 places (hht,hha), while c saves the activities carried out with its initial and final date (to determine the hours executed the dates are subtracted).

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

Now I need to know, for each record in A how many hours you have assigned (B) and how many hours you have completed (C)

实际上我有这个:

a:

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

b:

+----------+----------+--------+--------+
|    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

推荐答案

由于表b和c中每个ckey的值都有多个行，因此需要在子查询中进行汇总，否则获取重复的行导致不正确的总和.

Because you have multiple rows in tables b and c for each value of ckey you need to do the aggregation within a subquery, otherwise you get duplicated rows leading to incorrect sums.

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的演示

这篇关于在一个表的两个表中求和儿童的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！