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

最新推荐文章于 2024-04-17 20:45:59 发布

lanchengxiaoxiao

最新推荐文章于 2024-04-17 20:45:59 发布

阅读量1.3k

点赞数 1

分类专栏：工作问题文章标签： sql hashmap 数据结构联想数据库 java

工作问题专栏收录该内容

95 篇文章 0 订阅

订阅专栏

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) 
         
          GROUP 
          BY   
          o.grpid, DECODE (o.TYPE, 6, 16, 2, 1, 3, 1, o.TYPE) 
         
          ORDER 
          BY   
          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) 
         
          GROUP 
          BY   
          O.GRPID, DECODE (O.TYPE, 6, 16, 2, 1, 3, 1, O.TYPE) 
         
          ORDER 
          BY   
          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。