How can I know how much years have in SQL tables?-CodePudding

I need to print every city from table which has at least one student in every generation. Table is simple but I don't know how to extract every year from that table, because its string.

indeks | city

1/2018 | London ;
2/2018 | Paris ;
3/2018 | null;
4/2019 | London ;
4/2020 | London;

In this case SQL query needs print London, because every year from table has one student (2018,2019 and 2020). I have no idea how to even start query :}

CodePudding user response：

I'd try something like that:

select city from
(
  select
    city, count(distinct cast(SUBSTRING(indeks, LOCATE('/', indeks) 1) as unsigned)) as dy
  from cities
  group by city
) as t
where 
dy = (
  select
    max(cast(SUBSTRING(indeks, LOCATE('/', indeks) 1) as unsigned))-min(cast(SUBSTRING(indeks, LOCATE('/', indeks) 1) as unsigned)) 1
  from cities
)

so in inner query it will calculate how many different years are filled for the each city

outer filter will check which one has all of them