SQL Server 子查询返回了 1 个以上的值；用不同的条件选择同一行两次；

Question

我尝试运行以下查询:

SELECT DISTINCT person_pin AS Pin, att_date AS DailyDate,
    (SELECT MAX(att_time) FROM dbo.att_transaction WHERE att_state = 0 GROUP BY person_pin) 
    AS MaxTime,
    (SELECT MIN(att_time) FROM dbo.att_transaction WHERE att_state = 1 GROUP BY person_pin) 
    AS MinTime
FROM dbo.att_transaction
WHERE att_verify IN (4, 15)

我收到以下错误:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

当我在没有以下内容的情况下运行查询时:GROUP BY person_pin
它运行没有错误，但我得到以下结果:



它获取整个列的最小值或最大值。

我需要它来获取每个用户每天的最小值和最大值。

但是最小值应该只从 att_state 为 0 的行和 att_state 为 1 的行的最大值中获取。

我查询的表是这样的:

id  person_pin  att_date     att_time     att_state
---|-----------|------------|------------|---------
1  | 123       | 2018-09-18 | 15:11:03   | 1 
2  | 123       | 2018-09-18 | 10:05:32   | 0
5  | 234       | 2018-09-24 | 14:05:16   | 1
3  | 234       | 2018-09-24 | 13:05:55   | 1
4  | 123       | 2018-09-24 | 12:10:42   | 0
6  | 123       | 2018-09-24 | 12:15:35   | 0
7  | 234       | 2018-09-24 | 12:05:32   | 1
8  | 123       | 2018-09-24 | 10:05:33   | 1

我也试过:

SELECT DISTINCT tt.person_pin AS Pin, tt.att_date AS DailyDate
FROM dbo.att_transaction tt
INNER JOIN
    (SELECT person_pin,
    (SELECT MAX(att_time) FROM dbo.att_transaction WHERE att_state = 0 GROUP BY person_pin) 
    AS MaxTime,
    (SELECT MIN(att_time) FROM dbo.att_transaction WHERE att_state = 1 GROUP BY person_pin) 
    AS MinTime
    FROM dbo.att_transaction
    GROUP BY person_pin) groupedtt 
ON tt.person_pin = groupedtt.person_pin

但它只返回:



我需要它来返回每个用户每天的最小和最大时间。
但是最短时间只能从 att_state 为 0 的行中获取
并且最大时间只能从 att_state 为 1 的行中获取

需要帮助;m;

Answer 1

您可以尝试使用条件聚合函数来制作它。

SELECT MIN(CASE WHEN att_state = 1 then att_time end) MaxTime,
       MAX(CASE WHEN att_state = 0 then att_time end) MinTime,
       person_pin,
       att_date     
FROM dbo.att_transaction 
WHERE t2.att_verify IN (4, 15)
GROUP BY person_pin,
         att_date