MySQL中WHERE子句存在重复条件时如何返回对应数量的重复行？

你要的效果不能通过重复OR条件实现，WHERE子句仅负责过滤行，不会对原有匹配行做复制，你可以用以下两种方式实现：

方案1：UNION ALL拼接重复查询（适合重复次数少、逻辑简单的场景） #

需要计算几次对应id的价格，就拼接几次相同的查询语句：

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

方案2：关联序列表生成重复行（适合动态次数、多id批量计算的场景） #

先构造需求映射表（指定每个id需要计算的次数），再关联数字序列生成对应行数的重复数据：

SELECT ROUND(SUM(x.price),2) as "total" FROM (
   SELECT cd.name, MIN(tcgs.marketPrice) as "price"
   -- 配置每个id需要计算的次数
   FROM (SELECT 31374201 as target_id, 2 as count FROM DUAL) need_list
   -- 生成数字序列，这里示例最多支持3次，可根据需求扩展序列长度
   JOIN (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3) serial ON serial.n <= need_list.count
   JOIN card_database cd ON cd.id = need_list.target_id
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   GROUP BY cd.name, serial.n
) x

内容的提问来源于stack exchange，提问作者GenesisBits