sql - 在 ORACLE SQL 中使用 distinct 时如何避免在列中出现重复

Question

我试图实现这一点，但即使使用 DISTINCT，我仍然在 item_name 列中有多个相同的项目。

这就是我得到的

contract_id | item_name | max(amount) | 
1           | item1     | 20
12          | item1     | 25
111         | item1     | 10
3           | item2     | 15
32          | item2     | 30

但这就是我想要的

contract_id | item_name | max(amount) | 
12          | item1     | 25
32          | item2     | 30



SELECT DISTINCT tb2.item_detail_id
       tb2.item_name
       tb1.contract_id
       max(tb1.amount)

FROM main_table                                              tb1

INNER JOIN (SELECT DISTINCT t_tb2.item_detail_id
            MAX(t_tb1.amount)
            FROM main_table                                  t_tb1
            INNER JOIN item                                  t_tb2
             ON t_tb1.item_detail_id = t_tb2.item_detail_id
            GROUP BY t_tb2.item_detail_id)                   temp
 ON tb1.item_detail_id = temp.item_detail_id
INNER JOIN item                                              tb2
 ON tb1.item_detail_id = tb2.item_detail_id

GROUP BY tb2.item_name,
        tb1.contract_id,
        tb1.amount

标签： sqloraclegreatest-n-per-group

Answer 1

您可以使用聚合，但像这样：

SELECT tb2.item_name
       MAX(tb1.contract_id) KEEP (DENSE_RANK FIRST ORDER BY tb1.amount DESC) as contract_id,
       MAX(tb1.amount)

FROM . . .
GROUP BY tb2.item_name