如何在SQL中批量将含DATE列的1900-01-01替换为NULL/NA

首先修正原查询的语法错误（原查询存在多WHERE子句、注释格式错误、排除条件缺失等问题），在此基础上实现批量处理日期列的需求：

基础修正后的查询（无批量处理） #

SELECT DISTINCT
    col1,
    col2,
    col3,
    col4,
    col5,
    col_date_1,
    col_date_2,
    col_date_99      
FROM "DATATABLE"
WHERE col1 = '2020'
    AND col2 IS NOT NULL 
    AND col3 NOT IN ('criteria1', 'criteria2') 
    AND col3 LIKE '0510%' 
    AND col3 != '0510005000' -- 排除指定值
    AND NOT(col4= 'NA' AND col5 IN ('TX', 'XK', 'XT', 'Y1', 'Y2', 'Y3', 'Y4', 'YR', 'ZH', 'ZI', 'ZJ'))

批量处理含DATE列的解决方案 #

由于存在数百个含DATE的列，无法逐个编写CASE语句，需通过动态SQL生成查询逻辑，以下分主流数据库举例：

1. SQL Server #

利用系统视图sys.columns筛选列名含'DATE'的字段，拼接CASE替换逻辑：

DECLARE @sql NVARCHAR(MAX) = ''

-- 拼接非日期列
SELECT @sql += 'col1, col2, col3, col4, col5, '

-- 拼接日期列的替换逻辑
SELECT @sql += 
    'CASE WHEN ' + QUOTENAME(name) + ' = ''1900-01-01'' THEN NULL ELSE ' + QUOTENAME(name) + ' END AS ' + QUOTENAME(name) + ', '
FROM sys.columns
WHERE object_id = OBJECT_ID('DATATABLE')
AND name LIKE '%DATE%'

-- 去除末尾多余的逗号，拼接FROM和WHERE子句
SET @sql = LEFT(@sql, LEN(@sql)-1) + ' FROM "DATATABLE" WHERE col1 = ''2020''
    AND col2 IS NOT NULL 
    AND col3 NOT IN (''criteria1'', ''criteria2'') 
    AND col3 LIKE ''0510%'' 
    AND col3 != ''0510005000''
    AND NOT(col4= ''NA'' AND col5 IN (''TX'', ''XK'', ''XT'', ''Y1'', ''Y2'', ''Y3'', ''Y4'', ''YR'', ''ZH'', ''ZI'', ''ZJ''))'

-- 执行动态SQL
EXEC sp_executesql @sql

2. PostgreSQL #

使用information_schema.columns生成动态查询：

DO $$
DECLARE
    sql_str TEXT := '';
BEGIN
    -- 拼接非日期列
    sql_str := 'SELECT DISTINCT col1, col2, col3, col4, col5, ';

    -- 拼接日期列的替换逻辑
    SELECT string_agg(
        'CASE WHEN ' || quote_ident(column_name) || ' = ''1900-01-01''::DATE THEN NULL ELSE ' || quote_ident(column_name) || ' END AS ' || quote_ident(column_name),
        ', '
    ) INTO sql_str
    FROM information_schema.columns
    WHERE table_name = 'DATATABLE'
    AND column_name LIKE '%DATE%';

    -- 拼接FROM和WHERE子句
    sql_str := sql_str || ' FROM "DATATABLE" WHERE col1 = ''2020''
        AND col2 IS NOT NULL 
        AND col3 NOT IN (''criteria1'', ''criteria2'') 
        AND col3 LIKE ''0510%'' 
        AND col3 != ''0510005000''
        AND NOT(col4= ''NA'' AND col5 IN (''TX'', ''XK'', ''XT'', ''Y1'', ''Y2'', ''Y3'', ''Y4'', ''YR'', ''ZH'', ''ZI'', ''ZJ''))';

    -- 执行动态SQL
    EXECUTE sql_str;
END $$;

3. MySQL #

通过information_schema.columns生成动态语句：

SET @sql = '';

-- 拼接非日期列
SET @sql = CONCAT(@sql, 'SELECT DISTINCT col1, col2, col3, col4, col5, ');

-- 拼接日期列的替换逻辑
SELECT GROUP_CONCAT(
    CONCAT('CASE WHEN `', column_name, '` = ''1900-01-01'' THEN NULL ELSE `', column_name, '` END AS `', column_name, '`')
    SEPARATOR ', '
) INTO @part_sql
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name = 'DATATABLE'
AND column_name LIKE '%DATE%';

SET @sql = CONCAT(@sql, @part_sql, ' FROM `DATATABLE` WHERE col1 = ''2020''
    AND col2 IS NOT NULL 
    AND col3 NOT IN (''criteria1'', ''criteria2'') 
    AND col3 LIKE ''0510%'' 
    AND col3 != ''0510005000''
    AND NOT(col4= ''NA'' AND col5 IN (''TX'', ''XK'', ''XT'', ''Y1'', ''Y2'', ''Y3'', ''Y4'', ''YR'', ''ZH'', ''ZI'', ''ZJ''))');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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