非规范化多对多表SQL查询：筛选符合特定属性条件的用户

嘿，这就帮你搞定这个SQL筛选需求！咱们先把需求拆解清楚：要从那张多对多的非规范化表里，先排除掉三类属性（以0开头、以1开头、包含'-'的），最后只留下那些只有1条符合要求的字母数字属性的用户。

直接可用的SQL实现（两种方法） #

方法1：用窗口函数（简洁高效，适合支持窗口函数的数据库）

SELECT t.User, t.Property
FROM (
    SELECT 
        User,
        Property,
        -- 统计每个用户的有效属性总数
        COUNT(*) OVER (PARTITION BY User) AS valid_prop_count
    FROM your_table_name
    -- 排除三类不符合要求的属性
    WHERE Property NOT LIKE '0%'
      AND Property NOT LIKE '1%'
      AND Property NOT LIKE '%-%'
      -- 可选：如果需要严格限制属性仅为字母数字，加上下面这行
      -- AND Property REGEXP '^[A-Za-z0-9]+$'
) t
-- 只保留有效属性数量恰好为1的用户记录
WHERE t.valid_prop_count = 1;

方法2：分组关联（兼容性好，支持所有主流数据库）

SELECT yt.User, yt.Property
FROM your_table_name yt
JOIN (
    -- 先筛选出有效属性数为1的用户
    SELECT User
    FROM your_table_name
    WHERE Property NOT LIKE '0%'
      AND Property NOT LIKE '1%'
      AND Property NOT LIKE '%-%'
      -- 可选：严格限制字母数字的话加这行
      -- AND Property REGEXP '^[A-Za-z0-9]+$'
    GROUP BY User
    HAVING COUNT(*) = 1
) filtered_users ON yt.User = filtered_users.User
-- 再次过滤属性，确保拿到的是有效属性
WHERE yt.Property NOT LIKE '0%'
  AND yt.Property NOT LIKE '1%'
  AND yt.Property NOT LIKE '%-%';

逻辑拆解 #

1. 属性过滤：用三个NOT LIKE条件精准排除掉不需要的属性：

   • NOT LIKE '0%'：排除以0开头的属性
   • NOT LIKE '1%'：排除以1开头的属性
   • NOT LIKE '%-%'：排除包含'-'的属性
   • 如果你需要确保剩下的属性是纯字母数字，可以加上正则判断Property REGEXP '^[A-Za-z0-9]+$'，避免混入其他特殊字符。

2. 用户筛选：

   • 方法1用窗口函数COUNT(*) OVER (PARTITION BY User)，给每条有效属性记录打上该用户的有效属性总数标签，最后筛选总数为1的记录即可，一步到位。
   • 方法2先通过分组统计找到有效属性数为1的用户列表，再回原表关联获取对应的属性，适合不支持窗口函数的老版本数据库。

示例数据运行结果 #

用你给出的示例数据执行后，会得到如下结果：

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