错误：子查询返回超过 1 个值。当子查询跟随时这是不允许的

Question

我有这样的桌子 我已经尝试获取以下查询，它工作正常

SELECT DISTINCT 
    USER_ID, SESSION_ID,
    (SELECT EVENT_TIME FROM Actividades_Usuarios 
     WHERE EVENT_ID = 1 AND USER_ID = 407) AS START_TIME_SESSION,
    (SELECT EVENT_TIME FROM Actividades_Usuarios 
     WHERE EVENT_ID = 2 AND USER_ID = 407) AS END_TIME_SESSION,
    DATEDIFF(mi, (SELECT EVENT_TIME FROM Actividades_Usuarios 
                  WHERE EVENT_ID = 1 AND USER_ID = 407),
                 (SELECT EVENT_TIME FROM Actividades_Usuarios 
                  WHERE EVENT_ID = 2 AND USER_ID = 407)) AS TIME_SESSION 
FROM 
    Actividades_Usuarios 
WHERE 
    USER_ID = 407

，但对于单个用户来说，例如：

然后我尝试使对于一般情况，当 EVENT_ID 为 1 时，将 EVENT*_*TIME 设置为 START_TIME_SESSION，当 EVENT_ID 为 2 时，将 EVENT*_*TIME 设置为 START_TIME_SESSION，并使用以下查询：

SELECT DISTINCT USER_ID,
       SESSION_ID,
       (SELECT EVENT_TIME FROM Actividades_Usuarios 
WHERE EVENT_ID = 1) as START_TIME_SESSION,
       (SELECT EVENT_TIME FROM Actividades_Usuarios 
WHERE EVENT_ID = 2) as END_TIME_SESSION
FROM Actividades_Usuarios 

但会出现常见错误：

子查询返回超过 1 个值...

当我正在查看该查询结果时。事实上，如果我将其中一列作为简单查询运行，则返回相同数量的行，无论选择什么...

SELECT USER_ID,SESSION_ID,
EVENT_TIME as START_TIME_SESSION 
FROM Actividades_Usuarios 
WHERE EVENT_ID = 1

但是，如何在这两者之间添加来自 DATEDIFF 的另一列 (END_TIME_SESSION) 和最终目标列结果 (TIME_SESSION)？

Answer 1

您需要关联所有子查询，即在子查询中添加 WHERE 子句，类似于“子查询的用户 id = 主查询的用户 id”，

用一个示例澄清我的答案......

SELECT DISTINCT 
A.USER_ID,
A.SESSION_ID,
(SELECT B.EVENT_TIME 
 FROM Actividades_Usuarios B
 WHERE B.EVENT_ID = 1 
 AND B.USER_ID = A.USER_ID) as START_TIME_SESSION,
(SELECT C.EVENT_TIME 
 FROM Actividades_Usuarios C
 WHERE C.EVENT_ID = 2 
 AND C.USER_ID = A.USER_ID) as END_TIME_SESSION
FROM Actividades_Usuarios A

Answer 2

您真正想要使用的是聚合，并首先避免所有子查询：

select USER_ID, SESSION_ID,
    min(case when EVENT_ID = 1 then EVENT_TIME end) as START_TIME_SESSION,
    min(case when EVENT_ID = 2 then EVENT_TIME end) as END_TIME_SESSION
from Actividades_Usuarios
where EVENT_ID in (1, 2)
group by USER_ID, SESSION_ID;

使用 CTE 将使计算变得轻而易举：

with data as (
    select USER_ID, SESSION_ID,
        min(case when EVENT_ID = 1 then EVENT_TIME end) as START_TIME_SESSION,
        min(case when EVENT_ID = 2 then EVENT_TIME end) as END_TIME_SESSION
    from Actividades_Usuarios
    where EVENT_ID in (1, 2)
    group by USER_ID, SESSION_ID
)
select *, datediff(minute, START_TIME_SESSION, END_TIME_SESSION) as TIME_SESSION
from data;

您还应该研究 datediff 仅计算间隔边界的方式。我怀疑你想要的结果更像是这样的：

datediff(millisecond, START_TIME_SESSION, END_TIME_SESSION) / 60000 as TIME_SESSION