从sql server获取特定的独立列

omhiaaxx  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(316)

我有两张table:
表a:

ID | Date      | Uploaded Date
---+-----------+--------------
16 | 05/01/2020| 05/05/2020 12:36 PM
18 | 05/01/2020| 05/05/2020 12:20 PM

表b:

ID | Value | Amount  | A_ID
---+-------+---------+-----
1  | 3     | 76.295  | 16
2  | 2     | 93.465  | 16
3  | 5     | 82.396  | 16
4  | 8     | 62.2736 | 16
5  | 9     | 50.71   | 16
6  | 3     | 75.869  | 18
7  | 2     | 93.465  | 18
8  | 5     | 82.396  | 18
9  | 8     | 62.2736 | 18
10 | 9     | 50.71   | 18

我想写一本书 select 根据条件获取唯一数据的语句。
我要找的是基于值列的唯一记录。fyr-低于输出:

Date       | Amount  | Value
---+-------+---------+------
05/01/2020 | 76.295  | 3
05/01/2020 | 93.465  | 2
05/01/2020 | 82.396  | 5
05/01/2020 | 62.2736 | 8
05/01/2020 | 50.71   | 9

下面是我尝试过的几个问题:

select Date, Amount, Value
from A
left join B on A.ID = B.A_ID  order by Uploaded Date desc

执行上述查询后,我得到以下数据:

Date       | Amount  | Value
---+-------+---------+------
05/01/2020 | 76.295  | 3
05/01/2020 | 93.465  | 2
05/01/2020 | 82.396  | 5
05/01/2020 | 62.2736 | 8
05/01/2020 | 50.71   | 9
05/01/2020 | 75.869  | 3
05/01/2020 | 93.465  | 2
05/01/2020 | 82.396  | 5
05/01/2020 | 62.2736 | 8
05/01/2020 | 50.71   | 9

我试过了 distinct 查询:

select distinct Date, Amount, Value
from A
left join B on a.ID = b.A_ID order by uploaded date desc

但它仍然会返回重复的数据:

Date       | Amount  | Value
---+-------+---------+------
05/01/2020 | 76.295  | 3
05/01/2020 | 93.465  | 2
05/01/2020 | 82.396  | 5
05/01/2020 | 62.2736 | 8
05/01/2020 | 50.71   | 9
05/01/2020 | 75.869  | 3

具有多条记录的表。

ID | Date      | Uploaded Date
---+-----------+--------------
16 | 05/01/2020| 05/05/2020 12:36 PM
18 | 05/01/2020| 05/05/2020 12:20 PM
19 | 05/02/2020| 05/05/2020 12:43 PM
20 | 05/03/2020| 06/05/2020 11:57 AM
21 | 05/04/2020| 06/05/2020 11:57 AM
ID | Value | Amount  | A_ID
---+-------+---------+-----
1  | 3     | 76.295  | 16
2  | 2     | 93.465  | 16
3  | 3     | 82.396  | 16
4  | 8     | 62.2736 | 16
5  | 3     | 50.71   | 19
6  | 3     | 50.51   | 20
7  | 4     | 52.71   | 21
8  | 4     | 55.11   | 20

在这种情况下,我希望o/p:

Date       | Amount  | Value
---+-------+---------+------
05/01/2020 | 76.295  | 3
05/01/2020 | 93.465  | 2
05/01/2020 | 62.2736 | 8
05/02/2020 | 50.71   | 3
05/03/2020 | 50.51   | 3
05/03/2020 | 55.11   | 4
05/04/2020 | 52.71   | 4
at0kjp5o

at0kjp5o1#

如果我正确理解您的要求,那么您只需要列值中具有相同值的所有行中的第一行(基于id)。如果它是正确的,您可以使用行号来实现您的要求,如下所示-
此处演示

SELECT ID,Value,Amount,A_ID 
FROM 
(
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY Value ORDER BY ID) RN
    FROM Table_B
)A
WHERE RN = 1
ORDER BY ID

现在您可以将输出与表\u a连接起来,以选取其他必要的数据。
如果您有多个日期,并且需要保留每个日期的第一条记录,则行号生成将如下所示。查询的其他部分将与现在一样。

ROW_NUMBER() OVER(PARTITION BY CAST(Date AS Date),Value ORDER BY ID) RN

相关问题