使用实例提取第二个最小id

Question

CUSTOMER(ID,CASE_ID,NAME,STATE)
          1,100,Alex,NY
          2,100,Alex,VA
          3,100,Alex,CT
          4,100,Tom,PA
          5,102,Peter,MO
          6,103,Dave,TN
          .
          .
          .

如何为每组case_id (

Answer 1

请尝试:

SELECT
    ID,
    CASE_ID
FROM
(
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY CASE_ID ORDER BY ID) Rn
    FROM CUSTOMER
)x
WHERE Rn=2

Answer 2

您可以使用窗口函数:

with cte as (
    select ID, CASE_ID, ROW_NUMBER() over (partition by CASE_ID order by ID) rn
    from CUSTOMER
)
select ID, CASE_ID
from cte
where rn = 2

或者您可以使用exists子句来删除第一行(即在存在值较低的行时获取最小值):

select MIN(ID) ID, CASE_ID
from CUSTOMER c
where exists (select 1 from CUSTOMER c2 where c2.ID < c.ID and c2.CASE_ID = c.CASE_ID)
group by CASE_ID

或者，另一种写法:

select MIN(ID) ID, CASE_ID
from CUSTOMER c
where c.ID > (select MIN(ID) from CUSTOMER c2 where c2.CASE_ID = c.CASE_ID)
group by CASE_ID