SQL使用N-1 Dense Rank过滤出密集秩N Mysql中的行

Mike_gundy123

如果我的标题不清楚,这里有一个我正在谈论的例子。

假设我有一个看起来像这样的表:

+----+------+----+----+---------+-------+---------+-------+
| 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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章