sql - 删除在不同列中具有相同键的重复行

Question

我正在编写代码以在具有相同品牌代码的同一张表中查找重复的 Sub_brand_Descriptions。我能够生成下面的表格（这是我需要的）但是第一行和第二行是相同的，因为我将表格与自身连接起来。有没有办法删除任何重复的行，因为 distinct 将不起作用（同一列中的不同子品牌 id 但因为它出现在不同列的下一行中，从技术上讲它们是相同的）？

select distinct
brands.BRAND_ID as Brand_Code,
sub.SUB_BRAND_ID as Sub_Brand_ID1,
sub.SUB_BRAND as Sub_Brand_Descrption1,
sub2.SUB_BRAND_ID as Sub_Brand_ID2,
sub2.SUB_BRAND as Sub_Brand_Descrption2

from table1 as brands
inner join table2 as sub on sub.BRAND_ID = brands.BRAND_ID and sub.LANGU = 'E'
inner join table2 as sub2 on sub2.SUB_BRAND = sub.SUB_BRAND and sub2.LANGU = 'E'

where sub.SUB_BRAND_ID != sub2.SUB_BRAND_ID and sub.BRAND_ID = sub2.BRAND_ID

品牌_代码	子品牌 ID1	子品牌描述1	子品牌 ID2	子品牌描述2
美国广播公司	X123	X123ABC	Y123	X123ABC
美国广播公司	Y123	X123ABC	X123	X123ABC

期望的输出：

品牌_代码	子品牌 ID1	子品牌描述1	子品牌 ID2	子品牌描述2
美国广播公司	X123	X123ABC	Y123	X123ABC

源数据： 表 1：

品牌_ID	标签
美国广播公司	1
CDE	1
EFG	2

来源表2：

品牌_ID	子品牌 ID	子品牌	语言
美国广播公司	X123	X123ABC	乙
美国广播公司	Y123	X123ABC	乙
英国广播公司	X223	H23ABC	乙
英国广播公司	Y223	H23ABC	乙

标签： sqlsql-servertsql

Answer 1

在查询中为 row_number 添加一个函数并将其过滤为 1，请参见下面的示例：（您可以根据需要更改 order_by_clause）

with cte as (

select distinct
brands.BRAND_ID as Brand_Code,
sub.SUB_BRAND_ID as Sub_Brand_ID1,
sub.SUB_BRAND as Sub_Brand_Descrption1,
sub2.SUB_BRAND_ID as Sub_Brand_ID2,
sub2.SUB_BRAND as Sub_Brand_Descrption2,
row_number() over (partition by Sub_Brand_Descrption1 order by Sub_Brand_ID1) as rn

from table1 as brands
inner join table2 as sub on sub.BRAND_ID = brands.BRAND_ID and sub.LANGU = 'E'
inner join table2 as sub2 on sub2.SUB_BRAND = sub.SUB_BRAND and sub2.LANGU = 'E'

where sub.SUB_BRAND_ID != sub2.SUB_BRAND_ID and sub.BRAND_ID = sub2.BRAND_ID

)

Select * from cte where rn=1;

或者

Select * from (
select distinct
brands.BRAND_ID as Brand_Code,
sub.SUB_BRAND_ID as Sub_Brand_ID1,
sub.SUB_BRAND as Sub_Brand_Descrption1,
sub2.SUB_BRAND_ID as Sub_Brand_ID2,
sub2.SUB_BRAND as Sub_Brand_Descrption2,
row_number() over (partition by Sub_Brand_Descrption1 order by Sub_Brand_ID1) as rn

from table1 as brands
inner join table2 as sub on sub.BRAND_ID = brands.BRAND_ID and sub.LANGU = 'E'
inner join table2 as sub2 on sub2.SUB_BRAND = sub.SUB_BRAND and sub2.LANGU = 'E'

where sub.SUB_BRAND_ID != sub2.SUB_BRAND_ID and sub.BRAND_ID = sub2.BRAND_ID) as temp

where rn=1