Unpivot列传行的应用

最新推荐文章于 2025-12-18 10:59:28 发布

原创最新推荐文章于 2025-12-18 10:59:28 发布 · 204 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #Unpivot

数据库专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种基于微信平台的健康筛查疾病的统计方法，通过分析特定时间段内的疾病记录，为用户提供疾病分布信息。该方法使用SQL存储过程进行数据处理，能够有效收集并统计儿童常见疾病的发病情况。

-- =============================================  
-- Author:  <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
--  [weixin_health_screen_disease] 37385  
-- =============================================  
CREATE PROCEDURE [dbo].[weixin_health_screen_disease]  
 @kid int  
AS  
BEGIN  
 SET NOCOUNT ON;  
 /*  
 declare @bgndate varchar(10), @enddate varchar(10), @year int  
   
 if  MONTH(GETDATE()) >= 9  
     begin  
     set @year= (select CAST( YEAR(GETDATE()) as int))  
     set @bgndate = (select CAST( YEAR(GETDATE()) as varchar) + '-09-01')  
     set @enddate = (select CAST( YEAR(DATEADD(yy,1,GETDATE())) as varchar) + '-07-01')  
     end  
   else   
        begin   
         set @year= (select CAST( YEAR(DATEADD(yy,-1,GETDATE())) as varchar))  
        set @bgndate = (select CAST( YEAR(DATEADD(yy,-1,GETDATE())) as varchar) + '-09-01')  
        set @enddate = (select CAST( YEAR(GETDATE()) as varchar) + '-07-01')  
        end   
          
     select  ID,bgndate, LEFT( CONVERT(varchar(10),bgndate,120),7) bgnmonth ,disease  
       into #t  
      from BasicData..weixin_doctor_disease_record  
     where kid = @kid  
       and bgndate >= @bgndate  
       and bgndate < @enddate      
        
      select 0 code, '获取成功' info    
       
      create table #t1 (bgnmonth varchar(10),cnt int)  
        
     insert into #t1(bgnmonth,cnt) select cast(@year as varchar(4)) + '-09', COUNT(ID) cnt from #t t where bgnmonth = cast(@year as varchar(4)) + '-09'   
     insert into #t1(bgnmonth,cnt) select cast(@year as varchar(4)) + '-10', COUNT(ID) cnt from #t t where bgnmonth =  cast(@year as varchar(4)) + '-10'   
     insert into #t1(bgnmonth,cnt) select cast(@year as varchar(4)) + '-11', COUNT(ID) cnt from #t t where bgnmonth =  cast(@year as varchar(4)) + '-11'   
     insert into #t1(bgnmonth,cnt) select cast(@year as varchar(4)) + '-12', COUNT(ID) cnt from #t t where bgnmonth =  cast(@year as varchar(4)) + '-12'    
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-01' , COUNT(ID) cnt from #t t where bgnmonth =  cast((@year+1) as varchar(4)) + '-01'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-02' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-02'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-03' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-03'  
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-04' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-04'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-05' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-05'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-06' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-06'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-07' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-07'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-08' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-08'   
       
  
       
      select  bgnmonth,cnt  
      from #t1  
      order by bgnmonth  
        
      select t.bgnmonth, COUNT(ID) cnt  
      from  
      #t t   
      group by  t.bgnmonth   
      order by t.bgnmonth   
       
      select t.disease, COUNT(ID) cnt  
      from  
      #t t   
      group by  t.disease  
      order by t.disease  
       */  
  
  select 0 code, '获取成功' info  
  
  Declare @springday datetime  
  Select @springday = sdate From BasicData.dbo.Springday Where term = Cast(DATEPART(yy, GETDATE()) as Varchar(10)) + '-0'  
  
  Select Convert(Varchar(7), cdate, 120) bgnmonth, disease, cnt  
    Into #t  
    From mcapp..record_mc_kid_day  
    Unpivot(cnt for disease In (fs, ks, hlfy, lbt, pz, fx, hy)) a  
    Where kid = @kid   
    and cdate >= Case When GETDATE() < @springday Then Cast(DATEPART(yy, GETDATE()) - 1 as Varchar(10)) + '-09-01'  
                        When GETDATE() >= Cast(DATEPART(yy, GETDATE()) as Varchar(10)) + '-09-01' Then Cast(DATEPART(yy, GETDATE()) as Varchar(10)) + '-09-01'  
                        Else @springday End  
      and cdate < GETDATE()  
  
  Select bgnmonth, Sum(cnt) cnt  
    From #t  
    Group by bgnmonth  
    
  Select Case disease When 'fs' Then '发烧' When 'ks' Then '咳嗽' When 'hlfy' Then '喉咙发炎' When 'lbt' Then '流鼻涕'   
                      When 'pz' Then '皮疹' When 'fx' Then '腹泻' When 'hy' Then '红眼病' Else '其它' End disease, Sum(cnt) cnt  
    From #t  
    Group by disease  
  
END