SQL - 如果存在表条目

What I am wanting to do is if there is an entry in "player_shops" for the account ID then have a specific where clause, otherwise have no where clause. If that makes sense. Here is my code:

我想要做的是,如果帐户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

I want that WHERE clause to only be there IF the is a player_shop entry for the specified account ID.

我希望WHERE子句只在那里,如果它是指定帐户ID的player_shop条目。

Any help?

4 个解决方案

#1

You better use JOIN.

你最好使用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

I'm not sure about IF syntax. It depends on what db you use.

我不确定IF语法。这取决于你使用的db。

#2

If you are using MS SQL try this:

如果您使用的是MS SQL,请尝试以下操作:

WHERE acc.acc_id = COALESCE(shop.acc_id, acc.acc_id)

#3

You should ALWAYS avoid using a Cartesian product in an SQL query whenever you can. Instead of joining the tables accounts and player_shops like that (with a comma) you should probably look into LEFT OUTER JOIN which might indirectly solve this problem for you as well.

您应该尽可能避免在SQL查询中使用笛卡尔积。不要像这样加入表帐户和player_shops(用逗号),你应该考虑LEFT OUTER JOIN,它也可能为你间接地解决这个问题。

#4

First, SELECTing * when you select from more than one table or when you have a group by clause is meaningless and will issue an error only.

首先,当您从多个表中选择时,或者当您有一个group by子句时,选择*是没有意义的,并且只会发出错误。

I think that you want here a LEFT JOIN:

我想你在这里想要一个LEFT JOIN:

 FROM accounts AS acc
 LEFT OUTER JOIN player_shops AS shop
   ON shop.acc_id = acc.acc_id

shop.acc_id will be filled with NULL if there is no record.

如果没有记录,shop.acc_id将填充NULL。

#1