如何在PostgreSQL里判断字段包含集合中的哪些值

文
章
目
录

使用PostgreSQL数据库时，咱们可能会遇到这样一个需求：判断一个字段里包含某个集合中的哪些字段，然后把包含的字段输出。今天就来给大家分享一下具体的实现方法。

一、用LIKE操作符和CASE语句实现

假设我们有一个集合ARRAY['apple', 'banana', 'cherry'] ，现在要检查数据表my_table里的my_column字段，看看它是否包含集合里的某个值。这时候，可以用SQL的LIKE操作符和CASE语句来实现。先来看示例查询：

SELECT
    my_column,
    CASE
        WHEN my_column LIKE '%apple%' THEN 'apple'
        WHEN my_column LIKE '%banana%' THEN 'banana'
        WHEN my_column LIKE '%cherry%' THEN 'cherry'
        ELSE 'none'
    END AS matched_value
FROM
    my_table;

下面给大家详细解释一下这段代码：

CASE语句在这里起到了关键作用，它就像是一个“判断大师”，专门用来判断my_column字段里有没有集合中的值。
LIKE '%apple%' ，这部分是检查my_column字段里是否包含字符串'apple' 。LIKE是PostgreSQL里用来进行字符串匹配的操作符，%是通配符，表示任意长度的字符串。所以'%apple%'的意思就是只要my_column字段里有apple这个字符串，不管它前面或后面还有什么其他内容，都算匹配成功。
后面类似地，又检查了是否包含'banana'和'cherry' ，方法和检查'apple'是一样的。
如果my_column字段里没有和集合里任何一个值匹配的内容，那就返回'none' 。

二、集合值较多时的优化方法

要是集合里的值特别多，还用上面那种手动写CASE语句的方法，就会很麻烦。这时候，我们可以考虑用一个临时表或者子查询来存储集合里的值，然后再进行匹配。下面是一个更通用的实现方法：

WITH search_terms AS (
    SELECT term
    FROM UNNEST(ARRAY['apple', 'banana', 'cherry']) AS term
)
SELECT
    my_column,
    COALESCE((SELECT term FROM search_terms WHERE my_column LIKE '%' || term || '%' LIMIT 1), 'none') AS matched_value
FROM
    my_table;

这段代码看起来稍微复杂一点，不过别担心，我来给大家拆解一下：

search_terms是一个公共表表达式（CTE），简单理解就是一个临时的“数据小仓库”，它用来存储我们要查找的集合值。这里通过UNNEST函数把数组ARRAY['apple', 'banana', 'cherry']里的值一个个取出来，存到term这个字段里。
内部的SELECT查询，会在search_terms这个“小仓库”里查找my_column字段中第一个匹配的集合值。my_column LIKE '%' || term || '%'这部分和前面的LIKE用法类似，不过这里是从search_terms里取出每个值，去和my_column进行匹配。||在SQL里是字符串连接符，把前后的字符串连起来。LIMIT 1表示只要找到第一个匹配的值就可以了，不用继续找下去。
COALESCE函数的作用是处理没有匹配的情况。如果在search_terms里没有找到和my_column匹配的值，COALESCE就会返回'none' 。