SQL - 选择值大于 id 和类别最大值的行

Question

+------------+---------------+---------------+----------------+
| Product ID | Part Sequence | Part Material |  Description   |
+------------+---------------+---------------+----------------+
|          1 |             1 | Steel         | Part A         |
|          1 |             2 | CFK           | Part B         |
|          1 |             3 | CFK           | Part B Variant |
|          1 |             4 | Steel         | Part C         |
|          1 |             5 | GFK           | Part D         |
|          1 |             6 | Plastic       | Part E         |
|          2 |             1 | Steel         | Part A         |
|          2 |             2 | CFK           | Part B         |
|          2 |             3 | Steel         | Part F         |
|          2 |             4 | CFK           | Part B         |
|          2 |             5 | Steel         | Part G         |
|          2 |             6 | Silicon       | Part D+        |
|          2 |             7 | Plastic       | Part E         |
+------------+---------------+---------------+----------------+

(按产品ID和零件顺序排序只是为了可读性，我的数据库表是无序的)

我需要查询每个产品 ID 的所有行，其中零件序列等于或高于最后一个钢零件。

所以对于上面的表格，预期结果是:

+------------+---------------+---------------+----------------+
| Product ID | Part Sequence | Part Material |  Description   |
+------------+---------------+---------------+----------------+
|          1 |             4 | Steel         | Part C         |
|          1 |             5 | GFK           | Part D         |
|          1 |             6 | Plastic       | Part E         |
|          2 |             5 | Steel         | Part G         |
|          2 |             6 | Silicon       | Part D+        |
|          2 |             7 | Plastic       | Part E         |
+------------+---------------+---------------+----------------+

我可以使用 SQL Select only rows with Max Value on a Column FILTERED by Column 中的解决方案找到最后一个钢零件，然后过滤具有更高零件序列的任何东西，但我希望有一个更有效的解决方案。

Answer 1

使用窗口聚合函数(只需要遍历表一次)而不是使用连接(需要遍历表两次):

[它还将返回没有钢部件的产品 - 如果这是一个问题，则可以使用类似的窗口查询来过滤掉这些行。]

SQL Fiddle

Oracle 11g R2 架构设置:

CREATE TABLE TEST ( Product_ID, Part_Sequence, Part_Material, Description ) AS
          SELECT 1, 1, 'Steel',   'Part A' FROM DUAL
UNION ALL SELECT 1, 2, 'CFK',     'Part B' FROM DUAL
UNION ALL SELECT 1, 3, 'CFK',     'Part B Variant' FROM DUAL
UNION ALL SELECT 1, 4, 'Steel',   'Part C' FROM DUAL
UNION ALL SELECT 1, 5, 'GFK',     'Part D' FROM DUAL
UNION ALL SELECT 1, 6, 'Plastic', 'Part E' FROM DUAL
UNION ALL SELECT 2, 1, 'Steel',   'Part A' FROM DUAL
UNION ALL SELECT 2, 2, 'CFK',     'Part B' FROM DUAL
UNION ALL SELECT 2, 3, 'Steel',   'Part F' FROM DUAL
UNION ALL SELECT 2, 4, 'CFK',     'Part B' FROM DUAL
UNION ALL SELECT 2, 5, 'Steel',   'Part G' FROM DUAL
UNION ALL SELECT 2, 6, 'Silicon', 'Part D+' FROM DUAL
UNION ALL SELECT 2, 7, 'Plastic', 'Part E' FROM DUAL
UNION ALL SELECT 3, 1, 'Silicon', 'Part A' FROM DUAL
UNION ALL SELECT 3, 2, 'Plastic', 'Part B' FROM DUAL;

查询 1:

SELECT Product_ID,
       Part_Sequence,
       Part_Material,
       Description
FROM   (
  SELECT t.*,
         COALESCE(
           SUM( CASE Part_Material WHEN 'Steel' THEN 1 ELSE 0 END )
             OVER ( PARTITION BY Product_ID
                    ORDER BY Part_Sequence
                    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ),
           0
         ) AS isInvalid
  FROM   TEST t
)
WHERE  isInvalid = 0

Results :

| PRODUCT_ID | PART_SEQUENCE | PART_MATERIAL | DESCRIPTION |
|------------|---------------|---------------|-------------|
|          1 |             4 |         Steel |      Part C |
|          1 |             5 |           GFK |      Part D |
|          1 |             6 |       Plastic |      Part E |
|          2 |             5 |         Steel |      Part G |
|          2 |             6 |       Silicon |     Part D+ |
|          2 |             7 |       Plastic |      Part E |
|          3 |             1 |       Silicon |      Part A |
|          3 |             2 |       Plastic |      Part B |