sqlite - 将 2 个表格中的小时和分钟相加并以 HH:MM 显示结果

Question

我有 2 个表，第一个表是主表，左侧有 HH:MM 格式的列，从右侧的列中获取信息，这些列是 hh 和 mm 格式

ACType   A     B        C      Ahr    Amin     Bhr    Bmin     Chr      Cmin

A320   12:34 85:45    07:23     12      34      85     45      7         23
B777   20:00 30:00    10:00     20      0       30     0      10         0

第二个表有 hh 和 mm 格式的列

ACType       Bhr   Bmin      Chr      Cmin

A320          10       20     46       31

我怎样才能得到最终结果：

ACType    A    B    C     Ahr     Amin   Bhr     Bmin     Chr    Cmin

A320  12:34  96:05 53:54  12       34     95      65       53      54
B777  20:00  30:00 10:00  20       0      30      0        10      0

标签： sqlite

Answer 1

SELECT
   first.ACType,
   first.A,
   (
     x.Bhr + x.Bmin / 60
   )
   || ':' || printf(" % 02d", x.Bmin % 60) AS B,
   (
     x.Chr + x.Cmin / 60
   )
   || ':' || printf(" % 02d", x.Cmin % 60) AS C,
   first.Ahr,
   first.Amin,
   x.Bhr,
   x.Bmin,
   x.Chr,
   x.Cmin
FROM
   first
   LEFT JOIN
     (
        SELECT
           ACType,
           SUM(Bhr) AS Bhr,
           SUM(Bmin)AS Bmin,
           SUM(Chr) AS Chr,
           SUM(Cmin) AS Cmin
        FROM
           (
              SELECT
                 ACType,
                 Bhr,
                 Bmin,
                 Chr,
                 Cmin
              FROM
                 first
              UNION ALL
              SELECT
                 ACType,
                 Bhr,
                 Bmin,
                 Chr,
                 Cmin
              FROM
                 SECOND
           )
        GROUP BY
           ACType
     )
     AS x
WHERE
   first.ACType = x.ACType