选择含指定特殊值的表格行的Excel函数与SQL查询方法

Excel 实现 #

分两种常见数据存储场景：

场景1：关联数值分散在多列存储 #

假设A列为Id，B列及之后的列分别存储各个关联数值，表头在第1行，数据从第2行开始，要筛选同时包含2和5的行：

1. 新增空白辅助列，在第2行输入公式：
   =AND(COUNTIF(B2:Z2,2)>0, COUNTIF(B2:Z2,5)>0)
2. 下拉填充公式到所有数据行，公式返回TRUE的就是同时包含两个目标值的行
3. 筛选辅助列值为TRUE的行即可得到结果
   如果目标值较多，可以把目标值存在单独的单元格区域，比如H1:H2存2、5，公式可以简化为：
   =SUMPRODUCT(--(COUNTIF(B2:Z2,$H$1:$H$2)>0))=COUNTA($H$1:$H$2)

场景2：关联数值以分隔符（比如|）拼接存在同一列 #

假设B列存储所有关联数值，用|作为分隔符，辅助列公式为：
=AND(ISNUMBER(SEARCH("|2|", "|"&B2&"|")), ISNUMBER(SEARCH("|5|", "|"&B2&"|")))
拼接前后的|是为了避免把12、25这类包含目标数字的数值被误匹配。

SQL 实现 #

同样分三种存储场景：

场景1：关联数值分散在多列（宽表存储） #

假设表名为id_data，关联数值列名为val1、val2、val3、val4，查询语句：

SELECT *
FROM id_data
WHERE 2 IN (val1, val2, val3, val4)
  AND 5 IN (val1, val2, val3, val4);

场景2：关联数值拼接存储在同一列 #

假设关联数值列名为vals，用|分隔：

SELECT *
FROM id_data
WHERE CONCAT('|', vals, '|') LIKE '%|2|%'
  AND CONCAT('|', vals, '|') LIKE '%|5|%';

场景3：关联数值行式存储（一行存一个Id+一个对应值，该结构扩展性更强） #

假设表名为id_correspond，字段为id、correspond_val：

SELECT id
FROM id_correspond
WHERE correspond_val IN (2,5)
GROUP BY id
HAVING COUNT(DISTINCT correspond_val) = 2;

这里HAVING后的数字等于你要匹配的目标值的数量，需要查询更多目标值时，只需要修改IN中的枚举值和HAVING后的数字即可。如果需要返回完整的关联数值列表，可以根据你使用的数据库用GROUP_CONCAT（MySQL）、STRING_AGG（PostgreSQL/SQL Server）等函数拼接。

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