如何编写SQL查询统计记录间相同属性列的数量并排序？

这个需求挺常见的，不用非得手动挨个写列的判断——不过具体实现得看你用的数据库类型，我分几种情况给你讲：

通用方案（所有数据库适用） #

不管你用什么SQL数据库，这种逐列判断相加的方法肯定能跑通，逻辑也最直观。假设你的表叫your_table，列是col1到col5，指定的目标记录是[1, 0, 1, 0, 0]，那么可以这么写：

SELECT
    *,
    -- 每一列和目标值对比，相同加1，最后求和得到匹配数
    (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) +
    (CASE WHEN col2 = 0 THEN 1 ELSE 0 END) +
    (CASE WHEN col3 = 1 THEN 1 ELSE 0 END) +
    (CASE WHEN col4 = 0 THEN 1 ELSE 0 END) +
    (CASE WHEN col5 = 0 THEN 1 ELSE 0 END) AS match_count
FROM your_table
ORDER BY match_count DESC;

这个方法的好处是兼容性拉满，不管是MySQL、PostgreSQL还是SQL Server都能用，缺点是如果列特别多的话，写起来有点繁琐。

数据库特定简化方案（减少重复代码） #

如果你的列数非常多，不想挨个写CASE，可以用对应数据库的数组/JSON函数来简化：

PostgreSQL #

PostgreSQL对数组支持很好，可以把行转成数组，再和目标数组逐元素对比计数：

-- 先定义目标数组
WITH target_record AS (
    SELECT ARRAY[1, 0, 1, 0, 0] AS target_arr
)
SELECT
    t.*,
    -- 展开两个数组，统计元素相等的数量
    (SELECT COUNT(*)
     FROM unnest(t.row_arr, tr.target_arr) AS vals(table_val, target_val)
     WHERE table_val = target_val) AS match_count
FROM (
    -- 把当前行的列转成数组（注意列顺序要和目标数组完全一致）
    SELECT ARRAY[col1, col2, col3, col4, col5] AS row_arr, *
    FROM your_table
) t
CROSS JOIN target_record tr
ORDER BY match_count DESC;

MySQL #

MySQL可以用JSON数组来实现类似的逻辑：

-- 定义目标JSON数组
SET @target = '[1, 0, 1, 0, 0]';

SELECT
    t.*,
    -- 展开行数组和目标数组，按位置匹配并计数
    (SELECT COUNT(*)
     FROM JSON_TABLE(t.row_json, '$[*]' COLUMNS(val INT PATH '$', idx INT PATH '$' FOR ORDINALITY)) AS row_vals
     JOIN JSON_TABLE(@target, '$[*]' COLUMNS(val INT PATH '$', idx INT PATH '$' FOR ORDINALITY)) AS target_vals
     ON row_vals.val = target_vals.val AND row_vals.idx = target_vals.idx) AS match_count
FROM (
    -- 把行转成JSON数组（列顺序要和目标一致）
    SELECT JSON_ARRAY(col1, col2, col3, col4, col5) AS row_json, *
    FROM your_table
) t
ORDER BY match_count DESC;

SQL Server #

SQL Server可以用OPENJSON来处理数组对比：

-- 定义目标JSON数组
DECLARE @target NVARCHAR(MAX) = '[1,0,1,0,0]';

SELECT
    t.*,
    -- 按数组下标匹配并统计相等的元素数
    (SELECT COUNT(*)
     FROM OPENJSON(t.row_json) AS row_vals
     JOIN OPENJSON(@target) AS target_vals
     ON row_vals.value = target_vals.value AND row_vals.[key] = target_vals.[key]) AS match_count
FROM (
    -- 将行转成JSON（列顺序要和目标一致）
    SELECT (SELECT col1, col2, col3, col4, col5 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS row_json, *
    FROM your_table
) t
ORDER BY match_count DESC;

注意事项 #

不管用哪种方法，列的顺序必须和目标记录的顺序完全一致，否则会出现匹配错误。如果列数不多，通用的CASE方法其实更易读、不容易出错；列数多的话，用数据库特定的数组/JSON方法能节省大量重复代码。

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