从sql server获取特定的独立列

omhiaaxx 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(205)

我有两张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

sql sql-server Distinct

来源：https://stackoverflow.com/questions/62134221/fetch-specific-distinct-columns-from-sql-server

1条答案

按热度按时间

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

赞(0）回复(0）举报 2021-08-09

我来回答

从sql server获取特定的独立列

1条答案

相关问题

热门标签

最新问答