[推荐] 【SQL中一些特别地方特别解法】 100feixianxxx]

发布时间:2016-12-8 8:25:42 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"[推荐] 【SQL中一些特别地方特别解法】 100feixianxxx]",主要涉及到[推荐] 【SQL中一些特别地方特别解法】 100feixianxxx]方面的内容,对于[推荐] 【SQL中一些特别地方特别解法】 100feixianxxx]感兴趣的同学可以参考一下。

本帖最后由 feixianxxx 于 2010-03-22 14:17:50 编辑 详见博客地址:http://blog.csdn.net/feixianxxx/archive/2010/03/21/5402391.aspx /*---------------------------------------------------------------------- *auther:Poofly *date:2010.3.14 *VERSION: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)  Jul  19 2008  Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: ) *转载请注明出处 *更多精彩内容,请进http://blog.csdn.net/feixianxxx ------------------------------------------------------------------------*/ --收集一些东西来写写 --1.EXCEPT 注意点:2个NULL值对于EXCEPT是相等的,不同于一般的筛选器. --环境 create table test_1 (a int ,b int) create table test_2 (c int, d int) insert test_1 select 1,2 union all select 1,null union all select 3,4 insert test_2 select 1,2 union all select 1,null -- except  select * from test_1  except  select * from test_2 /* a           b ----------- ----------- 3           4 */ -- not exists select * from test_1 where not exists(select * from test_2 where a=c and b=d) /* a           b ----------- ----------- 1           NULL               --这条记录对于test_1来说是唯一的 3           4 */ ps:因为现有版本不支持 except all 所以EXCEPT使用时候会有一个排序的阶段,效率一般不太好 --2.OVER()子句 注意点:OVER()子句在多聚合情况下比在select 下的子查询效率 or GROUP BY 高很多 --环境 create table test_3 ( id int, value int ) insert test_3 values(1,12) insert test_3 values(1,1) insert test_3 values(1,3) insert test_3 values(1,2) insert test_3 values(1,6) insert test_3 values(2,1) insert test_3 values(2,2) insert test_3 values(2,4) insert test_3 values(2,3) go --OVER SELECT ID, [SUM]=SUM(VALUE) OVER(), [AVG]=AVG(VALUE) OVER(), [COUNT]=COUNT(VALUE) OVER(), [MAX]=MAX(VALUE) OVER() FROM test_3  --子查询 select id, [SUM]=(select SUM(VALUE) from test_3 where l.id=id), [AVG]=(select AVG(VALUE) O from test_3 where l.id=id), [COUNT]=(select COUNT(VALUE)  from test_3 where l.id=id), [MAX]=(select MAX(VALUE)  from test_3 where l.id=id) FROM test_3 l --group by  SELECT ID, [SUM]=SUM(VALUE), [AVG]=AVG(VALUE) , [COUNT]=COUNT(VALUE) , [MAX]=MAX(VALUE)  FROM test_3  group by id CTRL+L 可以发现 over的开销明显小于后者..其中子查询效率极差 --3.利用字符串解决带附加属性的问题 注意点:当需要附加属性来解决问题时候,一般会想到子查询,但是因为子查询需要良好的索引设计,所以不太好用.可以使用字符串来解决这个问题 --环境 create table test_4 (id int, a int, b int , c int) insert test_4  select  1,2,3,4 union all select  1,3,5,4 union all select  1,3,7,4 union all select  1,3,7,8 union all select  2,2,3,4 union all select  2,5,3,8 union all select  2,5,3,8 union all select  2,7,3,8 union all select  2,1,9,9  go --字符串 select  ID, a=SUBSTRING(COL,1,5), b=SUBSTRING(COL,6,5), c=SUBSTRING(COL,11,5) from ( select ID, MAX(CAST(a as char(5))+CAST(b as char(5))+CAST(c as char(5))) as col from test_4  group by ID ) l --子查询 select *  from test_4  k where not exists(select * from test_4  where id=k.id and (a>k.a or a=k.a and b>k.b or a=k.a and b=k.b and c>k.c)) /* ID          a          b          c ----------- ---------- ---------- ---------- 1           3          7          8     2           7          3          8     */  这个字符串优点是无论是否有好的索引,因为它只扫描一次(这里数据量太小)      --4.利用聚合实现字符串拼接 注意:不用XML、函数、临时表、游标去实现字符串的拼接 --环境 create table test_5(empid int, name varchar(10)) insert test_5 select  1,'a'union all select  1,'b' union all select  1,'c'union all select  1,'d'union all select  2,'a' union all select  2,'t'union all select  2,'v'   select  empid, name=MAX(case when rn=1 then name  else '' end)+MAX(case when rn=2 then ','+name else '' end) +MAX(case when rn=3 then ','+name else '' end)+MAX(case when rn=4 then ','+name else ''  end)  from( select empid,name, (select COUNT(*) from test_5 where k.empid=empid and k.name>=name) as rn  from test_5 k )z  group by empid --xml select empid, name=stuff((select ','+name as [text()] from test_5 where k.empid=empid order by name for XML PATH('')),1,1,'') from test_5 k group by empid /* empid       name ----------- ------------------------------------------- 1           a,b,c,d 2           a,t,v */ ps:该方法前提是name不能在同一组里出现重复,且每组内最大记录数不是很大。通过执行发现该方法效率优于XML    适合用于SQL2000中想实现字符串拼接但是又不想函数的情况下.      5.TOP + ORDER BY 求中值 注意点:可以利用TOP + ORDER BY 巧妙解决取中值的问题,而且在SQL2000也可以方便使用 --环境 create table test_6(rq varchar(8), ddsj int) insert into test_6 select '200805',30 union all select '200805',40 union all select  '200805',50 union all select  '200805',20 union all select  '200806',250 union all select '200806',200 union all select '200806',310 union all select '200806',100 union all select '200806',130 go --TOP + ORDER BY 求中值(NTILE同样的效果) select rq, ( ( select MAX(ddsj) as ddsj from (select top 50 percent ddsj from test_6 where rq=k.rq order by ddsj ) k  )+ ( select MIN(ddsj) as ddsj from (select top 50 percent ddsj from test_6 where rq=k.rq order by ddsj desc) k  ) )/2 as ddsj from test_6 k group by rq  --利用位置的收尾呼应~ ;with cte as ( select *,ROW_NUMBER() over(partition by rq order by ddsj ) as rn1,  ROW_NUMBER() over(partition by rq order by ddsj desc ) as rn2 from test_6  ) select rq,ddsj=AVG(ddsj)  from cte  where abs(rn1-rn2)<=1 group by rq  /* rq       ddsj -------- ----------- 200805   35 200806   200 */  在适用 缺点是没有合适的索引将会很慢   --6 OR 转变成AND 注意点:大家都知道OR在where条件出现时候,一般来说意味着索引的失效,只要筛选列上有一个没有索引的话.相比较而言,AND是有个索引就有个效果.所以我们有必要在能将OR转成AND的时候就转化. --环境 create table test_7 ( id int, col1 int, col2 int ) create index in_col1 on test_7(col1) insert test_7 values(1,1,3) insert test_7 values(1,1,2) insert test_7 values(1,2,3) go 100000 insert test_7 values(1,3,3) insert test_7 values(1,3,1) insert test_7 values(2,2,3) insert test_7 values(2,2,5) insert test_7 values(2,4,9) go --or select * from test_7 where col1<1 or (col1=1 and col2<3) --and select * from test_7  where col1<=1 and (col1<1 or col2<3) 通过执行计划可以看到 带AND的开销比OR少了倍,它用到了COL1上的索引 --7 利用计算表达式改变IDENTITY 注意:使用select into 表的时候如果有自增列 会把自增的属性也复制过去,可以通过计算表达式去去除这个自增属性 --环境 create table test_8(id int identity(1,1),a int) insert test_8 select 1 insert test_8 select 2 insert test_8 select 3 insert test_8 select 7 insert test_8 select 9 go select ID+0 as id ,A into #1 from test_8  insert #1(a) select 8 select * from #1  /* id          A ----------- ----------- NULL        8                 ---没有自增 1           1 2           2 3           3 4           7 5           9 */ --8.set 中的连等 注意:在更新时也可以使用连续等于 @[email protected]+1 <===> @i加后赋值给COL字段 --环境 CREATE TABLE test_9 (   id INT        NOT NULL,   col VARCHAR(5) NOT NULL ); INSERT INTO test_9 VALUES(0, 'A'); INSERT INTO test_9 VALUES(0, 'B'); INSERT INTO test_9 VALUES(0, 'C'); INSERT INTO test_9 VALUES(0, 'C'); INSERT INTO test_9 VALUES(0, 'C'); INSERT INTO test_9 VALUES(0, 'B'); INSERT INTO test_9 VALUES(0, 'A'); go DECLARE @i AS INT; SET @i = 0; UPDATE test_9 SET @i = id = @i + 1; go select * from test_9  /* id          col ----------- ----- 1           A 2           B 3           C 4           C 5           C 6           B 7           A */

