SQLServer 常用sql2

--新建  
CREATE TABLE [dbo].[BI_Dim_Date]( 
  --YYMMDD 
    [DateF]   [nvarchar](20) NULL,  --法国日期格式
    [DateKey] [nvarchar](10) NULL,  
    [Dim_Year] [int] NULL,
    [Dim_Month] [int] NULL,    
    [Dim_Day] [int] NULL,
    --季度  
    [Qu] [int] NULL,  
    [QuCN] [varchar](20) NULL,  
    [QuEN] [varchar](20) NULL,  
   --月份
    [MonthCN] [varchar](20) NULL,  
    [MonthEN] [varchar](20) NULL,  
    --旬 1 上旬 2 中旬 3 下旬
    [Ten] [int] NULL,  
    [TenCN] [varchar](20) NULL,  
    --周
    [Dim_Week] [int] NULL,  
    --星期几
    [WeekDayCN] [varchar](20) NULL,  
    [WeekDayEN] [varchar](20) NULL,  
    --yy-mm-dd
    [Dim_Date1] [date] NULL, 
    --DD/MM/YY
     [Dim_Date2] [nvarchar](10) NULL,
     
     ---是否节假日 1放假 0正常
      [IsDayOff] [int] NULL, 
     --假日说明
     [Event_Name] [varchar](20) NULL     
)  

---插入顺序很建表顺序要对应

DECLARE @BeginDate DATE;  
  
SELECT @BeginDate = '20131230';  
WHILE @BeginDate<='20171231'  
BEGIN  
INSERT INTO BI_Dim_Date  

SELECT  
dbo.ChangeIt(@BeginDate)                                as DateF,
--CONVERT(varchar(10),@BeginDate,120)                     AS DateKey --查出格式2013-12-30
CONVERT(varchar(10),@BeginDate,112)                    AS DateKey,--
YEAR(@BeginDate)                                        AS Dim_Year,  
MONTH(@BeginDate)                                       as Dim_Month,  
Day(@BeginDate)                                         AS Dim_Day,  

Datepart(QUARTER,@BeginDate)                            AS Qu,  
CASE  
WHEN Datepart(QUARTER,@BeginDate)=1 then '第一季度'  
WHEN Datepart(QUARTER,@BeginDate)=2 then '第二季度'  
WHEN Datepart(QUARTER,@BeginDate)=3 then '第三季度'  
ELSE '第四季度'  
 END                                                    AS  QuCN,  
 CASE  
