0

Please login or register to vote for this query.

(click on this box to dismiss)

Вопросы и ответы для программистов

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

fork query

edited may 11 15

created may 11 15 Athari