WHERE 子句是否在子查询中给出不同的结果 [英] WHERE clause gives different result wether in subquery or not

问题描述

关注最后两行...

SELECT *
FROM
  (SELECT d.*
   FROM downloads AS d
   LEFT JOIN ps_customer AS pc ON d.id_customer=pc.id_customer
   WHERE pc.active=1
     AND d.id_customer IS NOT NULL
   GROUP BY id_product,
            id_customer) AS tmp
WHERE YEAR(tmp.date_download)=2015
  AND tmp.name='Antescofo'

and(移动到子查询中)

and (moved inside the subquery)

SELECT *
FROM
  (SELECT d.*
   FROM downloads AS d
   LEFT JOIN ps_customer AS pc ON d.id_customer=pc.id_customer
   WHERE pc.active=1
     AND d.id_customer IS NOT NULL
     AND YEAR(d.date_download)=2015
     AND d.name='Antescofo'
   GROUP BY id_product,
            id_customer) AS tmp

... 给出不同的行数结果.

... gives a different rows count result.

我试图理解为什么，因为 WHERE 子句以某种方式充当过滤器.

I'm trying to understand why, because a WHERE clause is somehow acting as a filter.

在子查询中或之后过滤有什么区别?

据我所知，第一个是选择现有和活跃客户的所有重复数据下载，仅显示 2015 年生产的 Antescofo 产品的下载"

第二个像从现有和活跃客户中选择所有与 Antescofo 产品相关且在 2015 年制造的重复数据下载，并显示所有内容".

推荐答案

当您引用不在 group by 列表中的列时，MySQL 将返回一个随机选择的值组中的行.因此，无论您是在子查询内部过滤，在 group by 之前，还是在子查询外部，在 group by 之后，都很重要.在第二种情况下，将随机包含或排除具有匹配和不匹配行的组.

When you refer to a column that is not in the group by list, MySQL will return a randomly chosen value from one of the rows in the group. So it matters whether you filter inside the subquery, before the group by, or outside the subquery, after the group by. In the 2nd case, groups with matching and non-matching rows will be included or excluded randomly.

这篇关于WHERE 子句是否在子查询中给出不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！