首页 > 代码库 > SQLServer 常用sql2
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
SQLServer 常用sql2
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。