Вопросы и ответы для программистов
with ShortTags as ( select Id, Count, TagName as [TagName0], left(TagName, len(TagName) - 1) as [TagName1], left(TagName, len(TagName) - 2) as [TagName2] from Tags ) select t1.TagName0 as [Singular Tag], t1.Count, tN.TagName0 as [Plural Tag], tN.Count, iif(t1.Count > tN.Count, 'Singular', 'Plural') as [Wins], tsyn.Score as [Syn Score], tsyn.ApprovalDate as [Syn Approved] from ShortTags as t1 inner join ShortTags as tN on t1.Id <> tN.Id left join TagSynonyms as tsyn on (tsyn.SourceTagName = t1.TagName0 and tsyn.TargetTagName = tN.TagName0) or (tsyn.TargetTagName = t1.TagName0 and tsyn.SourceTagName = tN.TagName0) where 1=1 and t1.TagName0 like N'%[а-я]' and tN.TagName0 like N'%[иыая]' and t1.Count > 0 and tN.Count > 0 and (0=1 or t1.TagName0 = tN.TagName0 or t1.TagName0 = tN.TagName1 or t1.TagName0 = tN.TagName2 or t1.TagName1 = tN.TagName1 or t1.TagName1 = tN.TagName2 ) and not (1=1 -- reversed condition to avoid duplicate rows and tN.TagName0 like N'%[а-я]' and t1.TagName0 like N'%[иыая]' and (0=1 or tN.TagName0 = t1.TagName0 or tN.TagName0 = t1.TagName1 or tN.TagName0 = t1.TagName2 or tN.TagName1 = t1.TagName1 or tN.TagName1 = t1.TagName2 ) ) order by t1.Count + tN.Count desc