SQL表字段匹配过滤需求：提取指定案件的特定警官字段值

核心需求拆解 #

• 筛选条件：incidents表中case_number等于'20260318010000cdts'的记录
• 返回字段：字段名包含offic的所有字段，加上officer1、officer2、investigative_officer1、investigative_officer2

不同数据库的实现方式 #

MySQL/MariaDB

先查询符合要求的字段列表，再拼接执行查询语句：

-- 第一步：获取目标字段名
SELECT GROUP_CONCAT(DISTINCT COLUMN_NAME SEPARATOR ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名'
  AND TABLE_NAME = 'incidents'
  AND (COLUMN_NAME LIKE '%offic%'
       OR COLUMN_NAME IN ('officer1', 'officer2', 'investigative_officer1', 'investigative_officer2'));

-- 第二步：用返回的字段名拼接查询语句，示例：
SELECT 字段1, 字段2, officer1, officer2, investigative_officer1, investigative_officer2
FROM incidents
WHERE case_number = '20260318010000cdts';

PostgreSQL

-- 第一步：获取目标字段名
SELECT string_agg(DISTINCT column_name, ', ')
FROM information_schema.columns
WHERE table_schema = 'public' -- 替换为你的实际schema
  AND table_name = 'incidents'
  AND (column_name LIKE '%offic%'
       OR column_name IN ('officer1', 'officer2', 'investigative_officer1', 'investigative_officer2'));

-- 第二步：拼接查询语句执行
SELECT 字段1, 字段2, officer1, officer2, investigative_officer1, investigative_officer2
FROM incidents
WHERE case_number = '20260318010000cdts';

SQL Server

-- 第一步：获取目标字段名
SELECT STRING_AGG(DISTINCT COLUMN_NAME, ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = '你的数据库名'
  AND TABLE_NAME = 'incidents'
  AND (COLUMN_NAME LIKE '%offic%'
       OR COLUMN_NAME IN ('officer1', 'officer2', 'investigative_officer1', 'investigative_officer2'));

-- 第二步：拼接查询语句执行
SELECT 字段1, 字段2, officer1, officer2, investigative_officer1, investigative_officer2
FROM incidents
WHERE case_number = '20260318010000cdts';

注意事项 #

• 替换代码中的数据库名、schema为实际环境对应的值
• 若需一次性执行可使用动态SQL，但要注意防范SQL注入风险，确保参数安全

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