我正在撰寫代碼以在具有相同brand_code 的同一個表中查找重復的Sub_brand_Descriptions。我能夠生成下面的表格（這是我需要的），但是第一行和第二行是相同的，因為我將表格與自己連接起來。有沒有辦法洗掉任何重復的行，因為不同的行將不起作用（同一列中的不同子品牌 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 | 乙 |
uj5u.com熱心網友回復：
在您的查詢中為 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
uj5u.com熱心網友回復：
您不必加入兩次，一次并旋轉結果即可
SELECT
brands.brand_code,
MAX(CASE WHEN rn = 1 THEN sub.sub_brand_id END) as Sub_Brand_ID1,
MAX(CASE WHEN rn = 1 THEN sub.sub_brand END) as Sub_Brand_Descrption1,
MAX(CASE WHEN rn = 2 THEN sub.sub_brand_id END) as Sub_Brand_ID2,
MAX(CASE WHEN rn = 2 THEN sub.sub_brand END) as Sub_Brand_Descrption2
from
table1 brands
INNER JOIN (SELECT *, row_number() over (partition by Brand_Code order by sub_brand_id) as rn from table2) sub on sub.brand_code = brands.brand_code and sub.langu = 'E'
GROUP BY brands.brand_code
我假設您想要 table1 中的其他列，否則放棄它并從 t2 中執行整個查詢，而無需連接：
SELECT
sub.brand_code,
MAX(CASE WHEN rn = 1 THEN sub.sub_brand_id END) as Sub_Brand_ID1,
MAX(CASE WHEN rn = 1 THEN sub.sub_brand END) as Sub_Brand_Descrption1,
MAX(CASE WHEN rn = 2 THEN sub.sub_brand_id END) as Sub_Brand_ID2,
MAX(CASE WHEN rn = 2 THEN sub.sub_brand END) as Sub_Brand_Descrption2
from
(SELECT *, row_number() over (partition by Brand_Code order by sub_brand_id) as rn from table2) sub
WHERE
sub.langu = 'E'
GROUP BY sub.brand_code
ps; 你的問題有一些明顯的錯別字：
你需要自己解決這些問題，因為我不知道哪個是準確的
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/346128.html
標籤：sql sql-server 查询语句
上一篇：我可以在IF陳述句中使用OPTION(MAXDOP1)進行查詢嗎？
下一篇：嘗試創建滾動行限制存盤程序