sqlite 有没有一种方法可以使用if elif else场景在SQL中合并查询？

hgncfbus 于 2022-12-13 发布在 SQLite

关注(0)|答案(1)|浏览(190)

我想计算活动示例的total_uniterrupted时间，这样就有两种情况：
1.当活动1处于活动状态时，活动2会中断活动1，然后活动2会在活动1之前结束。
scenario 1 graphical representation
其中i是当前示例，ac表示示例表中的其它示例
方案1的查询为：

total uninterrupted time = (i.end – i.start) – (ac.end – ac.start)
where ac.start between i.start and i.end and ac.end <= i.end

1.当活动1处于活动状态时，活动2会中断活动1，活动1会在活动2之前结束
scenario 2 graphical represenation
方案2的查询为：

Total uninterrupted time = (i.end – i.start) – (i.end – ac.start) 
where ac.start between i.start and i.end and ac.end >= i.end

我已经实现了分别计算这两种场景的total_uninterrupted time，但我需要使用if else之类的命令将它们放在同一个查询中。
我不知道在SQL中是否可以实现这样的功能：

IF (ac.start BETWEEN i.start AND i.end AND ac.end <= i.end)  
   THEN (i.end – i.start) – (ac.end – ac.start) AS total_uninterrupted_time

ELSE IF (ac.start BETWEEN i.start AND i.end AND ac.end <= i.end) 
    THEN (i.end – i.start) – (i.end – ac.start) AS total_uninterrupted_time

我在场景1中尝试了此方法：

SELECT 
    i.instanceid, i.activityid, i.start, i.end,
    i.end - i.start as totalWithInterruptions,
    SUM((SELECT COUNT(*) FROM instance AS ac 
         WHERE ac.instanceid != i.instanceid 
           AND ac.start BETWEEN i.start and i.end)) AS interrupted,
    COALESCE((SELECT (ac.end - ac.start) 
              FROM instance AS ac 
              WHERE ac.instanceid != i.instanceid 
                AND ac.start BETWEEN i.start AND i.end 
                AND ac.end <= i.end), 0) AS interruptions,
    SUM((COALESCE((i.end - i.start), 0) - COALESCE((SELECT (ac.end - ac.start) FROM instance AS ac WHERE ac.instanceid != i.instanceid AND ac.start BETWEEN i.start AND i.end AND ac.end <= i.end), 0))) AS timeWithoutInterruptions
FROM 
    instance AS i 
WHERE 
    userid = 2 
GROUP BY 
    i.instanceid

我得到的结果是
scenario 1 result
这仅正确执行了行2和行3，因为行3的结束时间小于行2的结束时间（ac.end〈= i.end）
但方案2适用于行1，即：

SELECT i.instanceid, i.activityid, i.start, i.end,
        i.end - i.start as totalWithInterruptions,
        SUM((SELECT count(*) from instance as ac WHERE ac.instanceid != i.instanceid and ac.start BETWEEN i.start and i.end )) as interrupted,
        COALESCE((SELECT (i.end - ac.start) FROM instance as ac WHERE ac.instanceid != i.instanceid and ac.start BETWEEN i.start and i.end and ac.end >= i.end),0) as interruptions,
        SUM((COALESCE((i.end - i.start),0) - COALESCE((SELECT (i.end - ac.start) FROM instance as ac WHERE ac.instanceid != i.instanceid and ac.start BETWEEN i.start and i.end and ac.end >= i.end),0))) as timeWithoutInterruptions
        FROM instance as i WHERE userid = 2 GROUP BY i.instanceid

得到的结果是scenerio 2 result，这只对第一行有效。
我尝试将scenerio 1和scenerio 2中执行timeWithoutInterruptions的查询组合在一起，以便在给定正确的条件时，对每一行执行正确的计算，方法是：

SUM((COALESCE((i.end - i.start),0) – ( 
COALESCE((SELECT (ac.end - ac.start) FROM instance as ac WHERE ac.instanceid != i.instanceid and ac.start BETWEEN i.start and i.end and ac.end <= i.end),0) 
OR 
COALESCE((SELECT (ac.end - ac.start) FROM instance as ac WHERE ac.instanceid != i.instanceid and ac.start BETWEEN i.start and i.end and ac.end >= i.end),0)
))) as time

但是这给了我一个错误。有没有其他的方法我可以把2个场景合并成1来计算中断和timeWithoutInterruptions。
应该在中断列中显示的正确结果是[21，22，0]，在timeWithoutInterruptions列中显示的正确结果是[4，6，22]