如何获取列的n个不同值的行 [英] How to get rows for n distinct values of a column
本文介绍了如何获取列的n个不同值的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要获取n个不同发票编号的行。
I need to get rows for n distinct invoice numbers.
我有SELECT查询，该查询提供了表中的结果：
I have SELECT query that gives me results from the table:
SELECT A.INVOICE_NUMBER, A.INVOICE_SEQ_NUMBER, B.FILE_NUMBER
FROM TABLE1 AS A, TABLE2 AS B
WHERE A.INVOICE_NUMBER = B.INVOICE_NUMBER
AND A.INVOICE_SEQ_NUMBER = B.INVOICE_SEQ_NUMBER
所以我得到了我的查询：
So I get this as a result of my query:
-----------------------------------------------------
| INVOICE_NUMBER | INVOICE_SEQ_NUMBER | FILE_NUMBER |
------------------------------------------------------
|1111111111-1 | 1 | P4324324525 |
-----------------------------------------------------
|1111111111-1 | 2 | P4565674574 |
-----------------------------------------------------
|1111111111-1 | 3 | V4324552557 |
-----------------------------------------------------
|1111111111-1 | 4 | V4324552525 |
-----------------------------------------------------
|2222222222-2 | 1 | S4563636574 |
-----------------------------------------------------
|3333333333-3 | 1 | Q4324325675 |
-----------------------------------------------------
|3333333333-3 | 2 | Q4565674574 |
-----------------------------------------------------
|4444444444-4 | 1 | F4326364366 |
-----------------------------------------------------
|4444444444-4 | 2 | F4565636323 |
-----------------------------------------------------
|4444444444-4 | 3 | F4398798588 |
-----------------------------------------------------
|4444444444-4 | 4 | F4565865888 |
-----------------------------------------------------
但是我需要的是例如仅获得结果三个不同的发票编号，因此我的查询应给出以下信息：
But what I need is, for example, to get result for only three distinct invoice numbers, so my query should give this:
-----------------------------------------------------
| INVOICE_NUMBER | INVOICE_SEQ_NUMBER | FILE_NUMBER |
------------------------------------------------------
|1111111111-1 | 1 | P4324324525 |
-----------------------------------------------------
|1111111111-1 | 2 | P4565674574 |
-----------------------------------------------------
|1111111111-1 | 3 | V4324552557 |
-----------------------------------------------------
|1111111111-1 | 4 | V4324552525 |
-----------------------------------------------------
|2222222222-2 | 1 | S4563636574 |
-----------------------------------------------------
|3333333333-3 | 1 | Q4324325675 |
-----------------------------------------------------
|3333333333-3 | 2 | Q4565674574 |
-----------------------------------------------------
我如何实现？
推荐答案
您听说过 JOIN 吗？
无论如何，一种解决问题的方法是使用 DENSE_RANK（）：
In any case, one way to solve your problem uses DENSE_RANK():
SELECT INVOICE_NUMBER, INVOICE_SEQ_NUMBER, FILE_NUMBER
FROM (SELECT A.INVOICE_NUMBER, A.INVOICE_SEQ_NUMBER, B.FILE_NUMBER,
DENSE_RANK() OVER (ORDER BY A.INVOICE_NUMBER) as seqnum
FROM TABLE1 A JOIN
TABLE2 B
ON A.INVOICE_NUMBER = B.INVOICE_NUMBER AND
A.INVOICE_SEQ_NUMBER = B.INVOICE_SEQ_NUMBER
) t
WHERE seqnum <= 3;
这篇关于如何获取列的n个不同值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
