日期:2014-05-17 浏览次数:20450 次
;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] ) , cte as ( select row_number() over(order by word_text,relatedWord_Text) rn,* from ConfuseWords ) select word_text,relatedWord_Text, LevelNo, UnitNo from cte t where exists(select 1 from cte where t.word_text=relatedWord_Text and rn>t.rn) /* word_text relatedWord_Text LevelNo UnitNo --------- ---------------- ----------- ----------- he his 1 3 hi meet 1 1 */