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

Luka Ristovic 0
Viewed 33

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 :}

relational-database tsql mysql database sql
1 Answers

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

Iłya Bursov0
Related