如何获取列的n个不同值的行

本文介绍了如何获取列的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;