如何从具有复合主键的表中选择不同的记录

Question

我希望了解如何从具有两个主键的SQL表中过滤出一个select。以下是示例：

╔═══════════════════╦════════════════════╦════════════╗
║ First Primary Key ║ Second Primary Key ║ Data       ║
╠═══════════════════╬════════════════════╬════════════╣
║ 1                 ║ 1                  ║ Bla,bla,bla║
║ 1                 ║ 2                  ║ Bla,bla,bla║
║ 1                 ║ 3                  ║ Bla,bla,bla║
║ 1                 ║ 4                  ║ Bla,bla,bla║
║ 2                 ║ 5                  ║ Bla,bla,bla║
║ 2                 ║ 6                  ║ Bla,bla,bla║
║ 2                 ║ 7                  ║ Bla,bla,bla║
║ 3                 ║ 8                  ║ Bla,bla,bla║
║ 4                 ║ 9                  ║ Bla,bla,bla║
║ 4                 ║ 10                 ║ Bla,bla,bla║
║ 4                 ║ 11                 ║ Bla,bla,bla║
║ 4                 ║ 12                 ║ Bla,bla,bla║
╚═══════════════════╩════════════════════╩════════════╝

我希望你能将第一列进行去重，并从第二列中仅取max(Second_Primary_Key)。

我想要的结果是：

╔═══════════════════╦════════════════════╦════════════╗
║ First_Primary_Key ║ Second_Primary_Key ║ Data       ║
╠═══════════════════╬════════════════════╬════════════╣
║ 1                 ║ 4                  ║ Bla,bla,bla║
║ 2                 ║ 7                  ║ Bla,bla,bla║
║ 3                 ║ 8                  ║ Bla,bla,bla║
║ 4                 ║ 12                 ║ Bla,bla,bla║
╚═══════════════════╩════════════════════╩════════════╝

结构应该像这样：

select * from foo  
where (distinct First_Primary_Key) and max(Second_Primary_Key)

- Francesco Mantovani

Answer 1

select First_PK, max(Second_PK) from foo group by First_PK

为了获取数据，我更喜欢使用窗口函数：

; with temp as (
 select row_number() over (partition by First_PK order by Second_PK desc)
   as row_num, First_PK, Second_PK, data
 from test)
 select * from temp
 where row_num = 1

- Giorgos Altanis

---

我喜欢窗口函数，但如何从“where row_num = 1”中获得最大值，而不是1，我需要“max”。 - Francesco Mantovani

你确定吗？desc关键字正是为了这个目的而存在的，以便每个First_PK下最大的Second_PK首先出现（因此行号为1）。我有什么遗漏的吗？ - Giorgos Altanis

Answer 2

您可以通过 group by 然后与其进行 join 来执行操作，例如：

select t1.first_primary_key, xxx.second_primary_key, t1.data
from tbl1 t1 join (
select first_primary_key, max(second_primary_key) as second_primary_key
from tbl1 
group by first_primary_key ) xxx 
on t1.first_primary_key = xxx.first_primary_key
AND t1.second_primary_key = xxx.second_primary_key;

- Rahul

---

我认为你缺少了一个 AND t1.second_primary_key = xxx.second_primary_key，否则你会返回所有行。 - Giorgos Altanis

@GiorgosAltanis，啊...好发现。谢谢。 - Rahul

合作无疑是最好的，你说呢？ :-) - Giorgos Altanis

Answer 3

使用 row_number() 函数获取每个 First_Primary_Key 对应的最高 Second_Primary_Key（每组取 top 1），可以使用row_number()函数实现。

select sub.First_Primary_Key, sub.Second_Primary_Key, sub.[Data]
from (
  select *
    , rn = row_number() over (
        partition by First_Primary_Key 
        order by Second_Primary_Key desc
      )
  from t
  ) as sub
where sub.rn = 1

---

cross apply 版本:

select distinct
    t.First_Primary_Key
  , x.Second_Primary_Key
  , x.[Data]
from t
  cross apply (
    select top 1
          *
      from t as i
      where i.First_Primary_Key = t.First_Primary_Key
      order by i.Second_Primary_Key desc
    ) as x;

---

top with ties 使用 row_number() 版本:

select top 1 with ties
    *
  from t
  order by 
    row_number() over (
      partition by First_Primary_Key
      order by Second_Primary_Key desc
      )

这个 inner join 版本和使用 rank() 而不是 row_number() 有相同的问题，如果一个 First_Primary_Key 具有多行相同的 max Second_Primary_Key，则可以得到多个结果。

inner join 版本：

select t.*
from t
  inner join (
    select MaxSecond_Primary_Key = max(Second_Primary_Key), First_Primary_Key
    from t
    group by First_Primary_Key
    ) as m
      on t.First_Primary_Key  = m.First_Primary_Key
     and t.Second_Primary_Key = m.MaxSecond_Primary_Key;

- SqlZim

---

那是一个长而且好的答案。 - Rahul