;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_text relatedWord_Text LevelNo UnitNo
--hi meet 1 1
--meet hi 1 1
--he his 1 3
--his he 1 3--想要输出结果如下:
--word_text relatedWord_Text LevelNo UnitNo
--hi meet 1 1
--he his 1 3
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_text relatedWord_Text LevelNo UnitNo
--hi meet 1 1
--meet hi 1 1
--he his 1 3
--his he 1 3--想要输出结果如下:
--word_text relatedWord_Text LevelNo UnitNo
--hi meet 1 1
--he his 1 3
--没看明白什么意思
select * from ConfuseWords where word_text in('hi','he')
/*
word_text relatedWord_Text LevelNo UnitNo
--------- ---------------- ----------- -----------
hi meet 1 1
he his 1 3
*/
;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
*/