查询反模式 - GroupBy、HAVING的理解

发布日期:2018-02-09    浏览次数:491

为了最简单地说明问题,我特地设计了一张这样的表。

  

一、GROUP BY单值规则

  规则1:单值规则,跟在SELECT后面的列表,对于每个分组来说,必须返回且仅仅返回一个值。

  典型的表现就是跟在SELECT后面的列,如果没有使用聚合函数,必须出现在GROUP BY子句后面。

  如下面这个查询报错:

  

  因为对于按照部门分组之后,技术部分组有3个编号,销售部分组有2个编号,你让数据库显示哪个呢?

  如果假设你使用聚合函数COUNT(编号)之后,对于每个部门分组,就只有一个值 - 该部门下的人数:

  

  下面来实战下,我们希望查询出每个部门,最高工资的那个人的姓名,部门,工资。

  

  Shit,出师不利。第一次实战就错误了,我们来分析下。

  很明显,上面的姓名列是不符合单值规则的。我们的一厢情愿想法是,MAX(工资)之后,SQL Server就能自动帮我们返回不符合单值规则的'姓名'。但是很遗憾,SQL Server并没有这么做。理由如下:

  1.   如果两个人的工资相同,那么应该将哪个人的姓名返回?
  2.   如果我们使用的不是MAX()聚合函数,而是SUM、AVG等聚合函数(没有与之匹配的工资),那么姓名返回哪个?
  3.   如果在查询语句中使用了两个聚合函数,如MAX(),MIN()。那么应该返回的是MAX工资的姓名,还是MIN工资的姓名呢?

  综上所述,数据库是不可能能够根据我们输入的一个聚合函数,就帮助我们判断并显示出不符合单值规则的列的。

  对于MYSQL来说,当有这种不符合单值规则的列时,默认是返回这一组结果的第一条记录。而SQLite是返回最后一条。

  因此,对于以上查询,我们要另寻解决方案。

  解决方案1:关联子查询

SELECT 姓名,部门,工资 FROM 工资表 AS T1
WHERE NOT EXISTS (SELECT NULL FROM 工资表 AS T2 WHERE T1.部门 = T2.部门 AND T2.工资 > T1.工资)

  输出如下:

  

  完全符合要求。对于上面的关联子查询,可以理解为:

  遍历工资表的所有记录,查找不存在比当前记录部门相同且工资还大的记录。

  虽然,关联子查询的语法非常简单,但是性能并不好。因为对于每一条记录,都要执行一次子查询。

  解决方案2:衍生表

   使用衍生表的思路是,先执行一个子查询,得到一个临时结果集,然后用临时结果集和原表进行INNER JOIN操作。就能得到最高工资的人的信息。

  

  刚写出这个SQL语句时,觉得非常妙,理解了之后觉得非常妙。

SELECT 姓名,T1.部门,工资 FROM 工资表 AS T1 INNER JOIN
(
    SELECT 部门,MAX(工资) AS 最高 FROM 工资表    --执行查询,先记录两个字段 部门-最高工资
    GROUP BY 部门
) AS T2        --衍生表T2
ON T1.部门 = T2.部门 AND 工资 = 最高

  衍生表的方式性能优于关联子查询,因为衍生表的方式只执行了一次子查询。但是它需要一张临时表来存储临时记录。因此,这个方案也并不是最佳的解决方案。

  解决方案3:使用JOIN + IS NULL

  这是一个更妙的解决方案,当我们用一个外联结去匹配记录时,当匹配的记录不存在,就会用NULL来代替相应的列。

  我们先来看一条非常简答的SQL语句:

  

  从中你看到了什么?当T2表中,不存在比T1表中工资高的记录时就返回NULL。

  那么,那么,那么一个IS NULL是不是就解决问题了呢?

  

  好妙,好妙的方法,让人拍案叫绝的使用了OUTER JOIN。

  JOIN解决方案适用于针对大量数据查询并且可伸缩比较时。它总是能比基于子查询的解决方案更好地适应数据量的变量。

  解决方案4:对额外的列使用聚合函数

  我们知道,GROUP BY时,SELECT列表必须返回的是单值,那么我们可不可以通过使用聚合函数,让这个列返回单值呢?答案是可以的。

  

  其实,返回的数据是有问题的,当工资相同时,它就返回按姓名从大到小排列的第一个姓名。也就是说,当工资相同时,它只能够返回一条记录。

  我们将聚合函数换成MIN看看。

  

  解决方案5:Row_Number() + OVER

  WITH B AS
  (
      SELECT row_number() OVER(PARTITION BY Name ORDER BY CreateTime) AS part ,Score, Name, CreateTime
      FROM xxx
  )
  SELECT * FROM B WHERE Part = 1

 

  输出如下:

  

二、HAVING的理解

  WHERE与HAVING的区别:

  •   WHERE(分组前过滤):WHERE不能对聚合函数列进行过滤,因为执行WHERE的时候,分组尚未执行,聚合函数也未执行。
  •   HAVING(分组后过滤):主要用于对聚合函数列进行过滤,因为HAVING实在分组之后执行的。HAVING子句只能配合GROUP BY子句使用。没有GROUP BY子句时不能使用HAVING。

  错误使用WHERE的示例:

  

  正确使用WHERE与HAVING的示例:

  

 

本文网址:https://www.wyxxw.cn/blog-detail-17-18-178.html

返回列表

非特殊说明,本文版权归原作者所有,转载请注明出处

提示:本站所有资源仅供学习与参考,请勿用于商业用途。图片来自互联网~如侵犯您的权益,请联系QQ:1067507709.

提示:转载请注明来自:http://www.cnblogs.com/kissdodog/p/3365789.html 。 转载人:momo