SQLServer 常用sql2

发布时间：2020-12-12 12:42:01 所属栏目：MsSql教程来源：网络整理

导读：--新建 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,[

--新建  
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),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,@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,@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,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),120),112)--20140101
    --select convert(varchar(10),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),101) --20140101
   --Select CONVERT(varchar(100),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',2)= 02 then 'Feb'  
 ---when SUBSTRING('20131230',2)= 03 then 'Mar'  
 --when SUBSTRING('20131230',2)= 04 then 'Apr'  
 --when SUBSTRING('20131230',2)= 05 then 'May'  
-- when SUBSTRING('20131230',2)= 06 then 'Jun'  
-- when SUBSTRING('20131230',2)= 07 then 'Jul'  
-- when SUBSTRING('20131230',2)= 08 then 'Aug'  
 --when SUBSTRING('20131230',2)= 09 then 'Sept'  
-- when SUBSTRING('20131230',2)= 10 then 'Oct'  
 --when SUBSTRING('20131230',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,2) as d)a,--( select 
--case  
 --when SUBSTRING(@d1,2)= 01 then 'Jan'  
-- when SUBSTRING(@d1,2)= 02 then 'Feb'  
 --when SUBSTRING(@d1,2)= 03 then 'Mar'  
-- when SUBSTRING(@d1,2)= 04 then 'Apr'  
 --when SUBSTRING(@d1,2)= 05 then 'May'  
 --when SUBSTRING(@d1,2)= 06 then 'Jun'  
- --when SUBSTRING(@d1,2)= 07 then 'Jul'  
-- when SUBSTRING(@d1,2)= 08 then 'Aug'  
 --when SUBSTRING(@d1,2)= 09 then 'Sept'  
 --when SUBSTRING(@d1,2)= 10 then 'Oct'  
 --when SUBSTRING(@d1,2)= 11 then 'Nov'  
 --else 'Dec'  
 --end as MonthEN)b,--(select SUBSTRING(@d1,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,2)= 02 then 'Feb'  
 when SUBSTRING(@dd,2)= 03 then 'Mar'  
 when SUBSTRING(@dd,2)= 04 then 'Apr'  
 when SUBSTRING(@dd,2)= 05 then 'May'  
 when SUBSTRING(@dd,2)= 06 then 'Jun'  
 when SUBSTRING(@dd,2)= 07 then 'Jul'  
 when SUBSTRING(@dd,2)= 08 then 'Aug'  
 when SUBSTRING(@dd,2)= 09 then 'Sept'  
 when SUBSTRING(@dd,2)= 10 then 'Oct'  
 when SUBSTRING(@dd,2)= 11 then 'Nov'  
 else 'Dec'  
 end AS MonthEN)b,(select SUBSTRING(@dd,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,@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,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],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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!