mysql - 具有相关表的复杂查询 - 最佳解决方案

Question

架构:

我有 3 个表:

• 用户
• 特色
• User_has_Feature:

最初所有用户都没有特征

示例数据:

用户:

| id | name |
| 1  | Rex  |
| 2  | Job  |

特点:

| id | name |
| 1  | Eat  |
| 2  | Walk |

用户有特征:

| id | user_id | feature_id | have_feature |
| 1  | 1       | 1          | true         |
| 2  | 1       | 1          | true         |
| 3  | 2       | 2          | true         |
| 4  | 2       | 2          | false        |

问题是:

• ¿如何只获取具有所有特征的记录？ (明确地)

例子:

| user_name | feature_name | feature_status |
| Rex       | Eat          | true           |
| Rex       | Walk         | true           |

• 如何获取不具备所有特征的记录？ (再次明确)

例子:

| user_name | feature_name | feature_status |
| Job       | Eat          | true           |
| Job       | Walk         | false          |

必须满足一些条件

• 我需要用户列表，其中包含两个查询中的所有特征(真或假)如示例
• 用户有 65 万条记录(目前)
• 特征有 45 条记录(目前)
• 是一次性查询。

思路是将结果导出为CSV文件

---

早期解决方案

感谢(@RolandoMySQLDBA、@Tom Ingram、@DRapp)的回答，我找到了解决方案:

SELECT u.name, f.name, IF(uhf.status=1,'YES','NO') as status
FROM user u
  JOIN user_has_feature uhf ON u.id = uhf.user_id
  JOIN feature f ON f.id = uhf.feature_id
  JOIN 
       (
         SELECT u.id as id
         FROM user u
           JOIN user_has_feature uhf ON uhf.user_id = u.id
         WHERE uhf.status = 1
         GROUP BY u.id
         HAVING count(u.id) <= (SELECT COUNT(1) FROM feature)
       ) as `condition` ON `condition`.id = u.id
ORDER BY u.name, f.id, uhf.status

获取不具有所有特征的记录和获取所有具有所有特征的记录:

• WHERE uhf.status = 1通过 WHERE uhf.status = 2
• HAVING count(u.id) <= (SELECT COUNT(1) FROM feature)通过 HAVING count(u.id) = (SELECT COUNT(1) FROM feature)

但我想知道这是否是一个最佳解决方案？

Answer 1

SELECT
    UNF.*,
    IF(
        (LENGTH(UNF.FeatureList) - LENGTH(REPLACE(UNF.FeatureList,',','')))
        = (FC.FeatureCount - 1),'Has All Features','Does Not Have All Features'
    ) HasAllFeatures
FROM
    (SELECT
        U.name user_name
        GROUP_CONCAT(F.name) Features
    FROM
        (SELECT user_id,feature_id FROM User_has_Feature
        WHERE feature_status = true) UHF
        INNER JOIN User U ON UHF.user_id = U.id
        INNER JOIN Feature F ON UHF.feature_id = F.id
    GROUP BY
       U.name
    ) UNF,
    (SELECT COUNT(1) FeatureCount FROM Feature) FC
;

UNF 子查询返回 User_has_Feature 中列出的所有用户和以逗号分隔的特征列表。 HasAllFeatures 列由 UNF.FeatureList 中的列数决定。在您的情况下，有两个功能。如果 UNF.FeatureList 中的逗号数为 FeatureCount - 1，则用户拥有所有特征。否则，用户不具备所有功能。

这是一个更好的版本，可以显示所有用户以及他们是否拥有所有、部分或没有功能

SELECT
    U.name user_name,
    IFNULL(UsersAndFeatures.HasAllFeatures,
    'Does Not Have Any Features')
    WhatFeaturesDoesThisUserHave
FROM
    User U LEFT JOIN
    (
        SELECT
            UHF.user_id id,
            IF(
                (LENGTH(UHF.FeatureList) - LENGTH(REPLACE(UHF.FeatureList,',','')))
                = (FC.FeatureCount - 1),
               'Has All Features',
               'Does Not Have All Features'
            ) HasAllFeatures
        FROM
            (
                SELECT user_id,GROUP_CONCAT(Feature.name) FeatureList
                FROM User_has_Feature INNER JOIN Feature
                ON User_has_Feature.feature_id = Feature.id
                GROUP BY user_id
            ) UHF,
            (SELECT COUNT(1) FeatureCount FROM Feature) FC
    ) UsersAndFeatures
USING (id);