如何合并两个SQL查询并在PHP foreach循环中展示夫妻收入表格

问题背景 #

需在PHP的foreach循环中，将夫妻两人的收入数据按**收入类型（row_type_id）**展示在HTML表格：每人各占一列，每行对应一类收入；无对应收入的列显示为空，所有行按row_type_id排序。

关联逻辑：通过j_b_controller表的joint_budget_id关联夫妻双方，position_order字段区分两人（1代表Person1，2代表Person2）；收入数据存储在incomes表，每种收入对应唯一row_type_id。

现有问题：之前的子查询方式仅能返回Person2的一行数据并重复显示，无法匹配对应收入类型展示。

正确的SQL查询方案 #

使用LEFT JOIN+UNION ALL的组合（兼容多数数据库）关联两人的收入数据，确保获取所有收入类型，同时匹配双方对应的数据：

$stmt = $pdo->prepare('
    SELECT 
        row_type_id,
        MAX(name) AS name,
        SUM(CASE WHEN position_order = 1 THEN amount ELSE 0 END) AS p1_amount,
        SUM(CASE WHEN position_order = 2 THEN amount ELSE 0 END) AS p2_amount
    FROM (
        SELECT i.name, i.amount, i.row_type_id, jb.position_order
        FROM incomes i
        JOIN j_b_controller jb ON i.account_id = jb.account_id
        WHERE jb.joint_budget_id = ? AND jb.position_order IN (1,2)
    ) combined
    GROUP BY row_type_id
    ORDER BY row_type_id ASC
');
$stmt->execute([$_SESSION['budget_id']]);
$income_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

关键说明 #

• 先通过子查询合并两人的所有收入数据，再用GROUP BY row_type_id聚合，通过CASE WHEN分别提取双方的金额。
• 这种写法兼容MySQL等不支持FULL OUTER JOIN的数据库，确保不会遗漏任何一方的收入类型。

修改后的PHP表格循环代码 #

调整foreach逻辑，正确处理空值和金额显示：

<tbody>
    <?php if (empty($income_rows)) : ?>
        <tr>
            <td colspan="6" style="text-align:center;">暂无收入数据</td>
        </tr>
    <?php else : ?>
        <?php foreach ($income_rows as $income) : ?>
            <tr role="row" class="item" data-modal=".incomeInfo<?= md5($income['row_type_id']) ?>">
                <td role="gridcell" class="table-edit-link"></td>
                <td role="gridcell" class="table-view-link">
                    <a href="#"><?= htmlspecialchars($income['name']) ?></a>
                </td>
                <!-- Person1 金额列 -->
                <td role="gridcell">
                    <?php if ($income['p1_amount'] > 0) : ?>
                        <?= number_format($income['p1_amount'], $decimals, ',', '.'); ?>
                    <?php endif; ?>
                </td>
                <!-- Person2 金额列 -->
                <td role="gridcell">
                    <?php if ($income['p2_amount'] > 0) : ?>
                        <?= number_format($income['p2_amount'], $decimals, ',', '.'); ?>
                    <?php endif; ?>
                </td>
                <!-- 月度合计 -->
                <td role="gridcell">
                    <?= number_format(
                        $income['p1_amount'] + $income['p2_amount'],
                        $decimals, ',', '.'
                    ) ?>
                </td>
                <!-- 年度合计 -->
                <td role="gridcell">
                    <?= number_format(
                        ($income['p1_amount'] + $income['p2_amount']) * 12,
                        $decimals, ',', '.'
                    ) ?>
                </td>
            </tr>
        <?php endforeach; ?>
    <?php endif; ?>
</tbody>

关键优化 #

• 用htmlspecialchars转义收入名称，避免XSS风险。
• 简化空值判断逻辑，仅当金额大于0时显示数值，无数据时单元格自动留空。
• 聚合后的金额默认为0，无需额外处理空值计算问题。

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