MySQL 如何将 WHERE 子句应用于除一个列之外的所有选定列？

Question

我正在汇总列并将它们按这样的“水果”列分组:(基于我的 previous question on SO )

SELECT `fruits`, sum(amount), sum(price)
FROM `accounts` 
GROUP BY fruits

我现在已经在表中添加了一个日期列，我正在尝试按这样的日期过滤结果:

SELECT `fruits`, sum(amount), sum(price)
FROM `accounts` 
WHERE `userid` = 1 AND `date` BETWEEN "2010-11-01" AND "2015-12-22"
GROUP BY fruits

这很好用，我得到了过滤后的结果。但是，如果在两个日期之间没有输入水果，它自然不会显示，因为 WHERE 子句也包含 fruits。

所以，我的问题是，我如何编写一个查询，它可以在不考虑日期的情况下获取所有水果，但只按日期过滤数量和价格列？

Answer 1

有几种方法。一种是使用条件聚合。删除谓词，并将条件测试移动到 SELECT 列表中的表达式。例如，像这样:

SELECT a.fruits
     , SUM( IF(a.userid = 1 AND a.date BETWEEN '2010-11-01' AND '2015-12-22'
              ,a.amount,0)
       ) AS `tot_amount`
     , SUM( IF(a.userid = 1 AND a.date BETWEEN '2010-11-01' AND '2015-12-22'
              ,a.price,0)
       ) AS `tot_price`
  FROM `accounts` a
 GROUP BY a.fruits

其他方法将使用行源返回您想要返回的 fruits 的值，然后对原始查询的总计进行“外部连接”。或者对于数量相当少的行，您可以在 SELECT 列表中使用相关子查询。

假设这会返回您要返回的一组 fruits:

SELECT s.fruits
  FROM `accounts` s
 GROUP BY s.fruits

我们可以将其包裹在括号中，并将其用作行源，就像它是表格一样。作为一个简单的例子:

SELECT f.fruits
  FROM ( 
         SELECT s.fruits
           FROM `accounts` s
          GROUP BY s.fruits
       ) f

现在我们可以对您的原始查询做同样的事情，将其包裹在括号中并使其成为内联 View :

SELECT f.fruits
     , IFNULL(t.tot_amount,0) AS tot_amount
     , IFNULL(t.tot_price,0)  AS tot_price
  FROM ( 
         SELECT s.fruits
           FROM `accounts` s
          GROUP BY s.fruits
       ) f
  LEFT
  JOIN (  
         -- original query here 
         SELECT a.`fruits`
              , sum(a.amount) AS tot_amount
              , sum(a.price) AS tot_price
           FROM `accounts` a
          WHERE a.`userid` = 1
            AND a.`date` BETWEEN '2010-11-01' AND '2015-12-22'
          GROUP BY a.fruits
       ) t
    ON t.fruits = f.fruits