mysql - 如何从 UNION ALL 之外的另一个表获取列值？

Question

在我的 SQL 中，我正在获取与用户和企业相关的事务。但是，我还需要获取该公司的名称。它位于表 Businesses 下的business_name 列中。在我的示例 SQL 中，我希望获取business_id=1 的企业名称。我当前的代码除了无法获取公司名称之外还可以工作。

(SELECT TRUNCATE(code_reward_amount, 2) AS amount, UNIX_TIMESTAMP(code_redeemed_date) AS date, 0 AS action_number
FROM CodesRedeemed
WHERE code_redeemed_by_user_id=191 AND code_business_id=1)
UNION ALL
(SELECT TRUNCATE(action_amount, 2) AS amount, UNIX_TIMESTAMP(action_date) AS date, action_number
FROM BusinessAccountActions
WHERE action_user_id=191 AND action_business_id=1)
ORDER BY date DESC
LIMIT 100

在我的第二次代码尝试中，它确实获取了企业名称，但是，在每一行中进行选择效率不高，因为每一行的企业名称都相同。我怎样才能做到一次并将其应用到每一行？也许是 UNION ALL 之外的某个地方？这是我的工作代码，但是，我想对其进行优化，以便它不会从企业中选择每一行中的business_name(因为保证所有行的business_name 相同，因为它们共享相同的business_id)。

(SELECT TRUNCATE(code_reward_amount, 2) AS amount, UNIX_TIMESTAMP(code_redeemed_date) AS date, 0 AS action_number, (SELECT business_name FROM Businesses WHERE business_id=1) AS business_name
FROM CodesRedeemed
WHERE code_redeemed_by_user_id=191 AND code_business_id=1)
UNION ALL
(SELECT TRUNCATE(action_amount, 2) AS amount, UNIX_TIMESTAMP(action_date) AS date, action_number, (SELECT business_name FROM Businesses WHERE business_id=1) AS business_name
FROM BusinessAccountActions
WHERE action_user_id=191 AND action_business_id=1)
ORDER BY date DESC
LIMIT 100

business_id 会根据业务而变化。我现在正在测试business_id 1。我将如何优化(主要是不检查每一行中的business_name)？谢谢。

Answer 1

使用JOIN。

SELECT u.amount, u.date, b.business_name, u.action_number
FROM (
    (SELECT TRUNCATE(code_reward_amount, 2) AS amount, UNIX_TIMESTAMP(code_redeemed_date) AS date, 0 AS action_number
    FROM CodesRedeemed
    WHERE code_redeemed_by_user_id=191 AND code_business_id=1)
    UNION ALL
    (SELECT TRUNCATE(action_amount, 2) AS amount, UNIX_TIMESTAMP(action_date) AS date, action_number
    FROM BusinessAccountActions
    WHERE action_user_id=191 AND action_business_id=1)
    ORDER BY date DESC
    LIMIT 100) AS u
CROSS JOIN Businesses AS b
WHERE b.business_id = 1