mysql - 在 WHERE 子句中找到重复项时返回？

Question

我有一个查询要从子查询中获取SUM总数，如下所示：

SELECT ROUND(SUM(x.price),2) as "total" FROM (
   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201 or cd.id = 31374201) 
   GROUP BY cd.name
) x

当子句中发现重复项时，我希望子查询添加一个新的不同行WHERE；

举个例子：

   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201) 
   GROUP BY cd.name

它有1个WHERE条件，输出是；

但是，如果我修改它并添加一个副本OR cd.id = 31374201：

   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201 OR cd.id = 31374201) 
   GROUP BY cd.name

然后我希望预期结果是两个不同的列，以便可以在我的原始查询中求和：

到目前为止，该WHERE子句似乎在使用OR.

我可以在 PHP 中执行此操作，但我想知道是否有办法避免这种情况并直接在 MySQL 中执行。

标签： mysql

Answer 1

正如@Barmar 和@Frankich 在评论中指出的那样，查询并没有忽略 OR。该行只是被选中。

因此，我不得不使用UNION ALL. 常规UNION会删除重复项。

SELECT ROUND(SUM(x.price),2) as "total" FROM (
   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201) GROUP BY cd.name
   UNION ALL
   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201) GROUP BY cd.name
) x