mysql - 如何将这两个sql合并为一个

Question

我想将这 2 个 sql 合并为 1 个

SELECT `buy_id`,count(*) as total1 FROM `sb_offer` WHERE `offer_time`>='1563811200' AND `offer_time`<'1564416000' group by `buy_id`

SELECT `buy_id`,count(*) as total2 FROM `sb_offer` WHERE `offer_time`>='1563811200' AND `offer_time`<'1564416000' AND `is_matching`=1 group by `buy_id`

Answer 1

这就是 CASE WHEN 的用途。您可以使用 sum 并根据您的条件传递 1 或 0。

例如:

SELECT `buy_id`
   ,COUNT(*) AS total1 
   ,SUM(CASE WHEN `is_matching`=1 THEN 1 ELSE 0 END) AS total2
FROM `sb_offer` 
WHERE 
  `offer_time`>='1563811200' 
  AND `offer_time`<'1564416000' 
GROUP BY `buy_id`