group by Order by 这些sql语句多字段的理解和联想

转载 2012年03月29日 18:27:17

group by,order by单个字段很好理解. 但是很多时候,需要group by,order by多个字段. 理解sql背后怎么做很重要.

比如group by a1, a2 order by a2,a3表示先按a1分组返回结果集,再这个结果集上再对a2分组返回结果集, 然后针对返回的结果集再对a1排序返回结果集后,再对a2排序.

比如需要一个操作表op,grpid表示组id,每个组下可以有多种操作类型.比如1~10,step表示操作当前的状态,0表示未执行,1表示执行完. 表里面包含了下面的数据.

id, grpid,type  step
1   101   2     0 
2   101   2     0
3   101   3     0
4   101   4     0
5   101   1     0
6   201   3     0
6   201   2     0
8   201   3     1
9   201   1     0
10  201   1     1

需求是找到表中同组下未执行的相同类型的最小id值. 同时优先处理类型是1,2,3的,类型是6的不产生效率,放到最后处理.

SELECT   grpid, minid, ntype
  FROM  SELECT   o.grpid,
                     MIN(o.id) AS minid,
                     DECODE (o.TYPE, 6, 16, 2, 1, 3, 1, o.TYPE)AS ntype
              FROM  op o
             WHERE  o.step = 0 AND o.TYPE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
          GROUPBY   o.grpid, DECODE (o.TYPE, 6, 16, 2, 1, 3, 1, o.TYPE)
          ORDERBY   DECODE (o.TYPE, 6, 16, 2, 1, 3, 1, o.TYPE),MIN (o.id))

结果:

1   101   1(2经过decode运算看成了1,所以返回id不是5,而是1)

4   101   4(4不经过decode且该类型在101组只有1个值,返回)

6   201   1

而测试人员误以为取的是每个组下的最小opid时,所以会用下面的sql检验

SELECT   GRPID,COUNT(1)
  FROM  SELECT   O.GRPID,
                     MIN(O.ID) AS MINID,
                     DECODE (O.TYPE, 6, 16, 2, 1, 3, 1, O.TYPE)AS NTYPE
              FROM  CPC.CPCOPERATION O
             WHERE  O.STEP = 0 AND O.TYPE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
          GROUPBY   O.GRPID, DECODE (O.TYPE, 6, 16, 2, 1, 3, 1, O.TYPE)
          ORDERBY   DECODE (O.TYPE, 6, 16, 2, 1, 3, 1, O.TYPE),MIN (O.ID))
GROUP BY   GRPID
  HAVING  COUNT (1) > 1;

结果

grpid   count(1)

101     2 (这样就会出现误会. 会发现是需求理解的问题, 认为每个组应该只返回一个id, 其实上面的sql的逻辑是每个组下的每个类型的只返回一个值.)

201     1

------------------------------------------------------------------------------------------------

附网上找到的一些基础资料:
一.数据分组(group by ):

select 列a,聚合函数(聚合函数规范) from 表明

where 过滤条件

group by 列a

group by 字句也和where条件语句结合在一起使用。当结合在一起时,where在前,group by 在后。即先对select xx from xx的记录集合用where进行筛选,然后再使用group by 对筛选后的结果进行分组。

二.使用having字句对分组后的结果进行筛选,语法和where差不多:having 条件表达式

需要注意having和where的用法区别:

1.having只能用在group by之后,对分组后的结果进行筛选(即使用having的前提条件是分组)。

2.where肯定在group by 之前,即也在having之前。

3.where后的条件表达式里不允许使用聚合函数,而having可以。

三、当一个查询语句同时出现了where,group by,having,order by的时候,执行顺序和编写顺序是:

1.执行where xx对全表数据做筛选,返回第1个结果集。

2.针对第1个结果集使用group by分组,返回第2个结果集。

3.针对第2个结果集中的每1组数据执行select xx,有几组就执行几次,返回第3个结果集。

4.针对第3个结集执行having xx进行筛选,返回第4个结果集。

5.针对第4个结果集排序。

例子:

完成一个复杂的查询语句,需求如下:

按由高到低的顺序显示个人平均分在70分以上的学生姓名和平均分,为了尽可能地提高平均分,在计算平均分前不包括分数在60分以下的成绩,并且也不计算贱人(jr)的成绩。

分析:

1.要求显示学生姓名和平均分

因此确定第1步select s_name,avg(score) from student

2.计算平均分前不包括分数在60分以下的成绩,并且也不计算贱人(jr)的成绩

因此确定第2步 where score>=60 and s_name!=’jr’

3.显示个人平均分

相同名字的学生(同一个学生)考了多门科目 因此按姓名分组

确定第3步 group by s_name

4.显示个人平均分在70分以上

因此确定第4步 having avg(s_score)>=70

5.按由高到低的顺序

因此确定第5步 order by avg(s_score) desc

--------------------------------------------

group by联想

group by 多字段对于数据库的数据源非常方便,下面是网上的一道笔试题,里面有类似group by的需求,只不过此时数据源变成了日志文件,无法再使用sql语句,需要应用程序设计数据结构实现:

