SQL查询过滤一个问题，会的请答一下
;WITH ConfuseWords AS(
SELECT 'hi' AS [word_text],'meet' AS [relatedWord_Text], 1 as [LevelNo], 1 as [UnitNo]
UNION all
SELECT 'meet' as [word_text],'hi' AS [relatedWord_Text], 1 as [LevelNo], 1 as [UnitNo]
UNION all
SELECT 'he' as [word_text],'his' as [relatedWord_Text], 1 as [LevelNo], 3 as [UnitNo]
UNION all
SELECT 'his' as [word_text],'he' as [relatedWord_Text], 1 as [LevelNo], 3 as [UnitNo]
union all
SELECT 'table' as [word_text],'coffee table' as [relatedWord_Text], 1 as [LevelNo], 3 as [UnitNo]
)
select distinct cw1.* from ConfuseWords as cw1
--结果如下：
--word_textrelatedWord_TextLevelNoUnitNo
--himeet11
--meethi11
--hehis13
--hishe13

--想要输出结果如下：
--word_textrelatedWord_TextLevelNoUnitNo
--himeet11
--hehis13




[解决办法]

SQL code

;WITH ConfuseWords AS(SELECT 'hi' AS [word_text],'meet' AS [relatedWord_Text], 1 as [LevelNo], 1 as [UnitNo]UNION allSELECT 'meet' as [word_text],'hi' AS [relatedWord_Text], 1 as [LevelNo], 1 as [UnitNo]UNION allSELECT 'he' as [word_text],'his' as [relatedWord_Text], 1 as [LevelNo], 3 as [UnitNo]UNION allSELECT 'his' as [word_text],'he' as [relatedWord_Text], 1 as [LevelNo], 3 as [UnitNo] union allSELECT 'table' as [word_text],'coffee table' as [relatedWord_Text], 1 as [LevelNo], 3 as [UnitNo] ),cte as(    select row_number() over(order by word_text,relatedWord_Text) rn,* from ConfuseWords)select word_text,relatedWord_Text, LevelNo, UnitNo from cte twhere exists(select 1 from cte where t.word_text=relatedWord_Text and rn>t.rn)/*word_text relatedWord_Text LevelNo     UnitNo--------- ---------------- ----------- -----------he        his              1           3hi        meet             1           1*/