1.//一个月以内
dateDiff(day,main.CreateTime , (Select CONVERT(varchar(100), DateAdd(month,-1,gETdATE()), 23))) <= 0
select CONVERT(varchar(12),dateadd(dd,-day(getdate())+1,getdate()),111) + ' 00:00' --当前月的第一天
select CONVERT(varchar(12),DATEADD(m,1 ,dateadd(dd,-day(getdate())+1,getdate())),111) --下一月的第一天
2.//空值赋初值
ISNULL(filed,0) as filedNum
给字段做加、减法直接在字段上加减即可，注意先把空值转换为0
3.//当月
DATEDIFF(month,time1,GETDATE())=0
4.// ROW_NUMBER 返回结果集分区内行的序列号，每个分区的第一行从1开始
ROW_NUMBER() OVER(ORDER BY 字段 desc) >>生成整体连续的序号（唯一）
ROW_NUMBER() OVER(PARTITION BY 字段 desc) >>生成组内连续的序号
5.int转换varchar
SET @InsertMainID =CAST(@@MainID as varchar(100))
6.RowCount 和@@rowcount
https://www.cnblogs.com/Nina-piaoye/p/5979414.html
Note：@@rowcount是在系统内部变化的，取最近的一条查询数据总行数，所以如果要在While中使用，不是很可靠。
exec ('SELECT * FROM [dbo].[FlowLeaveAndOver]')
select @@Rowcount
set @mm = @@Rowcount
@mm 永远为1，获取的是select @@Rowcount这条查询的数据。
7.将查询出的多条数据拼接成一条字符（返回的是表格类型） ：stuff
select stuff((
select 'and '+CustomExpression
from FlowCheckConfiguration for xml path('')),1,1,'') as Expression
8.将某个字段赋值给一个变量：as List
select @StepCondition = CustomExpression from
(
select StepNo, CustomExpression from [dbo].[FlowCheckConfiguration] a where a.TableTreeEntityId =1106 and StepNo = @n
) as List
9.已知表名，从该表中查询数据
Exec('select * from '+ @SqlTableName +' where ID = ' + @FlowIdStr + ' and '+ @StepCondition)
note：字符拼接查询时，不要直接+INT类型的变量要转换一下：cast(@Id as varchar(10))
10.向自定义表中插入数据
--声明
Declare @ConfigDt as FlowCheckConfiguration_Dt
--插入
insert into @ConfigDt select * from FlowCheckConfiguration where TableTreeEntityId =1106
--使用
select * from @ConfigDt
delete @ConfigDt where StepNo =1
11 把某个字段(MainID )相同的数据按照另一个字段(UpdateTime )排序并添加一列编码：常用来去最新数据
select Name ,row_number() OVER(PARTITION BY MainID ORDER BY special.UpdateTime desc) as row_flg from [dbo].[表名] as special
where id >100
12. 判断一个值非空，if @temp is not null 不能写成 if @temp ！= null
13.游标
Declare @ComSign varchar(100) set @ComSign = '' -- 一定要赋初值 DECLARE @Vernier VARCHAR(100)--声明游标变量 DECLARE curVernier CURSOR FOR select ID from [dbo].[FlowCheckDepRole] where MainID IN (1,2,3,4,5,6) OPEN curVernier --打开游标 FETCH NEXT FROM curVernier INTO @Vernier --给游标变量赋值
BEGIN WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行执行成功 BEGIN print @Vernier
FETCH NEXT FROM curVernier INTO @Vernier --下一个游标变量赋值
end END
CLOSE curVernier --关闭游标
DEALLOCATE curVernier --释放游标
拼接字符串 查询总数并赋值返回值
exec (@SqlHeader + @Sql1 + @Sql2 + @SqlWhere2)
declare @TableCount TABLE
(
TotalNum int
)
insert into @TableCount (TotalNum) exec (@Sql)
select top 1 @ReturnValue = TotalNum from @TableCount
拼接字符串分页
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = @sqlStrHeader + @sqlStrLeftFlowStep + @sqlStrWhere + ' ORDER BY ' + @OrderByString + ' OFFSET '
+ CAST(@StartIndex AS NVARCHAR(100)) + ' ROWS FETCH NEXT ' + CAST(@SearchCount AS NVARCHAR(10)) + ' ROWS ONLY'
EXEC(@Sql)
拼接字符串 中使用Like 需注意
SET @SqlWhere2 += N' and emp.EmpName like ''%' + @EmpName + '%'''
日期之间的间断查询
select DateDiff(month,start,end), DAY(end)-DAY(start)
select DateDiff(month,'2018-6-30','2019-5-1'), DAY('2019-5-1')-DAY('2018-6-30')
-- -29 差29天11个月
select DateDiff(month,'2018-5-1','2019-6-30'), DAY('2019-6-30')-DAY('2018-5-1')
-- 29 13个月29天
拼接语句中赋值变量
declare @SqlStr nvarchar(1000) ,@FlowInitiateEmpID int , @FlowNo varchar(50)
set @SqlStr = N'select top 1 @a = FlowNo,@b= FlowInitiateEmpID from HR_FloLeaveOver where id =1'
exec sp_executesql @SqlStr,N'@a nvarchar(50) output,@b int output',@FlowNo output,@FlowInitiateEmpID output
print @FlowNo
print @FlowInitiateEmpID
20 获取最新更新时间的数据
select * , row_number() OVER(PARTITION BY ParentMenuID ORDER BY t.UpdateTime desc) as row_flg
from [dbo].[GB_FloVersionInfor] t
select ProjectID, ProjectMember=stuff((select '/'+ emp.EmpName FROM [dbo].[PJ_TeamMember] t
LEFT JOIN dbo.HR_Employee emp ON emp.EmpID = t.EmpID
WHERE ProjectID= tb.ProjectID for xml path('')), 1, 1, '')
from [dbo].[PJ_TeamMember] AS tb
group by ProjectID
21.sql 中的异常捕捉
Checked:
BEGIN TRY
PRINT 1;
END TRY
BEGIN CATCH
SET @ReturnType = 10;
SET @ReturnMsg = ERROR_MESSAGE();
END CATCH;
22.case
SELECT (case FlowStatus WHEN 1 then 4 WHEN 2 then 3 END ) AS FlowStatus FROM dbo.FlowExpense
23.
--获取日期部分
Select DATENAME(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate() +1)
--下周一
SELECT DATEADD(D,(select 9-(select DATEPART(weekday,getdate()))), GETDATE());
--本周周一
select dateadd(week, datediff(week,0,CONVERT(DATETIME,GETDATE(),120)-1),0)
--本月初一
SELECT DATENAME(year,GetDate())+'-'+Datename(MONTH,GetDate())+'-1'