1,用户访问网站时,用随机的一个字符串表示该用户,同一个用户访问别的页面也用相同的字符串表示,用户id称为userId。
2,网站的每个页面用英文字符串来表示,称为page_type_id;
3,网站的访问日志的格式是:<userId 空格 page_type_id >
4,访问路径:找出用户访问最多三节访问路径,所谓三节路径是:home--prouduct---prouduct detail;
或者 prouduct--surport --faq  等等。
要求假设已经有了一个这样的一个日志文件,用java写一个程序,找出最多的三节路径。
下面是日志文件的示例:
123 home
234 product
456 product detail
123 product
456 product

123 product detail
......
解决思路:二个hashmap,m1:HashMap<userid,id1_id2_id3>   m2:HashMap<id1_id2_id3, count>

按照group by的思路,m1相当于按照group by userid, id1_id2_id3,m2相当于聚合函数count(id1_id2_id3)的感觉。

现在的问题是如何用应用程序构造id1_id2_id3这样的值,同时如何通过累加实现count的功能。

思路如下:

第一个HashMap<userID,page_type_id>,因为日志是顺序添加的,比如:
userID page_type_id
123    home                1
456    production          2
123    home                3

那么第一个HashMap保存['123','home'],同理,第三行标示用户‘123‘在相同页面刷新,那么可以跳过(等同于HashMap保存了这个值的话),如果没有遇到相同的话,
比如:
userID page_type_id
123    home                4
123    production          5
这样路径就变成了home-production,Map=['123','home,production'],如果在第N行,用户123的路径,没有在home-production的话,比如:
userID page_type_id
123    detail              N
那么,这个时候第一个HashMap=['123','home,production,detail'],第二HashMap保存[‘home,production,detail‘,1](count=1).

接下来,又遇到了用户“123”的话,把第一个HashMap['123','home,production,detail']的值替换掉,即又变成了[‘123’,‘home’],重复前面的做法,当路径符合三节的时候,把用户123在一个HashMap上面的值取出来,跟第二个HashMap[‘home,production,detail‘,1]的键(‘home,production,detail‘)比较,如果有的话,+1,如果没有的话,添加新的key给第二个HashMap。

二个累加器:一个用来计数,判断是否达到三层。另一个取map的key,取到加1,取不到放入,置1. 典型的hashmap的用法。


SQL语句order by两个字段同时排序。

Posted by 陶之11@网站&Webcode ORDER BY  后可加2个字段,用英文逗号隔开。 f1用升序, f2降序,sql该这样写 ORDER BY  f1, f2  ...

SQL语句order by两个字段同时排序

ORDER BY  后可加2个字段,用英文逗号隔开。 f1用升序, f2降序,SQL该这样写 ORDERBY  f1, f2  DESC 也可以这样写,更清楚: ORDERBY  f1 ASC...

MySql Order By 多个字段 排序规则

说在前面 程序员或多或少都用到过数据库的order by 语句排序查询,但是有几个人知道他的排序规则,当order by 多个字段的时候,排序股则又是怎样的。本人也是突发奇想,想了解一下mysql ...
  • xlxxcc
  • xlxxcc
  • 2016年08月21日 21:15
  • 51075

SQL语句order by两个字段同时排序问题

可能对不少网友来说非常简单,但是我觉得在一定程度上对我还是有些迷惑之处,下面就通过hibernate中的HQL语句来介绍下这类问题的效果。首先HQL语句代码:FROM Topic t WHERE t....

若SQL语句中的ORDER BY短语中指定了多个字段,则( )。

若SQL语句中的ORDER BY短语中指定了多个字段,则( )。  分享| 2010-08-02 14:19yingjie1107 | 浏览 12317 次  SQL A)依次按...

sql语句中order by 多个字段同时排序的应用

order  by 后面可以跟多个字段进行排序 用A1代表第一个字段,A2代表第二个字段 一、order by A1 , A2  desc   指的是用A1升序A2降序 二、order ...

GROUP BY 和 ORDER BY一起使用

====================== 写程序也有很长的一段时间了,有些东西我总不曾去思考,很少去积累一些有用的东西,总喜欢“用要即拿”的心态来对待,这是非常不好的坏习惯。这样只会造成依赖心太...
  • haiross
  • haiross
  • 2014年08月28日 14:51
  • 134248

sql order by与索引之间的关系(where条件出现字段才有效)

ORDER BY 通常会有两种实现方法,一个是利用有序索引自动实现,也就是说利用有序索引的有序性就不再另做排序操作了。另一个是把结果选好之后再排序。 用有序索引这种,当然是最快的,不过有一些限制条件...

linq/EF/lambda Group by/Order by 多个字段详细用法

原文出处:http://www.itdos.com/CSharp/20150409/0153322.html 1)单个字段Group by: //a.Key类型与a.Province字...

group by 多个字段

首先group by 的简单说明:    group by 一般和聚合函数一起使用才有意义,比如 count sum avg等,使用group by的两个要素:    (1) 出现在select后...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:group by Order by 这些sql语句多字段的理解和联想
举报原因:
原因补充:

(最多只允许输入30个字)