SQL查询最小值和最大值分组

I have a table as below :

我有如下表格:

How can I craft a SQL select statement so that MIN AND MAX EVENT DATE groups results by FLAG (0,1)?

如何创建一个SQL select语句，使最小和最大事件日期组根据标记(0,1)产生结果?

So the result would be:

结果是:

2 个解决方案

#1

An alternative if Window function does not work:

如果窗口函数不起作用，则另一种方法:

 SELECT 
       t1.card_no, t1.descr_reader,
       t1.event_date date_in,
       (select top 1 event_date from test t2
        where t2.card_no = t1.card_no and
              t2.reader_no = t1.reader_no and
              t2.descr_reader = t1.descr_reader and
              t2.event_date > t1.event_date and
              t2.flag = 1
        order by t2.event_date ) as date_out
FROM test t1 
WHERE t1.flag = 0

#2

Just do conditional aggregation with use of window function

只需使用窗口函数进行条件聚合

SELECT card_no, descr_reader,
       max(CASE WHEN flag = 0 THEN event_date END) date_in,
       max(CASE WHEN flag = 1 THEN event_date END) date_out 
FROM
(
     SELECT *,
               COUNT(flag) OVER (PARTITION BY flag ORDER BY id) Seq
     FROM table t
)t
GROUP BY card_no, descr_reader, Seq

#1