如何从UNION之外的另一个表中获得列值？

Question

在我的SQL中，我得到与用户和业务相关的事务。然而，我也需要知道公司的名称。它可以在表业务下的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

在我的第二次代码尝试中，它确实获得了业务名称，但是，在每一行中进行选择是不有效的，因为每个行的业务名称都是相同的。我怎样才能做到一次，并应用到每一行？也许在联盟之外的某个地方？但是，下面是我的工作代码，我想优化它，这样它就不会从每一行的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

Answer 2

正如JOIN建议的那样，使用巴玛是一种完全可以接受的方法，也是我最有可能这样做的方式。

但是，您可以使用用户定义变量并将该附加选择替换为该选项。

 SELECT business_name FROM Businesses WHERE business_id=1 LIMIT 1 INTO @bname;

(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, @bname AS business_name
  FROM BusinessAccountActions
  WHERE action_user_id=191 AND action_business_id=1)
  ORDER BY date DESC
  LIMIT 100