数据库:join(连接)、group by(分组)、order by(排序)、where(条件)的执行顺序

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/alice_tl/article/details/88764591

join(连接)、group by(分组)、order by(排序)、where(条件)是数据库最常用的几个函数,这里简单介绍下各个函数的作用,及其共同使用时的使用顺序。

 

函数的作用

join:join主要是为了获取两个表中匹配关系时使用。

group by:group by主要用于分组,根据一个或多个列对结果集进行分组,达到对数据的分类更加精确。

order by:数据库中常用order by关键字对结果集进行排序,又可使用desc和asc来进行指定规则的排序。

where:可以在where 子句中指定任何条件,并且可以使用 and 或者 or 组合多个条件。

 

group by、order by和where的关系

  • group by必须位于where 后,order by前。
  • group一般与order by一起使用,执行顺序维持group by在前,order by在后。
  • 使用group by 后,若想进行再次筛选可以使用having,即having是在分组后进行筛选。
  • where是在执行条件后再分组。
  • having对一组数据进行操作;where是对行进行操作。
     

在SQL中执行的顺序

  • 先连接from后的数据源(若有join,则先执行on后条件,再连接数据源)。
  • 执行where条件
  • 执行group by
  • 执行having
  • 执行order by
  • 输出结果。

 

总体来说,语句编写的顺序为:

select》distinct 》from 》on》 join 》where 》group by 》with cube|rollup 》having 》select》distinct 》order by》 top

展开阅读全文

根据条件 进行group by 分组

09-28

