SQLServer常用运维SQL整理（2）

1. SQL 遇到以零作除数错误——用NULLIF()函数处理为0的分母

with tb1 as(select 1 a ,0 b)
select ISNULL((a / NULLIF(b,0)) ,0) AB  from tb1

说明：

NULLIF(b,0) ，是判断 b 是不是等于 0，如果 b = 0，则 a / b 这个算式返回 NULL值

ISNULL(null,0)，就更简单了，如果 NULLIF 返回 NULL值，那就直接给个默认值 0

当为分母为0就不会报错了.

2. SQL查连续登录3天以上的用户

create table tmptablee(id int,rq datetime);

insert tmptablee values(1,'2022-07-01') ;
insert tmptablee values(1,'2022-07-01') ;
insert tmptablee values(1,'2022-07-02') ;
insert tmptablee values(2,'2022-07-02') ;
insert tmptablee values(2,'2022-07-02') ;
insert tmptablee values(2,'2022-07-03') ;
insert tmptablee values(1,'2022-07-03') ;
insert tmptablee values(1,'2022-07-04') ;
insert tmptablee values(2,'2022-07-04') ;
insert tmptablee values(2,'2022-07-05');
insert tmptablee values(2,'2022-07-06');
insert tmptablee values(2,'2022-07-07');
insert tmptablee values(2,'2022-07-21');
insert tmptablee values(2,'2022-07-22');
insert tmptablee values(2,'2022-07-23');
insert tmptablee values(3,'2022-07-24');
insert tmptablee values(3,'2022-07-25');
insert tmptablee values(3,'2022-07-26') ;
insert tmptablee values(3,'2022-07-27') ;
insert tmptablee values(3,'2022-07-28') ;
insert tmptablee values(1,'2022-08-03') ;


-- 第一步：用户登录日期去重
select distinct id,convert(date,rq) as rq from tmptablee

--第二步：用row_number() over()函数计数
select *,row_number() over(partition by id order by rq) as cum from (select distinct id,convert(date,rq) as rq from tmptablee)a

--第三步：日期减去计数值得到开始时期
select *,dateadd(day,-cum+1,convert(date,rq)) as 开始时期 from (
	select *,row_number() over(partition by id order by rq) as cum from (
		select distinct id,convert(date,rq) as rq from tmptablee
	)a
)b;

--第四步：根据id和开始时期分组并计算总和，大于等于3的即为连续登陆3天的用户
select id,开始时期,count(*) from (
	select *,dateadd(day,-cum +1,convert(date,rq)) as 开始时期 from (
		select *,row_number() over(partition by id order by rq) as cum from (
			select distinct id,convert(date,rq) as rq from tmptablee
		)a
	)b
)c 
group by id,开始时期 having count(*)>=3;

3. SQL Server 多行合并成一行与一行拆分成多行

​

 建表及插入测试数据：

-- 创建测试用表rows_to_row
create table rows_to_row(
name char(5) not null default '',
hobby varchar(20) not null default ''
)
 
-- 向测试表添加数据
insert into rows_to_row 
values
('张三','aaa'),
('张三','sss'),
('张三','ddd'),
('李四','ggg'),
('李四','kkk'),
('李四','jjj');

实现合并效果的SQL语句：

select  name ,
        hobby = ( stuff((select ',' + hobby from rows_to_row where name = Test.name for xml path('')), 1, 1, '') )
from rows_to_row as Test 
group by name

4. SQL 保留两位小数+四舍五入

select cast(362333315*1.3/10000 as numeric(18,5))
select convert(decimal(18, 5), 362333315*1.3/10000)

5. SQL SERVER 获取汉字的首拼音字母的函数

Create Function [fn_GetPySzm](@str nvarchar(4000))
	returns nvarchar(4000)
	with encryption as

