获取带有其他信息的重复项 [英] Getting duplicates with additional information

问题描述

我已经继承了一个数据库，但是在构建有效的SQL查询时遇到了麻烦.

I've inherited a database and I'm having trouble constructing a working SQL query.

假设这是数据:

[Products]

| Id    | DisplayId     | Version   | Company   | Description   |
|----   |-----------    |---------- |-----------| -----------   |
| 1     | 12345         | 0         | 16        | Random        |
| 2     | 12345         | 0         | 2         | Random 2      |
| 3     | AB123         | 0         | 1         | Random 3      |
| 4     | 12345         | 1         | 16        | Random 4      |
| 5     | 12345         | 1         | 2         | Random 5      |
| 6     | AB123         | 0         | 5         | Random 6      |
| 7     | 12345         | 2         | 16        | Random 7      |
| 8     | XX45          | 0         | 5         | Random 8      |
| 9     | XX45          | 0         | 7         | Random 9      |
| 10    | XX45          | 1         | 5         | Random 10     |
| 11    | XX45          | 1         | 7         | Random 11     |


[Companies]

| Id    | Code      |
|----   |-----------|
| 1     | 'ABC'     |
| 2     | '456'     |
| 5     | 'XYZ'     |
| 7     | 'XYZ'     |
| 16    | '456'     |

Version列是版本号.较高的数字表示较新的版本. Company列是引用Id列上Companies表的外键. 还有一个名为ProductData的表，其中的ProductId列引用了Products.Id.

The Versioncolumn is a version number. Higher numbers indicate more recent versions. The Company column is a foreign key referencing the Companies table on the Id column. There's another table called ProductData with a ProductId column referencing Products.Id.

现在，我需要根据DisplayId和相应的Companies.Code查找重复项.应将ProductData表连接起来以显示标题(ProductData.Title)，并且结果中应仅包括最新的标题.因此，预期结果是:

Now I need to find duplicates based on the DisplayId and the corresponding Companies.Code. The ProductData table should be joined to show a title (ProductData.Title), and only the most recent ones should be included in the results. So the expected results are:

| Id    | DisplayId     | Version   | Company   | Description   | ProductData.Title |
|----   |-----------    |---------- |-----------|-------------  |------------------ |
| 5     | 12345         | 1         | 2         | Random 2      | Title 2           |
| 7     | 12345         | 2         | 16        | Random 7      | Title 7           |
| 10    | XX45          | 1         | 5         | Random 10     | Title 10          |
| 11    | XX45          | 1         | 7         | Random 11     | Title 11          |

• 因为XX45有2个条目":一个与公司5相关，一个与公司7相关，但是两个公司共享相同的代码.
• 因为12345有2个条目":一个与公司2相关，一个与公司16相关，但是两个公司共享相同的代码.请注意，两者的最新版本有所不同(公司16的条目的版本2和公司2的条目的版本1)
• 不应包含ABC123，因为其2个条目具有不同的公司代码.

我很想学习您的见解...

I'm eager to learn your insights...

推荐答案

尝试一下:

SELECT b.ID,displayid,version,company,productdata.title
FROM 
(select A.ID,a.displayid,version,a.company,rn,a.code, COUNT(displayid)  over (partition by displayid,code) cnt from
(select Prod.ID,displayid,version,company,Companies.code, Row_number() over (partition by displayid,company order by version desc) rn
from Prod inner join Companies on Prod.Company = Companies.id) a  
where a.rn=1) b inner join productdata on b.id = productdata.id  where cnt =2

这篇关于获取带有其他信息的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！