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_NameSET @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 ChangeItSELECT [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)
--@BeginDateSET @BeginDate=DATEADD(DAY,1,@BeginDate);
endupdate 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'endupdate 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中的以‘,’分开的值**/selectid, a.name, SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key]
fromtest a,master..spt_values
wherenumber >=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本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！