sqlserver

最新推荐文章于 2022-11-03 09:55:00 发布

yzhldk

最新推荐文章于 2022-11-03 09:55:00 发布

阅读量216

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/yzhldk/article/details/79793926

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.row_number() over (order by month,week) --序号函数

over

2.rank() over (order by month,week) --排名函数

3.如何将查询出的多行数据拼成一个字符串返回

--------
SELECT  tb.ExamineTableID ,
        ExamineTableName ,
        STUFF(( SELECT  ',' + ObjectID
                FROM    SYKH_ExamineObjectOrganization obj
                WHERE   tb.ExamineTableID = obj.ExamineTableID
              FOR
                XML PATH('')
              ), 1, 1, '') AS UnitID ,
        STUFF(( SELECT  ',' + ObjectName
                FROM    SYKH_ExamineObjectOrganization obj
                WHERE   tb.ExamineTableID = obj.ExamineTableID
              FOR
                XML PATH('')
              ), 1, 1, '') AS Unit ,
        gl.GuideLineID PMGuidLineID ,
        gl.Name AS PMGuidLineNAME,objtype.ParentID UnitTypeID,objtype.B0101 UnitType
FROM    SYKH_ExamineTable tb
        LEFT JOIN dbo.SYKH_ExamineGuideLine gl ON tb.ExamineTableID = gl.ExamineTableID
                                                  AND isPMColumn = 1
       LEFT JOIN  (SELECT obj.ExamineTableID,b.ParentID,p.B0101 FROM PEA_View_B01 b LEFT JOIN PEA_View_B01 p ON b.ParentID=p.B00 LEFT JOIN SYKH_ExamineObjectOrganization obj ON b.B00=obj.ObjectID  GROUP BY obj.ExamineTableID,b.ParentID,p.B0101 ) objtype ON   objtype.ExamineTableID=tb.ExamineTableID
WHERE tb.ExamineTableID='$ExamineTableID'

另一种方式：

-----通过 select 累加
DECLARE @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(a0101) FROM A01_2011 
where a0101 like '%马%'
GROUP BY a0101
SELECT @sql_col

4.连续输出多年的数据

SELECT
    number AS dmcod,number AS dmcpt
FROM
    master..spt_values 
WHERE
    type = 'p'
AND number between 2015 and DateName(year,GetDate())

5.曾经写的一个关于生成日期-年，月，周数据的存储过程

