循环
简单的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)