在R（DBI/ODBC）中实现SQL多列多前缀值过滤的方法

方案一：手动扩展WHERE条件（快速直接） #

直接在WHERE子句中为每一列添加相同的LIKE条件，只要任意一列匹配指定前缀就保留记录。注意修正原SQL的语法错误（FROM name.table1 is a改为FROM name.table1 a，移除WHERE末尾多余的括号）：

new_file <- DBI::dbGetQuery(original_file,
"SELECT a.patient_id, a.code_01_cd, a.code_02_cd, a.code_03_cd, a.code_04_cd, a.code_05_cd, 
a.code_06_cd, a.code_07_cd, a.code_08_cd, a.code_09_cd, a.code_10_cd, a.code_11_cd, 
a.code_12_cd, a.code_13_cd, a.code_14_cd, a.code_15_cd, a.code_16_cd, a.code_17_cd, 
a.code_18_cd, a.code_19_cd, a.code_20_cd, a.code_21_cd, a.code_22_cd, a.code_23_cd, 
a.code_24_cd, a.code_25_cd, a.code_01_type_cd, b.patient_id, b.admit_date
FROM name.table1 a
INNER JOIN name.table2 b
ON a.patient_id = b.patient_id
WHERE (
  a.code_01_cd LIKE 'M10%' OR a.code_01_cd LIKE 'N850%' OR a.code_01_cd LIKE 'X500%' OR a.code_01_cd LIKE 'V100%'
  OR a.code_02_cd LIKE 'M10%' OR a.code_02_cd LIKE 'N850%' OR a.code_02_cd LIKE 'X500%' OR a.code_02_cd LIKE 'V100%'
  OR a.code_03_cd LIKE 'M10%' OR a.code_03_cd LIKE 'N850%' OR a.code_03_cd LIKE 'X500%' OR a.code_03_cd LIKE 'V100%'
  -- 依次复制该格式到code_25_cd
  OR a.code_25_cd LIKE 'M10%' OR a.code_25_cd LIKE 'N850%' OR a.code_25_cd LIKE 'X500%' OR a.code_25_cd LIKE 'V100%'
)
AND admit_date >= '2020-01-01'")

方案二：用R动态生成SQL（高效维护） #

借助R的字符串生成能力，自动生成25列的过滤条件，避免手动重复编写：

# 定义需要匹配的前缀
prefixes <- c("'M10%'", "'N850%'", "'X500%'", "'V100%'")
# 生成25个code列的名称
code_cols <- paste0("a.code_", sprintf("%02d", 1:25), "_cd")

# 生成每个列的过滤条件：列 LIKE 前缀1 OR 列 LIKE 前缀2...
col_conditions <- sapply(code_cols, function(col) {
  paste0(col, " LIKE ", paste(prefixes, collapse = " OR "))
})
# 把所有列的条件用OR连接，包裹在括号内
where_clause <- paste("(", paste(col_conditions, collapse = " OR "), ")")

# 拼接完整SQL语句
sql_query <- paste0(
  "SELECT a.patient_id, ", paste(code_cols, collapse = ", "), ", a.code_01_type_cd, b.patient_id, b.admit_date\n",
  "FROM name.table1 a\n",
  "INNER JOIN name.table2 b\n",
  "ON a.patient_id = b.patient_id\n",
  "WHERE ", where_clause, "\n",
  "AND admit_date >= '2020-01-01'"
)

# 执行查询
new_file <- DBI::dbGetQuery(original_file, sql_query)

如果习惯用glue包拼接SQL（代码更易读），可以替换为：

library(glue)

sql_query <- glue("
SELECT a.patient_id, {paste(code_cols, collapse = ', ')}, a.code_01_type_cd, b.patient_id, b.admit_date
FROM name.table1 a
INNER JOIN name.table2 b
ON a.patient_id = b.patient_id
WHERE {where_clause}
AND admit_date >= '2020-01-01'
")

new_file <- DBI::dbGetQuery(original_file, sql_query)

方案三：用UNPIVOT转置列（数据库支持时使用） #

如果你的数据库（比如Denodo）支持UNPIVOT语法，可以将25个code列转成行，过滤后再处理，逻辑更简洁：

new_file <- DBI::dbGetQuery(original_file,
"SELECT *
FROM (
  SELECT a.patient_id, a.code_01_type_cd, b.admit_date,
         code_value, code_col
  FROM name.table1 a
  INNER JOIN name.table2 b ON a.patient_id = b.patient_id
  UNPIVOT (
    code_value FOR code_col IN (
      code_01_cd, code_02_cd, code_03_cd, -- 列出所有25个code列
      code_04_cd, code_05_cd, ..., code_25_cd
    )
  ) AS unpivoted_data
  WHERE admit_date >= '2020-01-01'
) AS filtered_data
WHERE code_value LIKE 'M10%' OR code_value LIKE 'N850%' OR code_value LIKE 'X500%' OR code_value LIKE 'V100%'
")

注：如果需要恢复原列结构，可以在过滤后再用PIVOT转置回去，具体语法参考数据库文档。

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