SQLServer常用sql2 - 数据库编程

then 'Feb' when 3 then 'Mar' when 4 then 'Apr' when 5 then 'May' when 6 then 'Jun' when 7 then 'Jul' when 8 then 'Aug' when 9 then 'Sept' when 10 then 'Oct' when 11 then 'Nov' else 'Dec' end )

最大与最小

 select MIN(cc.DateKey) aa, MAX(cc.DateKey) bb 
 from(
   select DateKey from BI_Dim_Date where Dim_Year=2014 and Dim_Month=11
 ) cc

查询每个区的前两条

select * from 
(select *, row_number() over(partition by area_name order  by area_name  ) aa  FROM db_bi) t 
 where t.aa<=2

字符串的截取见贴：http://bbs.csdn.net/topics/390946681

---新建表
if OBJECT_ID('test') is not null
drop table test 
go 
create table test 
( 
    id int, 
    name varchar(10), 
    [key] varchar(20) 
) 
go 
--插入数据
insert test 
select 1,'lisa','li,is,sa' union all
select 2,'sophia','ab,cd,ef' union all
select 3,'lori','12,34,23'
go 

select * from test
   /**
   master..spt_values是数字辅助表，里面是1,2,3,4...
charindex是查找key中‘,’的位置(从第number)位开始找
substring是从第number位开始取字符串，截止位置是charindex中得出的‘,’的位置
所要实现的功能是，取出key中的以‘,’分开的值**/
   
   
select
    id, 
    a.name, 
    SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key] 
from
    test a,master..spt_values 
where
    number >=1 and number<=len([key])  
    and type='p' 
    and substring(','+[key],number,1)=','
    
    
    select * from master..spt_values  where type='p'

递归查询

----2008递归  由父项递归到子项 查询父ID为wID的
with cte(ID,PID)
AS(
--父项
select * from DIGUI where PID='wID'
union all
--递归结果集中的下级
select t.ID,t.PID  from DIGUI as t
inner join cte as c on t.PID=c.id
)
select * from cte


---由子项递归到父项  查询所有子ID=kssID的
with cte(ID,PID)
AS(
--下一级父项
select * from DIGUI where ID='kssID'
union all
--递归结果集中的父项
select t.ID,t.PID  from DIGUI as t
inner join cte as c on t.ID=c.PID
)
select * from cte

SQLServer常用sql2(三)