0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Chinese language

-- Tags: Spelling variants (different combinations of dashes)

with
PostCounts as (
  select
     Tags.Id         as TagId,
     count(Posts.Id) as PostCount
  from Posts    inner join
       PostTags on Posts.Id = PostTags.PostId
                inner join
       Tags     on PostTags.TagId = Tags.Id
  group by Tags.Id
),
Variants as (
  select *
  from (select replace(TagName, '-', '') as vname, * from tags) as r
  where vname != tagname
)

select
  v1.TagName,
  c1.PostCount,
  v2.TagName,
  c2.PostCount
from
  Variants v1   inner join
  Variants v2   on v1.vname = v2.vname
                inner join
  PostCounts c1 on v1.Id = c1.TagId
                inner join
  PostCounts c2 on v2.Id = c2.TagId
where v1.Id != v2.Id
  and c1.PostCount >= c2.PostCount
order by c2.PostCount desc;

fork query

created aug 9 10 sth