sf.

该回复于2010-07-28 10:56:28被版主删除

sf我的哈哈哈

该回复于2010-07-28 10:56:27被版主删除

切 板凳都没了 还沙发呢

6楼sf

学习.

最好except ALL 的实现方法,intersect,intersect all 的2000,2005的写法也搞出来,要写就写全了嘛。。。。 哈哈

引用 9 楼 ldslove 的回复:最好except ALL 的实现方法,intersect,intersect all 的2000,2005的写法也搞出来,要写就写全了嘛。。。。 哈哈 ..... 想写的 ....下次了...l累啊。。

膜拜

eee

字符合并的方法很好,UP

学习接分~~~~

膜拜- -强人

ooooooooooooooooooooooooooooooooooooooooooooooooooooooooo

MARKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK

学习 学习~呵呵

谢谢分享  呵呵 ~

辛苦了 3ky

学习了~~~~~~~~~~~~~~~~~~~~~~~~

EXCEPT还真不常用 关于那个拼字符串的,你确定那么写效率高?

这个还真要认真看看!

看看……

看完后不得不补充几点: 1,7,8条没什么好说的 2, 其实看查询计划可以看出2因为窗口操作,会有比较复杂的逻辑运算在里面。 子查询写法,当然是不用说的 当你开启io选项查看后,其实group by是最简单的. 当分组依赖列(或列组合)上索引后,特别是聚集索引后,group by的优势就更加体现。 3, 这个做法,实际上用了 空格占位的做法, 但是一想就知道有问题,因为空格占位比较大小是字串大小。 就像我们一些以路径方式存储的分级一样 10001111 1111 11110001 它用0占位,而这里楼主采用空格占位,可想必然是有问题的。 0009 用你的方法成为 9000 随便改一条记录值,比如最后一条,插入 2,11,9,9 就可看出问题 4, 第一种做法属硬编码,效率自然会好些。只是适用性差了些。 5, 这个我没理解,5个数字的中值 的定义是什么,呵呵 6, 是诚不可取的, 无论怎么进行逻辑变换,你这个写法也不成立。 逻辑 <> 数据凑巧 第一种, col1 < 1  or ... 从这里看出,只要col1 < 1无论 col2怎么条件,这部分col1<1的数据都成立 而第二种,始终有col2<3的限制

