如果我的标题不清楚,这里有一个我正在谈论的例子。
假设我有一个看起来像这样的表:
+----+------+----+----+---------+-------+---------+-------+
| ID | Time | X | Y | X_START | X_END | Y_START | Y_END |
+----+------+----+----+---------+-------+---------+-------+
| 1 | 0 | 8 | 6 | 6 | 10 | 4 | 8 |
| 2 | 0 | 20 | 10 | 18 | 22 | 8 | 12 |
| 3 | 1 | 8 | 8 | 6 | 10 | 6 | 10 |
| 4 | 1 | 10 | 24 | 8 | 12 | 22 | 26 |
+----+------+----+----+---------+-------+---------+-------+
如果我将此查询应用于表:
WITH
cte1 AS (SELECT *,
DENSE_RANK() OVER (ORDER BY TIME) AS DENSE_RANK
FROM data
ORDER BY TIME)
SELECT * FROM cte1
我得到:
+----+------+----+----+---------+-------+---------+-------+------------+
| ID | Time | X | Y | X_START | X_END | Y_START | Y_END | DENSE_RANK |
+----+------+----+----+---------+-------+---------+-------+------------+
| 1 | 0 | 8 | 6 | 6 | 10 | 4 | 8 | 1 |
| 2 | 0 | 20 | 10 | 18 | 22 | 8 | 12 | 1 |
| 3 | 1 | 8 | 8 | 6 | 10 | 6 | 10 | 2 |
| 4 | 1 | 10 | 24 | 8 | 12 | 22 | 26 | 2 |
+----+------+----+----+---------+-------+---------+-------+------------+
现在我想要做的是过滤掉 X 在 X_START - X_END 范围内且 Y 在 Y_START - Y_END 范围内且 DENSE_RANK 为 n-1 的任何行
所以我想要一个看起来像这样的结果:
+----+------+----+----+---------+-------+---------+-------+------------+
| ID | Time | X | Y | X_START | X_END | Y_START | Y_END | DENSE_RANK |
+----+------+----+----+---------+-------+---------+-------+------------+
| 1 | 0 | 8 | 6 | 6 | 10 | 4 | 8 | 1 |
| 2 | 0 | 20 | 10 | 18 | 22 | 8 | 12 | 1 |
| 4 | 1 | 10 | 24 | 8 | 12 | 22 | 26 | 2 |
+----+------+----+----+---------+-------+---------+-------+------------+
我对 SQL 很陌生,所以我不太确定如何解决这个问题。提前感谢您的所有帮助!
使用MIN()
窗口函数确定ID
每个的最小值,Time
以便在满足所有其他条件的情况下排除该行:
WITH cte AS (
SELECT *,
DENSE_RANK() OVER (ORDER BY TIME) AS `DENSE_RANK`,
MIN(ID) OVER (PARTITION BY TIME) min_id
FROM data
)
SELECT ID, Time, X, Y, X_START, X_END, Y_START, Y_END, `DENSE_RANK`
FROM cte
WHERE `DENSE_RANK` = 1
OR NOT (ID = min_id AND X BETWEEN X_START AND X_END AND Y BETWEEN Y_START AND Y_END)
ORDER BY `DENSE_RANK`, ID;
请参阅演示。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句