SQL Server常用查询（长期更新）

常用SQL查询

1. 查询数据库中哪些表为空

-- 查询UD开头的表中为空的表
select t.name as table_name,s.name as schema_name,sum(p.rows) as total_rows
from sys.tables as t
join sys.schemas s on (t.schema_id = s.schema_id)
join sys.partitions p on (t.object_id = p.object_id)
where p.index_id in (0,1)
group by t.name,s.name
having sum(p.rows) = 0 and t.name like 'UD%'

2. 游标的使用案例

if CURSOR_STATUS('global','cursor_tag') = -3                      -- 判断游标是否存在
begin
	declare cursor_tag Cursor FOR                                 -- 创建游标
	select jo2.JobNum,jo2.OprSeq 
	from Erp.JobOper jo2 
	where jo2.OpCode like 'OP05%'
	and jo2.JobComplete = 0
	and jo2.JobNum in (
		select jo.JobNum 
		from Erp.JobOper jo
		where jo.OpCode = 'OP0403' 
		and jo.JobComplete = 0
		and (select Top 1 jo3.OpCode from Erp.JobOper jo3 where jo3.OpComplete = 1 and jo3.JobNum = jo.JobNum order by jo3.OprSeq desc) like 'OP05%'
	)
	and jo2.OprSeq < (
		select max(jo4.OprSeq) from Erp.JobOper jo4
		where jo4.OpCode = 'OP0403'
		and jo4.JobComplete = 0
		and jo4.JobNum = jo2.JobNum
	)
	order by jo2.JobNum,jo2.OprSeq
end

declare @varCursor_jobnum varchar(20);                            -- 定义游标参数，与游标查询的字段一致
declare @varCursor_seq int;

IF OBJECT_ID('tempdb..#T1') is not null                           -- 判断临时表是否存在
begin
	drop table #T1                                                -- 删除临时表
end
create table #T1(                                                 -- 创建临时表，临时表字段类型与数量需要与下方查询语句中的一致
	JobNum varchar(20),
	JobQty int,
	OprSeq int,
	OpCode varchar(20),
	OpDesc varchar(50),
	PartNum varchar(30),
	PartDesc varchar(100),
	SubSeq int,
	VendorName varchar(50),
	PONum varchar(20)
	)

open cursor_tag                                                  -- 打开游标
fetch next from cursor_tag into @varCursor_jobnum,@varCursor_seq -- 将游标数据存入定义的游标变量
	while(@@FETCH_STATUS = 0)                                    -- 循环查询
	begin
	insert #T1                                                   -- 将查询数据插入至临时表
	select Top 1 jo1.JobNum,jh.ProdQty,jo1.OprSeq,jo1.OpCode,jo1.OpDesc,jh.PartNum,jh.PartDescription
	,@varCursor_seq as "Seq"
	,"VendorName" = 
		(
		select v.Name
		from Erp.JobOper jj
		left join Erp.Vendor v 
		on jj.VendorNum = v.VendorNum
		where jj.JobNum = jo1.JobNum 
		and jj.OprSeq = @varCursor_seq)
	,pp.PONum
	from Erp.JobOper jo1
	left join Erp.JobHead jh on jh.JobNum = jo1.JobNum
	left join (
		select pr.JobNum,pr.PONum from Erp.PORel pr where pr.JobNum = @varCursor_jobnum and pr.JobSeq = @varCursor_seq
	) as pp on pp.JobNum = jo1.JobNum
	where jo1.JobComplete = 0 
	and jo1.JobNum = @varCursor_jobnum
	and jo1.OpCode like 'OP0403'
	and (select jo2.OpCode from Erp.JobOper jo2 where jo2.JobNum = jo1.JobNum and jo2.OprSeq = @varCursor_seq) like 'OP05%'
	and jo1.OprSeq > @varCursor_seq
	order by jo1.JobNum
	fetch next from cursor_tag into @varCursor_jobnum,@varCursor_seq  -- 将下一个游标数据存入定义的游标变量
	end		
close cursor_tag                                                  -- 关闭游标（有打开就有关闭） 

select * from #T1                                                 -- 查询临时表T1

deallocate cursor_tag                                             -- 释放游标

drop table #T1                                                    -- 删除临时表

3. 时间转换查询

-- 方法一：
DECLARE @seconds INT = 28474;
DECLARE @minutes INT = @seconds / 60;

DECLARE @hours INT = @minutes / 60;

DECLARE @timeTime TIME = DATEADD(SECOND, @seconds, '2023-10-26');

SELECT DATEPART(HOUR, @timeTime) AS Hour, DATEPART(MINUTE, @timeTime) AS Minute, DATEPART(SECOND, @timeTime) AS Second;


-- 方法二：
DECLARE @minutes INT = 36811710;

DECLARE @hours INT = @minutes / 60;

DECLARE @seconds INT = @minutes % 60;

DECLARE @timeTime TIME = CAST(@hours AS VARCHAR(2)) + ':' + CAST(@seconds AS VARCHAR(2));

SELECT @timeTime;