学习 顶一个

顶一哈

学习,接分.

值得学习,有空 多教教我

不错,学习,谢谢lz!

学习了。。。

引用 26 楼 fcuandy 的回复:看完后不得不补充几点: 1,7,8条没什么好说的 2, 其实看查询计划可以看出2因为窗口操作,会有比较复杂的逻辑运算在里面。 子查询写法,当然是不用说的 当你开启io选项查看后,其实group by是最简单的. 当分组依赖列(或列组合)上索引后,特别是聚集索引后,group by的优势就更加体现。 3, 这个做法,实际上用了 空格占位的做法, 但是一想就知道有问题,因为空格占位…… 这位网友很热心,评论也很准

1 结论接了,谢谢楼主

结论接了,谢谢楼主

很强悍啊

引用楼主 feixianxxx 的回复:详见博客地址:http://blog.csdn.net/feixianxxx/archive/2010/03/21/5402391.aspx SQL code /*---------------------------------------------------------------------- *auther:Poofly *date:2010.3.14 *VERSION:…… 确实值得看看啊,对于数据库提高有帮助

路过,学习,蹭分....

引用 26 楼 fcuandy 的回复:看完后不得不补充几点: 1,7,8条没什么好说的 2, 其实看查询计划可以看出2因为窗口操作,会有比较复杂的逻辑运算在里面。 子查询写法,当然是不用说的 当你开启io选项查看后,其实group by是最简单的. 当分组依赖列(或列组合)上索引后,特别是聚集索引后,group by的优势就更加体现。 3, 这个做法,实际上用了 空格占位的做法, 但是一想就知道有问题,因为空格占位…… 学习,越来越迷恋偶像了

拜读了、、、、

学习下、。。。。。。。。。。。。。。。。。

很好,学习了,谢谢分享!!!

不错不错

引用 26 楼 fcuandy 的回复:6, 是诚不可取的, 无论怎么进行逻辑变换,你这个写法也不成立。 逻辑 <> 数据凑巧 第一种, col1 < 1 or ... 从这里看出,只要col1 < 1无论 col2怎么条件,这部分col1<1的数据都成立 而第二种,始终有col2<3的限制 这逻辑能力太强悍了

说得不错,好好学习下!

学习.

受用了....

学习。经验是宝贵的。谢谢分享。

学习.

该回复于2012-08-28 09:31:41被版主删除

不错真是受益匪浅啊