最近有份代码原来写的意思大概如下rn如果条件1存在,使用资金代码(zjdm)进行分组rn如果条件2存在,使用交易代码(jydm)进行分组rn如果条件1,2都存在,使用资金代码(zjdm)和交易代码(jydm)进行分组rn[code=SQL]rndeclare @condition1 int ,@condition2 intrndeclare @zlb tablern(rn col1 float,rn col2 float,rn col3 float,rn zljg float,rn zlsl float,rn zlje float,rn zjdm varchar(255),rn jydm varchar(255) rn)rndeclare @mrmcb tablern(rn col1 float,rn col2 float,rn col3 float,rn jg float,rn sl float,rn je float,rn zjdm varchar(255),rn jydm varchar(255) rn)rndeclare @mrmcb_cale tablern(rn col1 float,rn col2 float,rn col3 float,rn jg float,rn sl float,rn je float,rn zjdm varchar(255),rn jydm varchar(255) rn)rndeclare @mrmcb_sum tablern(rn col1 float,rn col2 float,rn col3 float,rn jg float,rn sl float,rn je float,rn zjdm varchar(255),rn jydm varchar(255) rn)rnselect @condition1 = 1,@condition2 = 1rninsert @zlb select 1,2,3,10,20,200,'买','张三'rn union select 1,2,3,10,30,300,'买','张三'rn union select 1,2,3,10,20,200,'买','李四'rn union select 1,2,3,30,10,300,'买','李四'rninsert @mrmcb select 1,2,3,20,50,1000,'买','张三'rn union select 1,2,3,10,20,200,'卖','张三'rn union select 1,2,3,10,50,500,'买','李四'rn union select 1,2,3,20,30,600,'卖','李四'rnif @condition1 = 0 and @condition2 = 0 -- 如果条件1和条件2都没有 rnbeginrn insert @mrmcb_cale rn select col1,col2,col3,max(zljg),sum(zlsl),sum(zlje),'',''rn from @zlbrn group by col1,col2,col3rn insert @mrmcb_cale rn select col1,col2,col3,max(jg),sum(sl),sum(je),'',''rn from @mrmcb rn group by col1,col2,col3rn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je),'',''rn from @mrmcb_calern group by col1,col2,col3 rnendrnif @condition1 = 0 and @condition2 = 1 -- 如果条件2存在,使用交易代码(jydm)进行分组rnbeginrn insert @mrmcb_cale rn select col1,col2,col3,max(zljg),sum(zlsl),sum(zlje),'',jydmrn from @zlbrn group by col1,col2,col3,jydmrn insert @mrmcb_cale rn select col1,col2,col3,max(jg),sum(sl),sum(je),'',jydmrn from @mrmcb rn group by col1,col2,col3,jydmrn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je),'',jydmrn from @mrmcb_calern group by col1,col2,col3,jydm rnendrnif @condition1 = 1 and @condition2 = 0 -- 如果条件1存在,使用资金代码(zjdm)进行分组rnbeginrn insert @mrmcb_cale rn select col1,col2,col3,max(zljg),sum(zlsl),sum(zlje),zjdm,''rn from @zlbrn group by col1,col2,col3,zjdmrn insert @mrmcb_cale rn select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,''rn from @mrmcb rn group by col1,col2,col3,zjdmrn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,''rn from @mrmcb_calern group by col1,col2,col3,zjdm rnendrnif @condition1 = 1 and @condition2 = 1 -- 如果条件1,2都存在,使用资金代码(zjdm)和交易代码(jydm)进行分组rnbegin rn insert @mrmcb_cale rn select col1,col2,col3,max(zljg),sum(zlsl),sum(zlje),zjdm,jydmrn from @zlbrn group by col1,col2,col3,zjdm,jydmrn insert @mrmcb_cale rn select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,jydmrn from @mrmcb rn group by col1,col2,col3,zjdm,jydmrn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,jydmrn from @mrmcb_calern group by col1,col2,col3,zjdm,jydm rnendrnselect * from @mrmcb_sumrn[/code]rnrn想把这个代码缩短,优化,不喜欢看到重复的代码(强迫症。。。)下面贴2个自己优化的方法rn方案1 (失败) group by 不能拆开rn[code=SQL]rn-- 方案1 (失败) group by 不能拆开 设置condition1 = 1,@condition2 = 1 是结果错误rnselect @condition1 = 1,@condition2 = 1 --设置条件rnrninsert @zlb select 1,2,3,10,20,200,'买','张三' --插入测试数据rn union select 1,2,3,10,30,300,'买','张三'rn union select 1,2,3,10,20,200,'买','李四'rn union select 1,2,3,30,10,300,'买','李四'rninsert @mrmcb select 1,2,3,20,50,1000,'买','张三'rn union select 1,2,3,10,20,200,'卖','张三'rn union select 1,2,3,10,50,500,'买','李四'rn union select 1,2,3,20,30,600,'卖','李四'rnrn rnif @condition1 = 0 rnbeginrn insert @mrmcb_calern select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,'' as zjdm,'' as jydmrn from @zlbrn group by col1,col2,col3 rnendrnrnif @condition1 = 1rnbeginrn insert @mrmcb_calern select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,'' as zjdm,'' as jydmrn from @zlbrn group by col1,col2,col3,zjdm rnendrnrnif @condition2 = 0 rnbeginrn insert @mrmcb_calern select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je,'' as zjdm,'' as jydmrn from @mrmcbrn group by col1,col2,col3 rnendrnif @condition2 = 0 rnbeginrn insert @mrmcb_calern select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je,'' as zjdm,jydmrn from @mrmcbrn group by col1,col2,col3,jydm rnendrnif @condition1 = 0 and @condition2 = 0rnbeginrn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je),'',''rn from @mrmcb_calern group by col1,col2,col3rnendrnif @condition1 = 1 and @condition2 = 0rnbeginrn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,''rn from @mrmcb_calern group by col1,col2,col3,zjdm rnendrnif @condition1 = 0 and @condition2 = 1rnbeginrnrn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je),'',jydmrn from @mrmcb_calern group by col1,col2,col3,jydm rnendrnif @condition1 = 1 and @condition2 = 1rnbeginrnrn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,jydmrn from @mrmcb_calern group by col1,col2,col3,zjdm,jydm rnendrnrnselect * from @mrmcb_sumrn[/code]rn方案2 (优化的还是不好,代码重复太多)rn[code=SQL]rn--方案2 (优化的还是不好)rnselect @condition1 = 1,@condition2 = 0 --设置条件rnrninsert @zlb select 1,2,3,10,20,200,'买','张三' --插入测试数据rn union select 1,2,3,10,30,300,'买','张三'rn union select 1,2,3,10,20,200,'买','李四'rn union select 1,2,3,30,10,300,'买','李四'rninsert @mrmcb select 1,2,3,20,50,1000,'买','张三'rn union select 1,2,3,10,20,200,'卖','张三'rn union select 1,2,3,10,50,500,'买','李四'rn union select 1,2,3,20,30,600,'卖','李四'rn rnif @condition1 = 0 and @condition2 = 0 -- 如果条件1和条件2都没有 rnbeginrn ;withrn a as rn (rn select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,'' as zjdm,'' as jydmrn from @zlbrn group by col1,col2,col3rn unionrn select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je,'' as zjdm,'' as jydmrn from @mrmcb rn group by col1,col2,col3rn )rn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je),'' as jydm,'' as zjdmrn from arn group by col1,col2,col3 rnendrnif @condition1 = 1 and @condition2 = 0 rnbeginrn ;withrn a as rn (rn select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je, zjdm,'' as jydmrn from @zlbrn group by col1,col2,col3,zjdmrn unionrn select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je, zjdm,'' as jydmrn from @mrmcb rn group by col1,col2,col3,zjdmrn )rn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je), zjdm,'' as jydmrn from arn group by col1,col2,col3,zjdm rnendrnif @condition1 = 0 and @condition2 = 1 rnbeginrn ;withrn a as rn (rn select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,'' as zjdm,jydmrn from @zlbrn group by col1,col2,col3,jydmrn unionrn select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je,'' as zjdm,jydmrn from @mrmcb rn group by col1,col2,col3,jydmrn )rn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je), '' as zjdm,jydmrn from arn group by col1,col2,col3,jydm rnendrnif @condition1 = 1 and @condition2 = 1 rnbeginrn ;withrn a as rn (rn select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,zjdm,jydmrn from @zlbrn group by col1,col2,col3,zjdm,jydmrn unionrn select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je,zjdm,jydmrn from @mrmcb rn group by col1,col2,col3,zjdm,jydmrn )rn insert @mrmcb_sum rn select col1,col2,col3,max(jg),sum(sl),sum(je), zjdm,jydmrn from arn group by col1,col2,col3,zjdm,jydm rnendrnselect * from @mrmcb_sumrnrn[/code]rn请各位大神看看优化下,最好不要有重复的代码。 论坛

没有更多推荐了,返回首页