常用sql备忘

循环

简单的while循环比使用游标快很多，原因应该是不需要 FETCH 显示数据，还有不需要将数据select出来存到游标中

SELECT * FROM Users 


DECLARE @I INT
SET @I = (SELECT COUNT(*) FROM Users WHERE EmployeeID IS NULL)
WHILE @I > 0
BEGIN
	DECLARE @USERID varchar(100)
	SET @USERID = (SELECT TOP 1 UserID FROM Users WHERE EmployeeID IS NULL)
	UPDATE Users SET EmployeeID = 1 WHERE UserID = @USERID
	SET @I = @I - 1
END


DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT * FROM Users) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor ; --读取第一行数据
WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Users SET EmployeeID = 0 WHERE CURRENT OF My_Cursor; --更新
        --DELETE FROM dbo.MemberAccount WHERE CURRENT OF My_Cursor; --删除
        FETCH NEXT FROM My_Cursor; --读取下一行数据
    END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
GO

还可以循环字符串
比如字符串内以“，”分隔

WHILE(charIndex(',',@userList)>0)
	BEGIN
		SET @regCode = substring(@userList,1,charIndex(',',@userList)-1)
		-- do something
	   SET @userList = substring(@userList,charIndex(',',@userList)+1,len(@userList))
    END	

根据父ID查询子节点列表

with w_dept as
(
     select Department.deptid,DeptName,DeptParentID,IsCompany from Department
     where deptid='{0}' and DeptDelFlag = 0 
     union all
     select d.DeptID,d.DeptName,d.DeptParentID,d.IsCompany from Department d,w_dept B where d.DeptID=B.DeptParentID and d.DeptDelFlag = 0
)
select * from w_dept left join Sort on w_dept.DeptID = Sort.SortObjectID where IsCompany = 0 and SortType = 'Dept' order by SortLevel asc

将单条数据按照某字段排列成表

ALTER PROCEDURE [dbo].[LaborHourSelect] 
	@PageNo int ,
	@PageSize int,
	@Year varchar(50),
	@Type varchar(50) = ''
AS
BEGIN
	DECLARE @Num int = (@PageNo - 1)*@PageSize
	select distinct userCode,userName,project,
	max(case [MONTH] when '01' then laborHour else 0 end) m01,
	max(case [MONTH] when '02' then laborHour else 0 end) m02,
	max(case [MONTH] when '03' then laborHour else 0 end) m03,
	max(case [MONTH] when '04' then laborHour else 0 end) m04,
	max(case [MONTH] when '05' then laborHour else 0 end) m05,
	max(case [MONTH] when '06' then laborHour else 0 end) m06,
	max(case [MONTH] when '07' then laborHour else 0 end) m07,
	max(case [MONTH] when '08' then laborHour else 0 end) m08,
	max(case [MONTH] when '09' then laborHour else 0 end) m09,
	max(case [MONTH] when '10' then laborHour else 0 end) m10,
	max(case [MONTH] when '11' then laborHour else 0 end) m11,
	max(case [MONTH] when '12' then laborHour else 0 end) m12 into #1 
	from LaborHour WHERE ([YEAR] = @Year or @Year = '' or @Year = '请选择...' ) and userType != @Type and userType <> @Type AND delFlag = 0
	group by userCode,userName,project
	
	select * ,m01 + m02 + m03 + m04 + m05 + m06 + m07 + m08 + m09 + m10 + m11 + m12 as Total into #2 from #1
	drop table #1
	
	IF @PageNo = 0 AND @PageSize = 0
		SELECT 用户编码 = userCode,用户名称 = userName,项目名称=project, 一月 = m01,二月 = m02,三月 = m03,四月 = m04,五月 = m05,六月 = m06,七月 = m07,八月 = m08,九月 = m09,十月 = m10,十一月 = m11,十二月 = m12,合计 = Total
		FROM #2 ORDER BY userCode
	ELSE
		SELECT * FROM (SELECT ROW_NUMBER()OVER(ORDER BY userCode DESC)rownumber,* FROM #2)a WHERE rownumber > @Num AND rownumber <= (@Num + @PageSize)
	SELECT COUNT(*) FROM #2
	DROP TABLE #2
END

获取当前日期字符串

DECLARE @NowDay varchar(50) = Datename(year,GetDate()) + ‘-’ + Datename(month,GetDate())+’-’ + RIGHT(‘0’+DATENAME(DD,GetDate()),2)

注意最后day的写法，Datename(DD,GetDate())这样获取到的可能是一位数，所以需要先把datetime转化成字符串，再截取

查询区间日期内双休日

DECLARE @t TABLE(date0 DATETIME)
DECLARE @st DATETIME,@et DATETIME
SET @st=‘2007-01-01’
SET @et=‘2008-01-01’
WHILE @st < @et
BEGIN
INSERT INTO @t VALUES(@st)
SELECT @st=DATEADD(DAY,1,@st)
END
SELECT date0,DATENAME(weekday,date0) FROM @t WHERE DATEPART(weekday,date0+@@DATEFIRST-1) IN (6,7)