USE [ExamineTJSFJ]
GO
/****** Object:  StoredProcedure [dbo].[PSKH_DateInstance_Create]    Script Date: 05/10/2018 10:32:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PSKH_DateInstance_Create](
	@paraYear varchar(100)
)
AS
BEGIN
    DECLARE @Todayday datetime
    DECLARE @Period int
    DECLARE @StartTime datetime
    DECLARE @EndTime DATETIME
    DECLARE @YearStr VARCHAR(200)
    DECLARE @IsRn VARCHAR(100)--是否是闰年

    
    
    SET @Todayday = CONVERT(DATETIME,@paraYear)
	SET @Period = YEAR(@Todayday)--周期
	SET @YearStr = CONVERT(VARCHAR(10),YEAR(@Todayday))
	DELETE FROM dbo.PSKH_DateInstance where year = @Period--删除数据，防止重复插入
    
    --是否是闰年
    IF @Period%4=0 AND @Period%100<>0 OR @Period%400=0
		SET @IsRn = 'true'
	ELSE
		SET @IsRn = 'false'
    
	--存储年度信息
	SET @StartTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'1-1',20)
	SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'12-31',20)
    
	INSERT INTO dbo.PSKH_DateInstance(
			  DateInstanceID,YEAR,HalfYear,Quarter,Month,Week,Day,DateName,StartTime,EndTime,CreateTime,DateType)
	VALUES(NEWID(),@Period,null,null,null,null,null,'年度',@StartTime,@EndTime,GETDATE(),'1') 
    
    --存储上半年、下半年信息
	SET @StartTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'1-1',20)
	SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'6-30',20)
    
	INSERT INTO dbo.PSKH_DateInstance(
			  DateInstanceID,YEAR,HalfYear,Quarter,Month,Week,Day,DateName,StartTime,EndTime,CreateTime,DateType)
	VALUES(NEWID(),@Period,1,null,null,null,null,'上半年',@StartTime,@EndTime,GETDATE(),'2')
	
	SET @StartTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'7-1',20)
	SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'12-31',20)
    
	INSERT INTO dbo.PSKH_DateInstance(
			  DateInstanceID,YEAR,HalfYear,Quarter,Month,Week,Day,DateName,StartTime,EndTime,CreateTime,DateType)
	VALUES(NEWID(),@Period,2,null,null,null,null,'下半年',@StartTime,@EndTime,GETDATE(),'2')
	
	--存储季度信息（循环执行）  
	DECLARE @i int
	DECLARE @MonthDayCount int --每月有多少天
	SET @i = 1
	WHILE @i < 5
		BEGIN
		    IF @i = 1 
				BEGIN
					SET @StartTime =convert(datetime,CONVERT(char(5),@Todayday,120)+'1-1',20)
					SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'3-31',20)
				END
			ELSE IF @i =2 
				BEGIN
					SET @StartTime =convert(datetime,CONVERT(char(5),@Todayday,120)+'4-1',20)
					SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'6-30',20)
				END
			ELSE IF @i =3 
				BEGIN
					SET @StartTime =convert(datetime,CONVERT(char(5),@Todayday,120)+'7-1',20)
					SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'9-30',20)
				END
			ELSE IF @i =4 
				BEGIN
					SET @StartTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'10-1',20)
					SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'12-31',20)
				END
			
			INSERT INTO dbo.PSKH_DateInstance(
			  DateInstanceID,YEAR,HalfYear,Quarter,Month,Week,Day,DateName,StartTime,EndTime,CreateTime,DateType)
	           VALUES(NEWID(),@Period,null,@i,null,null,null,'第'+convert(varchar(10),@i)+'季度',@StartTime,@EndTime,GETDATE(),3)
			SET @i = @i +1
		END
	--存储月度信息（循环执行）
	DECLARE @j int
	DECLARE @k int
	SET @j = 1
	WHILE @j < 13
		BEGIN
		     SET @StartTime =convert(datetime,CONVERT(char(5),@Todayday,120)+CONVERT(varchar(10),@j)+'-1',20)
			 IF @j = 1 or @j = 3 or @j = 5 or @j = 7 or @j = 8 or @j = 10 or @j = 12  
				BEGIN
					SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+CONVERT(varchar(10),@j)+'-31',20)
					SET @MonthDayCount = 31
				END
			 ELSE IF @j = 4 or @j = 6 or @j = 9 or @j = 11
				 BEGIN
					SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+CONVERT(varchar(10),@j)+'-30',20)
					SET @MonthDayCount = 30
				 END
			 ELSE IF @j = 2
			     BEGIN
					IF @IsRn = 'true'
						BEGIN 
							SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+CONVERT(varchar(10),@j)+'-29',20)
							SET @MonthDayCount = 29
						END
					ELSE 
						BEGIN
							SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+CONVERT(varchar(10),@j)+'-28',20)
							SET @MonthDayCount = 28
					    END
				 END
			  --插入月的信息
			  INSERT INTO dbo.PSKH_DateInstance(
			       DateInstanceID,YEAR,HalfYear,Quarter,Month,Week,Day,DateName,StartTime,EndTime,CreateTime,DateType)
	             VALUES(NEWID(),@Period,null,null,@j,null,null,'第'+convert(varchar(10),@j)+'月',@StartTime,@EndTime,GETDATE(),4)
	          SET @k = 1
	          WHILE  @k<=@MonthDayCount
					BEGIN
					      --插入天的信息
					      INSERT INTO dbo.PSKH_DateInstance(
							  DateInstanceID,YEAR,HalfYear,Quarter,Month,Week,Day,DateName,StartTime,EndTime,CreateTime,DateType)
						    VALUES(NEWID(),@Period,null,null,@j,null,@k,'第'+convert(varchar(10),@j)+'月，第'+convert(varchar(10),@k)+'天',@StartTime,@StartTime,GETDATE(),6)
						  --每个月的每一天；
						  
						  SET @StartTime = DATEADD(day,1,@StartTime)
						  SET @k = @k+1
					END
			  SET @j = @j +1
		END
	--存储周信息（循环执行）
	DECLARE @v_month_cur int --存放月
	DECLARE @v_week int --存放周
	DECLARE @WeekBegin DATETIME--存放周开始的天
	DECLARE @WeekEnd DATETIME--存放周结束的天
	DECLARE @LastFirdayDay DATETIME -- 存放上周五的日期
	DECLARE @Jd int
		
	SET @StartTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'1-1',20)
	SET @EndTime = convert(datetime,CONVERT(char(5),@Todayday,120)+'12-31',20)
	SET DATEFIRST 7 --设置星期天为一周的第一天
    SET @v_week = 1 --默认第几周是从1开始
    
	WHILE  @StartTime<=@EndTime
		BEGIN
			IF (DATEPART(weekday,@StartTime)=6)--如果是周五
			    BEGIN
					SET @v_month_cur = MONTH(@StartTime)
					SET @WeekBegin = DATEADD(day,-5,@StartTime)
					SET @Jd = convert(varchar,datename(qq,@StartTime))
					SET @WeekEnd = DATEADD(day,1,@StartTime)
					SET @LastFirdayDay = DATEADD(day,-7,@StartTime)
					--判断下这周五的月份和上周五的月份是否一样，如果一样则要加1，如果不一样，则需要给@v_week设置为1
					IF(MONTH(@StartTime)<>MONTH(@LastFirdayDay))
						SET @v_week = 1
					ELSE 
						SET @v_week = @v_week+1
					INSERT INTO dbo.PSKH_DateInstance(
							  DateInstanceID,YEAR,HalfYear,Quarter,Month,Week,Day,DateName,StartTime,EndTime,CreateTime,DateType)
						    VALUES(NEWID(),@Period,null,@Jd,@v_month_cur,@v_week,null,'第'+convert(varchar(10),@v_month_cur)+'月，第'+convert(varchar(10),@v_week)+'周',@WeekBegin,@WeekEnd,GETDATE(),5)
				END
			SET @StartTime = DATEADD(day,1,@StartTime)
	    END
END

6.sqlserver 恢复

拿到之前备份的文件.bak文件；

1.先把备份文件拷到服务器地址上，\\$路径下；或者直接复制粘贴；

2.选择数据库，右键点击，选任务->还原->数据库；

3.有设备，文件，选项三项需要设置；设备，选择备份的那个.bak文件；

文件，修改路径下，要不默认都存到了C盘下；

4.选项，选择覆盖原来的数据库；关闭数据库连接后再恢复需要选择；