SQLServer 常用sql2，sqlserversql2

SQLServer 常用sql2，sqlserversql2

--建立  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-30CONVERT(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 begindeclare @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_dateSET Event_name= enamefrom bi_dim_date,TempDayOffWHERE 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 nulldrop table test go create table test (     id int,     name varchar(10),     [key] varchar(20) ) go --插入資料insert test select 1,'lisa','li,is,sa' union allselect 2,'sophia','ab,cd,ef' union allselect 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 tinner 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 tinner join cte as c on t.ID=c.PID)select * from cte