begin
declare @word nchar(1), @PY nvarchar(4000)
set @PY=''
while len(@str)>0
	begin
		set @word=left(@str, 1)
		set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
		then (select top 1 PY from (
		select 'A' PY, N'驁' word
		union all select 'B', N'簿'
		union all select 'C', N'錯'
		union all select 'D', N'鵽'
		union all select 'E', N'樲'
		union all select 'F', N'鰒'
		union all select 'G', N'腂'
		union all select 'H', N'夻'
		union all select 'J', N'攈'
		union all select 'K', N'穒'
		union all select 'L', N'鱳'
		union all select 'M', N'旀'
		union all select 'N', N'桛'
		union all select 'O', N'漚'
		union all select 'P', N'曝'
		union all select 'Q', N'囕'
		union all select 'R', N'鶸'
		union all select 'S', N'蜶'
		union all select 'T', N'籜'
		union all select 'W', N'鶩'
		union all select 'X', N'鑂'
		union all select 'Y', N'韻'
		union all select 'Z', N'咗'
		) T
		where word>=@word collate Chinese_PRC_CS_AS_KS_WS
		order by PY ASC) else @word end)
		set @str=right(@str,len(@str)-1)
	end
	return UPPER(@PY)
end

select dbo.fn_GetPySzm('李德春'),'李德春'

select dbo.fn_GetPySzm(UserName) as PY,UserName from PUSR
where Password like '123456' and  UserName not like '[A-Z]%'

​

6. SQL查下个月最后一天。真实案例：公司需将自动生成的发票默认记账日期设为下个月底，支持跨年。如今天为2022-12-03，取下个月底即：2023-01-31。

select convert(datetime,convert(varchar(50),dateAdd(month,2,dateAdd(day,1-datepart(day,GETDATE()),GETDATE()))-1,23)) as DocDueDate

7. 查物料近期最低采购价

if Object_id('Tempdb..#temp1') is not null drop table #temp1
create table #temp1(ItemNumber varchar(10),PurchDate date,PurchPrice decimal(10,2))
insert into #temp1(ItemNumber,PurchDate,PurchPrice)
select 'Item01', '2016-1-8',3.33 union all
select 'Item01', '2016-5-8',2.22 union all
select 'Item01', '2016-3-8',1.11 union all
select 'Item02', '2016-3-9',4.44 union all
select 'Item02', '2016-5-9',5.55 union all
select 'Item02', '2016-1-9',6.66 union all
select 'Item03', '2016-1-7',9.99 union all
select 'Item03', '2016-3-7',8.88 union all
select 'Item03', '2016-3-7',7.77



select *
from (
    select ItemNumber,PurchDate,PurchPrice,
    row_number() over(partition by ItemNumber order by ItemNumber asc,PurchPrice asc,PurchDate desc) row_num
    from #temp1) t1
where t1.row_num=1


select * from #temp1 order by ItemNumber asc,PurchPrice asc,PurchDate desc

​

 8.通过字段名找表，如：name like '%rq%' ，找数据库中含rq的所有表

select name from sys.tables 
where object_id in (select object_id from sys.all_columns where name like 'rq')

 9. 查MSSQL最近执行的50个SQL语句

SELECT TOP 50
 (total_logical_reads + total_logical_writes) AS total_logical_io,
 (total_logical_reads / execution_count) AS avg_logical_reads,
(total_logical_writes / execution_count) AS avg_logical_writes,
(total_physical_reads / execution_count) AS avg_phys_reads,
substring (st.text,
(qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset WHEN -1
 THEN datalength (st.text)
ELSE qs.statement_end_offset END
 - qs.statement_start_offset)/ 2)+ 1)
 AS statement_text,
*
FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS st
-- where text like '%2022-08-03%'
ORDER BY last_execution_time DESC 

 10. 跨库查询(OpenDataSource)

select * from opendatasource('SQLOLEDB','server=192.168.1.IP地址;uid=sa用户名;pwd=PASSWORD密码;database=test数据库名').test.dbo.dln1表名