我如何选择每一个特定的数据到一定的数量?

How can I select each particular data upto a certain quantity. For example in the below table, there are 4 A, 4 B, 2 C and 1 D. Now I want to select all letters but not more than two each of it, Which will yield 2 A, 2 B, 2 C and 1 D.

我如何选择每一个特定的数据到一定的数量。例如，在下面的表格中，有4个A, 4个B, 2个C和1个D，现在我想选择所有的字母，但不超过2个，这将产生2 A, 2 B, 2 C和1 D。

+====+========+
| ID | Letter |
+====+========+
|  1 | A      |
+----+--------+
|  2 | B      |
+----+--------+
|  3 | B      |
+----+--------+
|  4 | C      |
+----+--------+
|  5 | A      |
+----+--------+
|  6 | A      |
+----+--------+
|  7 | C      |
+----+--------+
|  8 | B      |
+----+--------+
|  9 | B      |
+----+--------+
| 10 | D      |
+----+--------+
| 11 | A      |
+----+--------+

Can anyone please help me for the above scenario?

有没有人能帮我解决以上的问题?

3 个解决方案

#1

I can think of a simple way:

我可以想到一个简单的方法:

select 
    case 
        when count(*) > 1 
            then 2 
        else count(*) 
    end,
    second_column
from your_table 
group by second_column;

This will give the result you want, but it won't really 'select ONLY two or less records' of each.

这将给出您想要的结果，但它不会真正“只选择两个或更少的记录”。

#2

Using a ROW_NUMBER() function and a derived table:

使用ROW_NUMBER()函数和派生表:

CREATE TABLE myTable (id int, Letter varchar(1))

INSERT INTO myTable
VALUES (1,'A')
                ,(2,'B')
                ,(3,'B')
                ,(4,'C')
                ,(5,'A')
                ,(6,'A')                    
                ,(7,'C')
                ,(8,'B')
                ,(9,'B')
                ,(10,'D')
                ,(11,'A')

SELECT id, Letter
FROM
(SELECT *
    ,ROW_NUMBER() OVER(PARTITION BY Letter ORDER BY Letter) as rn
FROM myTable) myTable
WHERE rn = 1 or rn = 2

In essence, "cut" (PARTITION) the rows by Letters, and assign them each a number for its unique group, then pick the first two of each Letter.

在本质上，用字母“分割”(分区)行，并为其独特的组分配每个数字，然后选择每个字母的前两个。

Try it here:

试一试:

http://rextester.com/WTKYCE51114

#3

Use ROW_NUMBER() function to tag each record the row number and PARTITION it BY (grouping by) letter and ORDER it BY (id)

使用ROW_NUMBER()函数标记每个记录行号并按(分组)字母对其进行分区，并按(id)排序

SELECT id,
       letter
  FROM (SELECT *,
               ROW_NUMBER() OVER(PARTITION BY letter ORDER BY id) rnum
          FROM myTable
       ) t
 WHERE rnum <=2

Ordering it by id, you will have the first two instances of each letter in ascending order, thus you will have below result (note that id 1 and 5 are selected for A, 2 and 3 for B)

按id排序，每个字母的前两个实例按升序排列，因此您将得到以下结果(注意，id 1和5被选为A、2和3)

id  letter
1   A
5   A
2   B
3   B
4   C
7   C
10  D

#1