一个利用创建临时表后插入数据的查询

最新推荐文章于 2022-04-06 10:00:27 发布

原创最新推荐文章于 2022-04-06 10:00:27 发布 · 2.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null #join #table #insert #object

SQL Server 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一个SQL Server中用于统计数据的存储过程实现方法。该过程包括创建临时表tmpPeisExamStat，设置日期范围，并统计不同分支的注册人数、考试人数及打印报告数量等关键指标。

 
DECLARE @begin varchar(10) set @begin=GETDATE()
DECLARE @end varchar(10)   set @end=GETDATE()
if object_id('tmpPeisExamStat') is  null 
Begin
   CREATE TABLE tmpPeisExamStat(
  QueryDate nvarchar(10) COLLATE   Chinese_PRC_CI_AS,
  QueryBranchID int,
  QueryBranchName nvarchar(50) COLLATE   Chinese_PRC_CI_AS,
  PreRegQty int DEFAULT 0,
  TeamRegQty int DEFAULT 0,
  RegTotal as PreRegQty+TeamRegQty,
  PreExamQty int DEFAULT 0,
  TeamExamQty int DEFAULT 0,
  ExamTotal as PreExamQty+TeamExamQty,
  PrePrintQty int DEFAULT 0,
  TeamPrintQty int DEFAULT 0,
  PrintTotal as PrePrintQty +TeamPrintQty)
    
End
ELSE
BEGIN
   truncate table tmpPeisExamStat
END
DECLARE @tmpDate varchar(10)
 SET @tmpDate = @begin
WHILE(@tmpDate <= @end)
BEGIN
   INSERT INTO tmpPeisExamStat(QueryDate,QueryBranchID,QueryBranchName)
   SELECT Convert(varchar(10),@tmpDate,111),BranchID,BranchName FROM PLBranch
   set @tmpDate = (SELECT Convert(varchar(10),DATEADD(Day,1,@tmpDate),111))
END

UPDATE tmpPeisExamStat SET PreRegQty = (SELECT COUNT(*) from PEISRegister
WHERE CancelJudge=0 AND RegisterDate=QueryDate AND BranchID=QueryBranchID AND <<{3}>>  AND UnitRegisterID IS NULL),
TeamRegQty = (SELECT COUNT(*) from PEISRegister
WHERE CancelJudge=0 AND RegisterDate=QueryDate AND BranchID=QueryBranchID  AND <<{3}>> AND UnitRegisterID IS NOT NULL),
PreExamQty = (SELECT COUNT(*) from PEISRegister
WHERE CancelJudge=0 AND ExamDate=QueryDate AND BranchID=QueryBranchID  AND <<{3}>> AND UnitRegisterID IS NULL),
TeamExamQty = (SELECT COUNT(*) from PEISRegister
WHERE CancelJudge=0 AND ExamDate=QueryDate AND BranchID=QueryBranchID  AND <<{3}>> AND UnitRegisterID IS NOT NULL),
PrePrintQty = (SELECT COUNT(*) from PEISPatientConclusion con 
 LEFT JOIN PEISRegister reg ON con.RegisterID = reg.RegisterID 
WHERE con.PrintDate=QueryDate AND BranchID=QueryBranchID AND reg.CancelJudge=0 AND <<{3}>> AND reg.UnitRegisterID IS NULL),
TeamPrintQty = (SELECT COUNT(*) from PEISPatientConclusion con 
 LEFT JOIN PEISRegister reg ON con.RegisterID = reg.RegisterID 
WHERE con.PrintDate=QueryDate AND BranchID=QueryBranchID AND reg.CancelJudge=0  AND <<{3}>> AND reg.UnitRegisterID IS NOT NULL)
select * from tmpPeisExamStat where 1=1  
AND <<{2}>>  ORDER BY QueryDate,QueryBranchID