mysql - SQL - 如果表条目存在则

Question

我想做的是，如果帐户 ID 在“player_shops”中有一个条目，则有一个特定的 where 子句，否则没有 where 子句。如果这是有道理的。这是我的代码:

SELECT *,
       SUM(acc.gold_coins + shop.gold_coins) AS total_gold
  FROM accounts AS acc
     , player_shops AS shop
 WHERE acc.acc_id = shop.acc_id
 GROUP BY acc.acc_id
 ORDER BY total_gold DESC
 LIMIT 100

我希望 WHERE 子句仅在指定帐户 ID 是 player_shop 条目时才存在。

有什么帮助吗？

Answer 1

你最好使用 JOIN。

SELECT *,
   SUM(acc.gold_coins + IF(shop.gold_coins IS NULL, 0, shop.gold_coins)) AS total_gold
FROM accounts AS acc
LEFT JOIN player_shops AS shop
ON acc.acc_id = shop.acc_id
GROUP BY acc.acc_id
ORDER BY total_gold DESC
LIMIT 100

我不确定 IF 语法。这取决于您使用的数据库。