MySQL单行当WHERE IN查询问题：跨连接生成三元组后获取用户详情

我来帮你搞定这个问题！你说通过cross join生成了包含三元组的单行数据，现在要根据这些ID获取用户详情，因为MySQL没有rotate功能不知道怎么操作对吧？其实不用纠结rotate，咱们换个思路——把单行里的多个ID转换成多行数据，就能正常关联用户表或者用WHERE IN查询了，分两种常见场景给你具体方案：

场景1：单行数据包含多个独立的ID字段 #

如果你的三元组是存在三个独立列里的（比如表triplets有a_id、b_id、c_id三个列，每个列对应一个用户ID），直接用UNION ALL把这三个列转成单行的ID列表，再关联用户表就行：

-- 获取所有三元组ID对应的用户详情，自动去重
SELECT DISTINCT u.*
FROM (
    -- 把三个列的ID转成单行集合
    SELECT a_id AS user_id FROM triplets
    UNION ALL
    SELECT b_id AS user_id FROM triplets
    UNION ALL
    SELECT c_id AS user_id FROM triplets
) AS id_list
-- 关联用户表获取详情
JOIN users u ON u.id = id_list.user_id;

场景2：单行数据是逗号分隔的ID字符串 #

如果你的三元组是存在一个逗号分隔的字段里（比如表triplets有user_ids列，值类似"101,102,103"），需要先把字符串拆分成多行ID，再关联用户表：

方案A：MySQL 8.0+ 使用递归CTE拆分 #

WITH RECURSIVE split_ids AS (
    -- 初始化：取出第一个ID和剩余的字符串
    SELECT 
        SUBSTRING_INDEX(user_ids, ',', 1) AS user_id,
        SUBSTRING(user_ids FROM LENGTH(SUBSTRING_INDEX(user_ids, ',', 1)) + 2) AS remaining_ids
    FROM triplets
    WHERE user_ids IS NOT NULL AND user_ids != ''
    UNION ALL
    -- 递归拆分剩余的字符串
    SELECT 
        SUBSTRING_INDEX(remaining_ids, ',', 1) AS user_id,
        SUBSTRING(remaining_ids FROM LENGTH(SUBSTRING_INDEX(remaining_ids, ',', 1)) + 2) AS remaining_ids
    FROM split_ids
    WHERE remaining_ids IS NOT NULL AND remaining_ids != ''
)
-- 关联用户表获取详情
SELECT DISTINCT u.*
FROM split_ids
JOIN users u ON u.id = split_ids.user_id;

方案B：MySQL 5.x 用数字辅助表拆分 #

如果你的MySQL版本不支持CTE，可以先创建一个数字辅助表（比如numbers，包含num列，值为1、2、3…，数量要大于你最多的ID个数）：

-- 先创建数字表（如果没有的话）
CREATE TABLE numbers (num INT PRIMARY KEY);
INSERT INTO numbers VALUES (1), (2), (3), (4), (5); -- 按需添加更多数字

-- 拆分字符串并关联用户表
SELECT DISTINCT u.*
FROM triplets t
JOIN numbers n 
    ON n.num <= LENGTH(t.user_ids) - LENGTH(REPLACE(t.user_ids, ',', '')) + 1
JOIN users u 
    ON u.id = SUBSTRING_INDEX(SUBSTRING_INDEX(t.user_ids, ',', n.num), ',', -1);

关键思路总结 #

不管哪种场景，核心都是把单行中的多个ID转换成多行的单个ID集合，这样就能像普通的ID列表一样，通过关联或者WHERE IN（比如WHERE u.id IN (SELECT user_id FROM id_list)）来获取用户详情了，完全不需要依赖rotate功能~

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