WHEN Datepart(QUARTER,@BeginDate)=1 then 'Q1'  
WHEN Datepart(QUARTER,@BeginDate)=2 then 'Q2'  
WHEN Datepart(QUARTER,@BeginDate)=3 then 'Q3'  
ELSE 'Q4'  
 END                                                    AS  QuEN,  
 

 case  
 when MONTH(@BeginDate)= 1 then '一月'  
 when MONTH(@BeginDate)= 2 then '二月'  
 when MONTH(@BeginDate)= 3 then '三月'  
 when MONTH(@BeginDate)= 4 then '四月'  
 when MONTH(@BeginDate)= 5 then '五月'  
 when MONTH(@BeginDate)= 6 then '六月'  
 when MONTH(@BeginDate)= 7 then '七月'  
 when MONTH(@BeginDate)= 8 then '八月'  
 when MONTH(@BeginDate)= 9 then '九月'  
 when MONTH(@BeginDate)= 10 then '十月'  
 when MONTH(@BeginDate)= 11 then '十一月'  
 else '十二月'  
 end                                                as MonthCN,  
 case  
 when MONTH(@BeginDate)= 1 then 'Jan'  
 when MONTH(@BeginDate)= 2 then 'Feb'  
 when MONTH(@BeginDate)= 3 then 'Mar'  
 when MONTH(@BeginDate)= 4 then 'Apr'  
 when MONTH(@BeginDate)= 5 then 'May'  
 when MONTH(@BeginDate)= 6 then 'Jun'  
 when MONTH(@BeginDate)= 7 then 'Jul'  
 when MONTH(@BeginDate)= 8 then 'Aug'  
 when MONTH(@BeginDate)= 9 then 'Sept'  
 when MONTH(@BeginDate)= 10 then 'Oct'  
 when MONTH(@BeginDate)= 11 then 'Nov'  
 else 'Dec'  
 end                                                as MonthEN,  
 CASE  
 when DATEPART(DAY,@BeginDate)<=10 THEN 1  
 WHEN DATEPART(DAY,@BeginDate)>20 THEN 3  
 ELSE 2                
 END                                                as Ten,  
 CASE  
 when DATEPART(DAY,@BeginDate)<=10 THEN '上旬'  
 WHEN DATEPART(DAY,@BeginDate)>20 THEN '下旬'  
 ELSE '中旬'                
 END                                                as TenCN,  
 DATEPART(WEEK,@BeginDate)-1                          AS Dim_Week,  
 DATENAME(WEEKDAY,@BeginDate)                       as WeekDayCN,  
 case  
 when DATENAME(WEEKDAY,@BeginDate)= '星期一' then 'Mon'  
 when DATENAME(WEEKDAY,@BeginDate)= '星期二' then 'Tue'  
 when DATENAME(WEEKDAY,@BeginDate)= '星期三' then 'Wed'  
 when DATENAME(WEEKDAY,@BeginDate)= '星期四' then 'Thu'  
 when DATENAME(WEEKDAY,@BeginDate)= '星期五' then 'Fri' 
 when DATENAME(WEEKDAY,@BeginDate)= '星期六' then 'Sat'   
 else 'Sun'  
 end                                                 as WeekDayEN,
 
 CONVERT (varchar,@BeginDate,112) as Dim_Date1, 
 CONVERT(varchar(100), cast(@BeginDate as datetime), 101) as Dim_Date2, 
 0 as IsDayOff,
 '' as Event_Name
 SET @BeginDate=DATEADD(DAY,1,@BeginDate);  
 end;  
   
   select * from BI_Dim_Date
    --TRUNCATE TABLE BI_Dim_Date
    
    --drop table BI_Dim_Date
  
   --select GETDATE()--2014-11-24 12:48:47
   --Select CONVERT(varchar(100), '20140101', 101) --20140101
    --select convert(varchar(10),convert(varchar(10),'20140101',120),112)--20140101
    --select convert(varchar(10),'20140101',120) --20140101
 -- Select CONVERT(varchar(100), GETDATE(), 101)  11/24/2014
  --select   DATENAME(WEEKDAY,'20131230') as WeekDay
  --set language N'English'  --英文格式的星期几
  --select   DATENAME(WEEKDAY,'20131230') as WeekDay
  --set language N'简体中文'
  --select   DATENAME(WEEKDAY,'20131230') as WeekDay
   --Select CONVERT(varchar(100), '20140101', 101) --20140101
   --Select CONVERT(varchar(100), GETDATE(), 101)  --11/24/2014
  -- Select CONVERT(varchar(100), cast('20140101' as datetime), 101) --01/01/2014
   --sp_rename 'Dim_Date.Month','Dim_Month','column'    
   --sp_rename 'Dim_Date.Day','Dim_Day','column'  
   
   
     select * from BI_Dim_Date where DateKey>='20161229'
 update BI_Dim_Date set Dim_Week=52 where DateKey between '20131230' and '20140105'
 update BI_Dim_Date set Dim_Week=52 where DateKey between '20141229' and '20150104'
 update BI_Dim_Date set Dim_Week=52 where DateKey between '20151228' and '20160103'
  update BI_Dim_Date set Dim_Week=52 where DateKey='20170101'
 
 update BI_Dim_Date set Dim_Week=Dim_Week-1 where WeekDayEN='Sun'
 
  update BI_Dim_Date set Dim_Week=Dim_Week+1 where DateKey>='20170101'
--select SUBSTRING('20131230',3,2)
 --select SUBSTRING('20131230',7,2)
 
 --select 
--case  
-- when SUBSTRING('20131230',5,2)= 01 then 'Jan'  
 --when SUBSTRING('20131230',5,2)= 02 then 'Feb'  
 ---when SUBSTRING('20131230',5,2)= 03 then 'Mar'  
 --when SUBSTRING('20131230',5,2)= 04 then 'Apr'  
 --when SUBSTRING('20131230',5,2)= 05 then 'May'  
-- when SUBSTRING('20131230',5,2)= 06 then 'Jun'  
-- when SUBSTRING('20131230',5,2)= 07 then 'Jul'  
-- when SUBSTRING('20131230',5,2)= 08 then 'Aug'  
 --when SUBSTRING('20131230',5,2)= 09 then 'Sept'  
-- when SUBSTRING('20131230',5,2)= 10 then 'Oct'  
 --when SUBSTRING('20131230',5,2)= 11 then 'Nov'  
-- else 'Dec'  
 --end                                                as MonthEN,  
  
 
 -----转化日期的存储过程 30-Dec-13

