1、1.OVER子句我們所了解的OVER字句不僅僅是用于排序上,其實它還可以用于標(biāo)量的聚合函數(shù)-為每一行計算聚合,不需要你去分組.(分組這個點我覺得很舒服）我在上個筆記中其實已經(jīng)用到了這個用法 我再換個例子說說：create table #(id int, a int)insert # select1,2 union all select 1,3 union all select 1,4 union all select 1,5 union all select 2,2 union all select 2,4 union all select 2,8 union all select2,8sel

2、ect id,a,SUM(a) over(partition by id ),-分組總和AVG(a) over(partition by id ),-分組平均COUNT(a) over(partition by id ),-分組計數(shù)MAX(a) over (partition by id )-分組最大數(shù)from # /*id          a             

3、60;                                 - - - - - -1           2     

4、      14          3           4           51           3    

5、       14          3           4           51           4   

6、        14          3           4           51           5  

7、         14          3           4           52           2 

8、          22          5           4           82           4

9、           22          5           4           82         &#

10、160; 8           22          5           4           82        &#

11、160;  8           22          5           4           8*/-上面的SUM(a) over(partition by id)等價于(select

12、sum(a) from # group by id)我在上篇學(xué)習(xí)筆記也提過了,用OVER()聚合的效率比子查詢 高非常多.2.關(guān)于多屬性的比較create table #test (id int, a int, b int , c int)insert #test 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 un

13、ion all select 2,7,3,8 union all select 2,1,9,9 -這個題目我們要求出根據(jù)ID分組,求出最大的一條記錄.這個所謂的最大就是先比A 再比B 再比C 要求結(jié)果：/*1,3,7,82,7,3,3*/-如果有這么個寫法 SQL 可以支持就好了- | 可惜沒有select ID,MAX(a,b,C) from #testgroup by ID-普通做法,依次比較select * from #test kwhere not exists(select * from #test where id=k.id and (a>k.a or a=k.a and b

14、>k.b or a=k.a and b=k.b and c>k.c)-把每個字符拼接起來,一次比完大小,這里注意將字段要轉(zhuǎn)化成相同的長度,這個的優(yōu)點是無論是否有好的索引,因為它只掃描一次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 colfrom #test group by ID) l3.PIVOT 這個基本語法大家都知道,我只說

15、一點吧,你不能旋轉(zhuǎn)多列屬性,除非你提前在CTE 或者派生表里面處理好 ，如下：create table #p(empid int, name varchar(10),val int)insert #p select 1,'a',2 union all select 1,'a',3 union all select 1,'a',4 union all select 1,'b',4 union all select 2,'a',5 union all select 2,'a',2 union all s

16、elect 2,'b',2  -drop table #p ;with cte as(select RTRIM(empid)+'_'+name as e_name,val from #p )select *from cte pivot(sum(val) for e_name in (1_a,1_b,2_a,2_b) l/*1_a         1_b         2_a 

17、0;       2_b- - - -9           4           7           2*/-至于Unpivot 簡單理解就是Pivot的反向操作.當(dāng)然注意一點：被旋轉(zhuǎn)的數(shù)據(jù)必須具有相同的數(shù)據(jù)類型.4。聚合問題a.連

18、接字符串問題:這個論壇里已經(jīng)寫爛了,2000用函數(shù),2005用XML PATH 我就不寫這里提供個新的方法：create table #p(empid int, name varchar(10)insert #p 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,&#

19、39;v'  -drop table #p -前提是name不能在同一組里出現(xiàn)重復(fù),且每組內(nèi)最大記錄數(shù)不是很大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 ','+na

20、me else ''  end) from(select empid,name,(select COUNT(*) from #p where k.empid=empid and >=name) as rn from #p k )z group by empid/*empid       name- -1           a,b,c,d2    

21、60;      a,t,v*/b.組內(nèi)數(shù)字連乘create table #p(empid int, val varchar(10)insert #p select 1,2union all select 1,6union all select 1,3union all select 1,4union all select 2,2union all select 2,3union all select 2,6  -drop table #p -loga(b)=x 等價于 power(a,x)=b-loga(v1*.*vn)=loga(

22、v1)+.+loga(vn)-v1*v2*.*vn=power(10,log10(v1*v2*.*vn)=POWER(10,sum(LOG10(val)select empid,乘積=POWER(10,sum(LOG10(val)from #p group by empid/*empid       乘積- -1           1442           36*/c.取中值(昨天正好有個這個問題的貼)-按rp分組、ddsj排序，選擇出每組擺在正中間的記錄行（若為偶數(shù)行的話，選擇擺在正中間的兩條記錄行）create table a(rq varchar(8), ddsj int)insert into aselect'200805',30 union all select'200805',