sql - 如何选择支付的 2 个最高价格

Question

这是输入数据。

  Dept            Company                   Code      Payment Amt

Gardeners     Sort:Insurance Carrier        100         20.00
Gardeners     Sort:Insurance Carrier        100         22.00
Gardeners     Sort:Insurance Carrier        100         21.00
Gardeners     Sort:Insurance Carrier        100         20.00
Gardeners     Sort:Insurance Carrier        100         22.00 

我想回去

Sort:Insurance Carrier 100   -  22.00 and 21.00

不是 22.00 和 22.00 我担心这段代码会返回 22 和 22 可以说是支付的 2 个最高价格，但实际上并非如此。

我有这个SQL

SELECT 
[DEPT], [Sort: Procedure Code] as Code, [Sort: Insurance Carrier], 
SUM(CASE WHEN num = 1 THEN [Pmt Amount] ELSE 0 END) AS [first high], 
SUM(CASE WHEN num = 2 THEN [Pmt Amount] ELSE 0 END) AS [second high] 
FROM 
(
SELECT ROW_NUMBER() OVER(PARTITION BY 
[DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier]
ORDER BY [Pmt Amount] DESC) AS num, 
[DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier], 
[Pmt Amount]
FROM 
[revenuedetail$]
) AS t 
WHERE num IN (1, 2)
GROUP BY [DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier]

Answer 1

如果您希望相同的值具有相同的数字，那么您应该使用dense_rank() 而不是row_number()。但你走在正确的轨道上!

同时将 sum() 更改为 max() 以避免使用相同的 dense_rank() 对值求和。

试试这个:

select 
    [dept]
  , [Sort: Procedure Code] as Code
  , [Sort: Insurance Carrier]
  , max(case when num = 1 then [Pmt Amount] else 0 end) as [first high]
  , max(case when num = 2 then [Pmt Amount] else 0 end) as [second high] 
from (
  select 
      dense_rank() over(
        partition by [dept], [Sort: Procedure Code], [Sort: Insurance Carrier]
        order by [Pmt Amount] desc
        ) as num
    , [dept]
    , [Sort: Procedure Code]
    , [Sort: Insurance Carrier]
    , [Pmt Amount]
  from [revenuedetail$]
  ) as t 
where num in (1, 2)
group by [dept], [Sort: Procedure Code], [Sort: Insurance Carrier]

rextester 演示:http://rextester.com/PJCDDC90476

返回:

+-----------+------+-------------------------+------------+-------------+
|   dept    | Code | Sort: Insurance Carrier | first high | second high |
+-----------+------+-------------------------+------------+-------------+
| Gardeners |  100 | Sort:Insurance Carrier  | 22.00      | 21.00       |
+-----------+------+-------------------------+------------+-------------+