--create proc ChangeDate
--@d1 nvarchar(10)
--as
--select a.d+'-'+b.MonthEN+'-'+c.y
--from (select SUBSTRING(@d1,7,2) as d)a,
--( select 
--case  
 --when SUBSTRING(@d1,5,2)= 01 then 'Jan'  
-- when SUBSTRING(@d1,5,2)= 02 then 'Feb'  
 --when SUBSTRING(@d1,5,2)= 03 then 'Mar'  
-- when SUBSTRING(@d1,5,2)= 04 then 'Apr'  
 --when SUBSTRING(@d1,5,2)= 05 then 'May'  
 --when SUBSTRING(@d1,5,2)= 06 then 'Jun'  
- --when SUBSTRING(@d1,5,2)= 07 then 'Jul'  
-- when SUBSTRING(@d1,5,2)= 08 then 'Aug'  
 --when SUBSTRING(@d1,5,2)= 09 then 'Sept'  
 --when SUBSTRING(@d1,5,2)= 10 then 'Oct'  
 --when SUBSTRING(@d1,5,2)= 11 then 'Nov'  
 --else 'Dec'  
 --end as MonthEN)b,
--(select SUBSTRING(@d1,3,2) as y) c

--exec ChangeDate @d1='20130220'



---自定义函数

CREATE FUNCTION ChangeIt(@dd nvarchar(10))
returns nvarchar(20)
AS begin
declare @result nvarchar(20)
SET  @result=(
select (a.d+'-'+b.MonthEN+'-'+c.y)
from (select SUBSTRING(@dd,9,2) AS d)a,
( select 
case  
 when SUBSTRING(@dd,6,2)= 01 then 'Jan'  
 when SUBSTRING(@dd,6,2)= 02 then 'Feb'  
 when SUBSTRING(@dd,6,2)= 03 then 'Mar'  
 when SUBSTRING(@dd,6,2)= 04 then 'Apr'  
 when SUBSTRING(@dd,6,2)= 05 then 'May'  
 when SUBSTRING(@dd,6,2)= 06 then 'Jun'  
 when SUBSTRING(@dd,6,2)= 07 then 'Jul'  
 when SUBSTRING(@dd,6,2)= 08 then 'Aug'  
 when SUBSTRING(@dd,6,2)= 09 then 'Sept'  
 when SUBSTRING(@dd,6,2)= 10 then 'Oct'  
 when SUBSTRING(@dd,6,2)= 11 then 'Nov'  
 else 'Dec'  
 end AS MonthEN)b,
 (select SUBSTRING(@dd,3,2) as y) c
 )
RETURN (@result)
END
 
 --drop function  ChangeIt
 SELECT [dbo].ChangeIt('20131201')
 
  SELECT dbo.ChangeIt('20131201')
  
 -- SET LANGUAGE us_english
--SELECT DATENAME(dd, '2010-5-31')+'-'+DATENAME(m, '2010-5-31')+'-'+DATENAME(yy, '2010-5-31')
  
  
  
  DECLARE @BeginDate DATE;  
SELECT @BeginDate = '20131230';  
WHILE @BeginDate<='20171231'  
BEGIN  
SELECT  
dbo.ChangeIt(@BeginDate) 
--@BeginDate
 SET @BeginDate=DATEADD(DAY,1,@BeginDate);  
end
  
  
  update BI_Dim_Date set Dim_Week=Dim_Week-1 where WeekDayEN='Sun'
     select * from BI_Dim_Date

连表更新

ALTER TABLE bi_dim_date ALTER COLUMN Event_name [varchar](100)  

update  bi_dim_date
SET Event_name= ename
from bi_dim_date,TempDayOff
WHERE DateKey=TempDayOff.Bdate

用case...when 更新

update BI_Dim_Date set MonthEN=(
 case    
 when Dim_Month= 1 then 'Jan'    
 when Dim_Month= 2 then 'Feb'    
 when Dim_Month= 3 then 'Mar'    
 when Dim_Month= 4 then 'Apr'    
 when Dim_Month= 5 then 'May'    
 when Dim_Month= 6 then 'Jun'    
 when Dim_Month= 7 then 'Jul'    
 when Dim_Month= 8 then 'Aug'    
 when Dim_Month= 9 then 'Sept'    
 when Dim_Month= 10 then 'Oct'    
 when Dim_Month= 11 then 'Nov'
  when Dim_Month= 12 then  'Dec'
 end
 

update BI_Dim_Date set MonthEN=(
 case    
 when Dim_Month= 1 then 'Jan'    
 when Dim_Month= 2 then 'Feb'    
 when Dim_Month= 3 then 'Mar'    
 when Dim_Month= 4 then 'Apr'    
 when Dim_Month= 5 then 'May'    
 when Dim_Month= 6 then 'Jun'    
 when Dim_Month= 7 then 'Jul'    
 when Dim_Month= 8 then 'Aug'    
 when Dim_Month= 9 then 'Sept'    
 when Dim_Month= 10 then 'Oct'    
 when Dim_Month= 11 then 'Nov'
 else  'Dec'
 end
 )
 
 update BI_Dim_Date set MonthEN=(
 case  Dim_Month  
 when 1 then 'Jan'    
 when 2 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