SQL查询返回表中的所有列，但最多有3个重复的id

时间：2021-06-28 23:07:37

Can someone please lend a hand with this query? I've been fooling with LIMIT or TOP, but I think I'm off track. I want to return all fields from a table, but with a max of 3 duplicate id's in the new table.

有人可以帮忙解决这个问题吗?我一直在玩LIMIT或TOP,但我认为我偏离轨道。我想从表中返回所有字段,但在新表中最多有3个重复的id。

Table

id    first    last    
===================
1    John    Doe    
1    John    Doe   
1    John    Doe   
1    John    Doe   
2    Mary    Green  
2    Mary    Green  
3    Stacy   Kirk  
3    Stacy   Kirk  
3    Stacy   Kirk  
3    Stacy   Kirk  
3    Stacy   Kirk

Desired Results (up to 3 ids)

期望的结果(最多3个ID)

id    first    last  
====================
1    John     Doe  
1    John     Doe  
1    John     Doe  
2    Mary     Green  
2    Mary     Green  
3    Stacy    Kirk  
3    Stacy    Kirk  
3    Stacy    Kirk

Thanks!

1 个解决方案

#1

3  

since you mentioned TOP, this is for SQL SERVER

因为你提到过TOP,这是针对SQL SERVER的

SELECT id, first, last
FROM
  (
  SELECT id, first, last,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LAST) rn
  FROM TABLE1
  ) s
WHERE s.rn <= 3

#1

3  

since you mentioned TOP, this is for SQL SERVER

因为你提到过TOP,这是针对SQL SERVER的

SELECT id, first, last
FROM
  (
  SELECT id, first, last,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LAST) rn
  FROM TABLE1
  ) s
WHERE s.rn <= 3
标签：

相关文章