SQL - 查询id和category对应的最大值及大于该最大值的行

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中的解决方案来查找最后一个钢件，然后过滤掉任何具有更高部件序列的内容，但我希望有一个更有效的解决方案。

- AlexanderP

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

结果:

| 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 |

- MT0

Answer 2

请尝试以下内容

Select * from product  
join 

(Select Max(Part_Sequence) as sequence,productid from product 
group by Productid, Part Sequence) d

on product.Part_Sequence = d.sequence
and product.productid = d.productid

如果您想查询特定的部分，只需将where子句应用于内部和外部查询，以查询该部分。

- Pranay Rana

---

在SQL中，product.[Part Sequence]是无效的标识符。 - user330315

Answer 3

试试这个

select t1.* from table as t1 inner join 
(
select product_id,max(Part_Sequence) as Part_Sequence from table as t 
where Part_Material='Steel'
group by product_id
)  t2 on t1.product_id=t2.product_id
where t1.Part_Sequence>=t2.Part_Sequence

- Madhivanan

---

我原以为是SQL Server，我纠正了。谢谢 :) - Madhivanan

Answer 4

如果表中不同的product_id数目较少，与表中行的数目相比，则使用连接可能会表现良好（否则使用窗口函数的解决方案会更快）：

select p.*
from parts p
 join (
   select product_id, max(part_sequence) as max_seq
   from parts 
   where part_material = 'Steel'
   group by product_id
  ) t on t.product_id = p.product_id and p.part_sequence >= t.max_seq

请注意，此操作不会返回没有part_material = 'Steel'的零件。我不知道这是否是可能的情况。

- user330315