SQL语句收集

一、for xml path 会截断字符串的解决办法

SELECT STUFF((select distinct(表字段) ',' from 表 where CHARINDEX('Offline',类别)=0 for xml path('')),1,0,'')

STUFF函数会把完整的字符串复制出来。

二、union 与 union all

select * from 表A
union 
select * from 表B

select 姓名 from 表A
union
select 姓名 from 表B

select 姓名 from 表A
union all
select 姓名 from 表B

union 连接会把重复的数据排除掉，上面语句一和二结果可能会不同。如果重复的保留一定用union all，否则统计时造成出入！

三、查找某一天

--系统认为每周第一天从星期日开始，但国人却认同周一为第一天
select DATEDIFF(wk,'2020-03-08','2020-03-09')=0 
--上周一
select dateadd(wk, datediff(wk,0,DATEADD(wk,-1, getdate())), 0) 
--上周末
select DATEADD(day,6, dateadd(wk, datediff(wk,0,DATEADD(wk,-1, getdate())), 0))


declare @dt datetime
set @dt=CONVERT(datetime,'2020-5-28')
--周一
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, CONVERT(DATETIME, @dt, 120) - 1), 0)
--周日
SELECT DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(WEEK, 0, CONVERT(DATETIME, @dt, 120) - 1), 0))

四、行转列

select * from DailyIncome ----第一步
pivot 
(
sum (IncomeAmount) ----第三步
for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]) ---第二步
) as AvgIncomePerDay

来解释下，要想用好Pivot函数，应该理解代码注释中的这几步。
第一步：肯定是要明白数据源了，这里是DailyIncome
第二步：要明白要想让哪一列的值进行转列，并指定新的列集合
第三步：要明白对于这新的列要求那些值

注意：对于除指定转列之外相同的行都会汇总到一列

转自：https://www.jianshu.com/p/8f929264995e

五、事务

create table Users
(
	AutoID int,
	UserName varchar(50)
)

go

begin tran
begin try
	declare @sql varchar(max)
	set @sql='insert into Users select 1,''jsonlee'''
	exec(@sql)
	
	set @sql='insert into Users select ''err'',''bb'''
	exec(@sql)
end try
begin catch
	if(@@TRANCOUNT>0)
	rollback tran
end catch
if(@@TRANCOUNT>0)
commit tran

有一条执行出错即回滚，如果没有事务的话，第一条会成功，第二抛出错误信息。

注意在事务代码中不要在提交和回滚前用return，否则会报错如：“EXECUTE 后的事务计数指示BEGIN和COMMIT语句的数目不匹配，上一计数=0，当前计数=1”，这是因为用了return，以下的代码不会执行，跳过了提交或者回滚，导致错误！

6、求周次

--求周次 如果当前周跨年，就算是新年的第一周
if OBJECT_ID('f_custom_GetWeeks') is not null
drop function f_custom_GetWeeks
go

CREATE function f_custom_GetWeeks(@dt datetime) 
returns int 
as 
begin 	
	--使用方法： select dbo.f_custom_GetWeeks(cast('2020-12-31' as datetime))
	declare @weeks int
	declare @dateFirstDayOfWeek datetime 
	declare @dateLastDayOfWeek datetime 
	--周一
	set @dateFirstDayOfWeek=DATEADD(WEEK, DATEDIFF(WEEK, 0, CONVERT(DATETIME, @dt, 120) - 1), 0)
	--周日
	set @dateLastDayOfWeek=DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(WEEK, 0, CONVERT(DATETIME, @dt, 120) - 1), 0))
	
	if(YEAR(@dateFirstDayOfWeek)<>YEAR(@dateLastDayOfWeek))
		set @weeks = 1 --跨年的周认为是新年的第一周
	else
		set @weeks = datepart(wk,@dt)
		
	return @weeks
end

7、on与where

在多表连接如inner join、left join、right join时

on 表示根据条件进行笛卡尔积运算，生成一个新的临时表
where 表示对临时表进行筛选

因此，如果where是仅针对原表进行筛选的话，尽量写在on连接之前！

8、分区函数 partition

分区分区，就是把某个字段做为一个区间，与分组group by 不同。分组多用于统计

select * from 
(
	select [name],addTime,age, 
	row_number() over(partition by [name] order by addTime) as sortfield
	from students
) as tt where sortfield=1

按学生名称分区，并按时间顺序，找出最早的分数，结果如下：

9、PATINDEX ( '%pattern%' , expression )

--查找中文起始位置
declare @str nvarchar(100)='12345678今天天所不错！！！'
select PATINDEX('%[吖-座]%',@str)  --9
select SUBSTRING(@str,0,PATINDEX('%[吖-座]%',@str)) --12345678

10、临时表和全局临时表

临时表#temp在执行中有效，##temp在会话中有效。所有以存储过程中#temp不需要删除，存储过程执行完后自动删除，而##temp需要手动判断并删除

if object_id('tempdb..##temp_global') is not null
   drop table ##temp_global

11、在存储过程中抛出异常

;throw 99001, '参数 @wost_code_str 没有值，没有给产品类型配置工序信息', 1;

SQL语句收集

发表评论