sql - SQL 在满足某个值之前选择所有值

Question

我有一个results看起来像这样的表：

ID	评分	天
1	5	'2001-01-01'
1	10	'2001-02-01'
1	8	'2001-03-01'
2	3	'2001-01-01'
2	10	'2001-02-01'
3	2	'2001-01-01'
3	5	'2001-02-01'
3	3	'2001-03-01'
4	8	'2001-01-01'
5	10	'2001-01-01'

我想选择在rating = 10到达之前或到达时到达的所有行，以获得如下内容：

ID	评分	天
1	5	'2001-01-01'
1	10	'2001-02-01'
2	3	'2001-01-01'
2	10	'2001-02-01'
5	10	'2001-01-01'

执行此操作的 SQL 查询是什么？

标签： sql

Answer 1

SELECT *
FROM table t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM table t2
                   WHERE t1.id = t2.id
                     AND t1.day > t2.day
                     AND t2.rating = 10 )
  AND EXISTS ( SELECT NULL
               FROM table t3
               WHERE t1.id = t3.id
                 AND t3.rating = 10 );

---

WITH
cte AS ( SELECT *, SUM(rating = 10) OVER (PARTITION BY id ORDER BY day DESC) ok
         FROM table )
SELECT *
FROM table
WHERE ok;