如何提取SQL表中年份并筛选各年份均有学生的城市

要实现这个需求，核心是先从indeks字段提取年份，再筛选出覆盖所有年份的城市，以下是具体实现步骤和SQL代码：

核心思路 #

1. 从indeks字符串中提取年份，同时过滤掉city为null的无效记录
2. 统计每个城市对应的不同年份数量
3. 计算表中所有存在有效城市记录的年份总数
4. 筛选出城市的年份覆盖数等于总年份数的结果

通用SQL实现（以MySQL为例） #

假设表名为student_records，代码如下：

SELECT city
FROM (
    -- 提取年份并过滤空城市
    SELECT 
        city,
        SUBSTRING_INDEX(indeks, '/', -1) AS year
    FROM student_records
    WHERE city IS NOT NULL
) AS city_year_map
GROUP BY city
-- 筛选覆盖所有年份的城市
HAVING COUNT(DISTINCT year) = (
    -- 计算总共有多少个不同的年份
    SELECT COUNT(DISTINCT SUBSTRING_INDEX(indeks, '/', -1))
    FROM student_records
    WHERE city IS NOT NULL
);

不同数据库的年份提取适配 #

如果使用其他数据库，只需替换年份提取的函数：

• PostgreSQL：用SPLIT_PART(indeks, '/', 2)替代SUBSTRING_INDEX(indeks, '/', -1)
• SQL Server：若年份固定为4位，用RIGHT(indeks, 4)；或用SUBSTRING(indeks, CHARINDEX('/', indeks) + 1, 4)
• Oracle：用SUBSTR(indeks, INSTR(indeks, '/') + 1)提取年份

结果说明 #

针对你提供的示例数据：

• 总共有2018、2019、2020三个有效年份
• London覆盖了全部3个年份，因此会被输出
• Paris仅覆盖2018年，null城市被过滤，均不会出现在结果中

内容的提问来源于stack exchange，提问作者Luka Ristovic