我需要获取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;