MySQL多条件下实现行转列查询的问题（Laravel适配）

方法一：聚合函数 + CASE 语句（推荐） #

这是MySQL中行转列的高效方案，通过分组用户ID，结合CASE判断每个慈善机构的捐赠金额，再用聚合函数提取对应列的值，无数据时自动返回NULL，不会丢失仅部分捐赠的用户数据。

原生SQL语句 #

SELECT
    user_uid,
    MAX(CASE WHEN charity_id = 178 THEN value END) AS charity_178,
    MAX(CASE WHEN charity_id = 25 THEN value END) AS charity_25,
    MAX(CASE WHEN charity_id = 209 THEN value END) AS charity_209,
    MAX(CASE WHEN charity_id = 150 THEN value END) AS charity_150,
    MAX(CASE WHEN charity_id = 176 THEN value END) AS charity_176
FROM donations
WHERE year = 2022
  AND charity_id IN (178, 25, 209, 150, 176)
GROUP BY user_uid
ORDER BY user_uid; -- 可按需替换为其他排序字段

补充说明

• 若用户对同一慈善机构有多笔捐赠，将MAX替换为SUM可得到该用户对该机构的总捐赠额；若仅需单笔（如最大金额），保留MAX即可。
• 该语句会返回所有2022年对指定慈善机构有过至少一笔捐赠的用户，未捐赠的对应列显示NULL。

方法二：左连接替代内连接 #

如果坚持用JOIN写法，需将所有内连接（JOIN）改为左连接（LEFT JOIN），并把筛选条件移至ON子句而非WHERE子句，以此保留仅部分捐赠的用户。

原生SQL语句 #

SELECT
    fun.uid,
    d1.value AS charity_178,
    d2.value AS charity_209,
    d3.value AS charity_25,
    d4.value AS charity_150,
    d5.value AS charity_176
FROM fundraisers AS fun
LEFT JOIN donations AS d1 
    ON fun.uid = d1.user_uid 
    AND d1.year = 2022 
    AND d1.charity_id = 178
LEFT JOIN donations AS d2 
    ON fun.uid = d2.user_uid 
    AND d2.year = 2022 
    AND d2.charity_id = 209
LEFT JOIN donations AS d3 
    ON fun.uid = d3.user_uid 
    AND d3.year = 2022 
    AND d3.charity_id = 25
LEFT JOIN donations AS d4 
    ON fun.uid = d4.user_uid 
    AND d4.year = 2022 
    AND d4.charity_id = 150
LEFT JOIN donations AS d5 
    ON fun.uid = d5.user_uid 
    AND d5.year = 2022 
    AND d5.charity_id = 176
-- 如需过滤至少有一笔捐赠的用户，添加以下条件
WHERE d1.value IS NOT NULL 
   OR d2.value IS NOT NULL 
   OR d3.value IS NOT NULL 
   OR d4.value IS NOT NULL 
   OR d5.value IS NOT NULL;

补充说明

• 左连接会保留fundraisers表中所有用户，即使无任何捐赠记录；若仅需有捐赠的用户，加上最后的WHERE条件即可。
• 该写法在慈善机构数量较多时会更冗长，性能不如方法一，因此更推荐前者。

Laravel中执行原生SQL #

在Laravel项目中，可通过DB门面直接执行上述原生SQL：

执行方法一的示例代码 #

use Illuminate\Support\Facades\DB;

$results = DB::select("
    SELECT
        user_uid,
        MAX(CASE WHEN charity_id = 178 THEN value END) AS charity_178,
        MAX(CASE WHEN charity_id = 25 THEN value END) AS charity_25,
        MAX(CASE WHEN charity_id = 209 THEN value END) AS charity_209,
        MAX(CASE WHEN charity_id = 150 THEN value END) AS charity_150,
        MAX(CASE WHEN charity_id = 176 THEN value END) AS charity_176
    FROM donations
    WHERE year = 2022
      AND charity_id IN (178, 25, 209, 150, 176)
    GROUP BY user_uid
");

执行方法二的示例代码 #

use Illuminate\Support\Facades\DB;

$results = DB::select("
    SELECT
        fun.uid,
        d1.value AS charity_178,
        d2.value AS charity_209,
        d3.value AS charity_25,
        d4.value AS charity_150,
        d5.value AS charity_176
    FROM fundraisers AS fun
    LEFT JOIN donations AS d1 
        ON fun.uid = d1.user_uid 
        AND d1.year = 2022 
        AND d1.charity_id = 178
    LEFT JOIN donations AS d2 
        ON fun.uid = d2.user_uid 
        AND d2.year = 2022 
        AND d2.charity_id = 209
    LEFT JOIN donations AS d3 
        ON fun.uid = d3.user_uid 
        AND d3.year = 2022 
        AND d3.charity_id = 25
    LEFT JOIN donations AS d4 
        ON fun.uid = d4.user_uid 
        AND d4.year = 2022 
        AND d4.charity_id = 150
    LEFT JOIN donations AS d5 
        ON fun.uid = d5.user_uid 
        AND d5.year = 2022 
        AND d5.charity_id = 176
    WHERE d1.value IS NOT NULL 
       OR d2.value IS NOT NULL 
       OR d3.value IS NOT NULL 
       OR d4.value IS NOT NULL 
       OR d5.value IS NOT NULL
");

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