想要结果的 SQLITE 查询 [英] SQLITE query to desire results

问题描述

我的表格格式为 (CUSTOMER) (FIG 1.1)

I have the table in the format (CUSTOMER) (FIG 1.1)

M_CODE  H_CODE END_TIME_OF_SERVICE ADULT ADULT_F
TKLK    LONE     09:19:16           1     2
TKLK    LONE     09:22:11           4     6
TKLK    LONE     09:32:46           2     7
TKLK    LONE     09:32:46           4     9
TKLK    LONE     10:09:36           1     3

如果我运行 (SELECT strftime('%H:%M', end, '-9 minutes') || ' - ' || strftime('%H:%M', end ) AS time_slot FROMend_times )它给出了正确的输出(图 - 1.2)

and if i run the ( SELECT strftime('%H:%M', end, '-9 minutes') || ' - ' || strftime('%H:%M', end ) AS time_slot FROM end_times ) it gives the correct output as (FIG - 1.2)

09:19 - 09:28
09:29 - 09:38
09:39 - 09:48
09:49 - 09:58
09:59 - 10:08
10:09 - 10:18 

你能指导一下如何映射这两个表吗，假设CUSTOMER表中的记录应该与adult和adult_f列相加(图1.2到图1.1)

Can you guide how to map these two tables, let`s say the the records in CUSTOMER table should be summed up for adult and adult_f column with( FIG 1.2 to FIG 1.1 )

   ADULT    ADULT_F    TIME_SLOT
      5        8      09:19 - 09:28
      6       16      09:29 - 09:38
      0        0      09:39 - 09:48
      0        0      09:49 - 09:58
      0        0      09:59 - 10:08
      1        3      10:09 - 10:18 

请指导我解决方案，感谢您的时间.

Kindly guide me a solution, thanks for your time.

推荐答案

首先，我们必须以一种易于比较的格式获取开始/结束时间(我们可以稍后将它们组合起来):

First, we have to get the start/end times in a format that we can easily compare (we can combine them later):

SELECT strftime('%H:%M', end, '-9 minutes') AS slot_start,
       strftime('%H:%M', end              ) AS slot_end
FROM end_times

然后我们可以将其加入到 CUSTOMER 表中，同时忽略秒:

Then we can join this to the CUSTOMER table, while ignoring the seconds:

SELECT ADULT,
       ADULT_F,
       slot_start,
       slot_end
FROM (SELECT strftime('%H:%M', end, '-9 minutes') AS slot_start,
             strftime('%H:%M', end              ) AS slot_end
      FROM end_times)
     LEFT JOIN CUSTOMER
     ON substr(END_TIME_OF_SERVICE, 1, 5) BETWEEN slot_start AND slot_end

最后，我们按槽分组以获得每个槽的总和.我正在使用 total 而不是 sum 获取 NULL 值的零:

Finally, we group by the slot to get the sums per slot. I'm using total instead of sum to get a zero for NULL values:

SELECT total(ADULT),
       total(ADULT_F),
       slot_start || ' - ' || slot_end
FROM (SELECT strftime('%H:%M', end, '-9 minutes') AS slot_start,
             strftime('%H:%M', end              ) AS slot_end
      FROM end_times)
     LEFT JOIN CUSTOMER
     ON substr(END_TIME_OF_SERVICE, 1, 5) BETWEEN slot_start AND slot_end
GROUP BY slot_start

这篇关于想要结果的 SQLITE 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！