问行间数据库相互关系

EN

--------------------------------------------------
article_id | title | year_published| articletype
--------------------------------------------------
1|Mechanical Code|2012|1
2|Mechanical Code|2015|1
3|Contruction Workshop|2010|2
4|Contruction Workshop|2012|2
5|Contruction Workshop|2013|2
6|Administrative Session I|2012|3
7|Administrative Session II|2014|3
8|Administrative Session III|2015|3

select title, count(*) as total from articles group by articletype  having total > 1;

select (case when substring_index(title, ' ', -1) in ('I', 'II', 'III')
            then left(title, length(title) - locate(' ', reverse(title)))
            else title
       end) as basetitle, count(*) as total
from articles
group basetitle
having count(*) > 1;