实战笔记--SQL Server临时表、With As、Row_Number和游标的综合使用

学更好的别人，

做更好的自己。

——《微卡智享》

本文长度为3314字，预计阅读9分钟

前言

做运维的同学都应该了解，现在运维，特别是查数据时，直接用SQL写报表要比开发个程序要快的多，这篇也是因为在客户现场临时写的报表做一个笔记。报表是写一个药品的明细账目录，也是结合了临时表，With As、Row_Number的用法及游标完成。

项目背景

因为客户项目急着上线，部分细节东西还没有全部打通，正好到了月末，需要进行一次对账，大部分数据倒是没有问题，针对个别的药品需要明细账的对比，所以也是直接在SQL里写了一个明细账的报表，用于对接查询使用。

 

#	报表设计思路
1	查询药品的补药，取药及盘点的数据，按对应科室和时间排序存放到临时表里
2	将排好序的表每行进行结余数量的计算
3	查询数据

 

第一条中因为药品的基本信息及在对应的药格查询比较繁琐，所以用到了With As的查询，然后再实现的存放到中间表。

代码实现

微卡智享

01

判断缓存表是否存在并删除

--删除缓存临时表
exec('if exists(select * from tempdb..sysobjects where name=''##tmpdata'') drop table tempdb..##tmpdata ')

临时表中我们用了##表名，这样的临时表是创建在tempdb的数据库中，如果关掉当前查询分析器后，此表也会自动清除，上面我们直接用exec加判断表是否存在，主要是为了可以反复执行时不会出现问题。

02

获取药品库存信息

declare @gcode varchar(30)
declare @start_cabinetid integer, @end_cabinetid integer
--设置药品编码
select @gcode='1702038SZ'
--设置开始和结束的病区ID
select @start_cabinetid=11
select @end_cabinetid=17




--获取药品库存
;with ygkc as (
select b.*,a.gcode,a.gname,a.spn,a.unit,a.pack,c.drug_code as cabinetcode,c.drug_name as cabinetname
from t_drugsInfo b, t_drugs a,t_drug_cabinet c  where a.drugs_id_yg=b.drugs_id and a.drug_cabinet_id 
=b.drug_cabinet_id and a.drug_cabinet_id=c.drug_cabinet_id
and c.drug_cabinet_id between @start_cabinetid and @end_cabinetid 
and a.gcode = @gcode
)

定义了变量用于修改要查询的药品编码，因为这个表查询出来需要关联三个表，而且下面的补药、取药及盘点数据都要和库存表进行关联，所以在此使用了With AS生成了一个ygkc的表。

1. with As前面要加上分号

2. 使用With As后面紧跟着的第一个语句必须使用，再下一句就不可用了。

03

将取药，补药及盘点数据按时间排序插入临时表

取药、补药及盘点数据通过我们刚才关联的ygkc表使用Union All联合查询可以同时显示出来，直接收成临时表可以用select into语法实现。生成临时表的数据要按时间进行统一排序，正常来说用Order by即可实现，不过我希望在生成的临时表里面加入序号这一列，所以还是使用到了ROW_NUMBER() OVER的语法。

--获取生成明细账数据
select ROW_NUMBER() over(order by 病区ID,操作时间) as 序号,*,0 as 结余库存 into ##tmpdata
from (
select b.gcode as 药品编码,b.gname as 药品名称,a.last_update_date as 操作时间,
a.drugsinfo_id as 药格ID,a.drug_cabinet_id as 病区ID,b.cabinetname as 病区名称,'补药' as 操作类型,
ACTUAL_SHIPPED_QUANTITY as 操作数量,0 as 账面库存,0 as 实盘库存,Mode_ID as 操作id
from T_Replenish_Info a, ygkc b where a.drugsinfo_id = b.drugsinfo_id_yg
and a.drug_cabinet_id = b.drug_cabinet_id 
union all 
select b.gcode,b.gname,a.last_update_date,
a.drugsinfo_id,a.drug_cabinet_id,b.cabinetname,'盘点',
0,sys_qty,check_qty,'9' from dbo.T_Check_Info a, ygkc b where a.drugsinfo_id=b.drugsinfo_id_yg
and a.drug_cabinet_id = b.drug_cabinet_id  
union all
select b.gcode,b.gname,a.last_update_date,
a.drugsinfo_id,a.drug_cabinet_id,b.cabinetname,'取药',
-1*ACTUAL_SHIPPED_QUANTITY,0,0, '8'
from T_pick_Info a, ygkc b where a.drugsinfo_id = b.drugsinfo_id_yg and a.drug_cabinet_id = b.drug_cabinet_id  
and Actual_shipped_quantity>0
) a

04

游标使用计算结余数量

上一步生成临时表时加入了一个新的列结余库存，因为表里面已经按科室加时间进行排序了，所以我们从上到下的遍历计算结余库存，计算公式：

结余库存=上一条对应科室的结余库存+操作数量

计算中注意的两个原则：

1. 当遇到盘点的处理时，按实盘数来当做结余库存。

2. 当取不到上一条对应科室的结余库存时，按结余库存为0处理。

遍历表的数据计算就用到了游标

--计算结余库存
declare @c0 cursor
declare @rowno integer,@cabinetid integer,@jyqty integer
        
set @c0=cursor local for 
  select 序号,病区ID from ##tmpdata
open @c0
fetch next from @c0 into @rowno,@cabinetid
while @@fetch_status=0
begin
  --获取上一条的结余库存
  select @jyqty = isnull((Case when 操作类型='盘点' then 实盘库存 else 结余库存 end),0) from ##tmpdata where 序号= @rowno-1 and 病区ID=@cabinetid
  
  --处理当前条数的结余库存
  update ##tmpdata set 结余库存 = isnull((Case when 操作类型='盘点' then isnull(实盘库存,0) else @jyqty+isnull(操作数量,0) end),0)
  where 序号=@rowno and 病区ID=@cabinetid
  
  --初始化结余库存变量
  select @jyqty = 0
  
  --继续遍历游标
  fetch next from @c0 into @rowno,@cabinetid
end
close @c0
deallocate @c0

TIPS

上面的业务数据处理时，记得要加上，优化处理速度

set nocount on 
set nocount off

05

查询临时表结果

--查询药品单品流水账
select 序号,药品编码,药品名称,药格ID,病区ID,
病区名称,操作时间,操作类型,操作数量,账面库存,实盘库存,结余库存 from ##tmpdata
order by 序号

完

扫描二维码

获取更多精彩

微卡智享

Vaccae

#编程#和为K的子数组

视频号      

「 往期文章 」

【失败也分享】C++ OpenCV人脸Delaunay三角形提取及仿射变换的使用

C++ OpenCV人脸图像提取

C++ OpenCV Contrib模块LBF人脸特征点检测