引用 45 楼 beirut 的回复:引用 26 楼 fcuandy 的回复: 6, 是诚不可取的, 无论怎么进行逻辑变换,你这个写法也不成立。 逻辑 <> 数据凑巧 第一种, col1 < 1 or ... 从这里看出,只要col1 < 1无论 col2怎么条件,这部分col1<1的数据都成立 而第二种,始终有col2<3的限制 这逻辑能力太强悍了 你不如嫁給他吧

受教了,学习中,谢谢

借鉴!

谢谢 这样的精神真的是少有  这样大家都能进步

相当经验了,收藏!

学习了,mark

第六个中的两个语句的结果都不一样吧? 第八个倒是没见过,不过平常抢分时候秀一下还可以,真要写UPDATE了最好还是老老实实分开写。 总的来说T-MAC的文章是越来越牛了

的确1、7、8没得说的,其它的都有得说

引用 54 楼 playwarcraft 的回复:引用 45 楼 beirut 的回复: 引用 26 楼 fcuandy 的回复: 6, 是诚不可取的, 无论怎么进行逻辑变换,你这个写法也不成立。 逻辑 <> 数据凑巧 第一种, col1 < 1 or ... 从这里看出,只要col1 < 1无论 col2怎么条件,这部分col1<1的数据都成立 而第二种,始终有col2<3的限制 这逻辑能力太强悍了 你不如嫁給他吧 我还小

发放人

SQL

看来自己差的太远了,学习ING

学习 啊 还有很长的路要走啊

mark............

mark yi xia

无语。太没法比了。

gip i tdut p j tc knx 

yes!!!

SQL中還有這麼奇妙的用法啊,頂一下咯

mark~~~

学习。经验是宝贵的。谢谢分享。

mark....谢谢分享

雁过....

学习了

留下,择日再看。

继续中...........

每天回帖即可获得10分可用分!

mk..

每天回帖即可获得10分可用分!

看上去不错。下来收藏一下。慢慢领悟一下。

mark~~

  总结的很好,谢谢拿出来和大家分享

学习了 

引用 26 楼 fcuandy 的回复:看完后不得不补充几点: 1,7,8条没什么好说的 2, 其实看查询计划可以看出2因为窗口操作,会有比较复杂的逻辑运算在里面。 子查询写法,当然是不用说的 当你开启io选项查看后,其实group by是最简单的. 当分组依赖列(或列组合)上索引后,特别是聚集索引后,group by的优势就更加体现。 3, 这个做法,实际上用了 空格占位的做法, 但是一想就知道有问题,因为空格占位…… 第五个 :中值的定义就是每组的数字经过排序后 如果一组有偶数个 那么就取当中2个平均值 如一组是奇数个,那就取当中的那个数值 第六个 :不小心笔误了 已经改正 应该是col1<1 or (col1=1 and col2<3)=> col1<=1 and (col1<1 or col2<3)

第二个问题确实欠缺考虑  没有考虑如果分组列加索引的情况 、 第三个问题我转换成 二进制后 可否解决此问题 select  ID, a=cast(SUBSTRING(COL,1,5) as int), b=cast(SUBSTRING(COL,6,5) as int), c=cast(SUBSTRING(COL,11,5) as int) from ( select ID, MAX(CAST(a as binary(5))+CAST(b as binary(5))+CAST(c as binary(5))) as col from test_4  group by ID ) l

很好,很强悍

很好很强大,楼主不错

很好啊 ,我顶

mk..

Mark,学习。

似的撒旦发生发生发生

引用 89 楼 feixianxxx 的回复:第二个问题确实欠缺考虑 没有考虑如果分组列加索引的情况 、 第三个问题我转换成 二进制后 可否解决此问题 SQL code select  ID, a=cast(SUBSTRING(COL,1,5) as int), b=cast(SUBSTRING(COL,6,5) as int), c=cast(SUBSTRING(COL,11,5) as int) from ( sel…… 不就等于0占位了

BD无了,我坐地板

用执行计划的开销来判断两种sql写法的效率不太妥,应该结合io.

引用 26 楼 fcuandy 的回复:看完后不得不补充几点: 1,7,8条没什么好说的 2, 其实看查询计划可以看出2因为窗口操作,会有比较复杂的逻辑运算在里面。 子查询写法,当然是不用说的 当你开启io选项查看后,其实group by是最简单的. 当分组依赖列(或列组合)上索引后,特别是聚集索引后,group by的优势就更加体现。 3, 这个做法,实际上用了 空格占位的做法, 但是一想就知道有问题,因为空格占…… 2,可以通过 update 或者 select 加几个变量去处理。效率也是比较高的。。

错了,我上面说的是第4个问题

上一篇:数据库获得值 40huandfly]
下一篇:数据插入数据库时自动填充空白ID 40flash8627]

相关文章

相关评论