SQL 按年,月,日统计相关函数

说明:

统计每月下组织每天费用记录

表:MemberMoney  

字段   Id   主键

          用户Id varchar(50)

          OnOrgId 所在单位 varchar(50)

          RealPay 金额 decimal(18,2)

          PayDate 缴费时间 datetime

          AddYear 年 int

          PayMonth 月 int

          PayType 缴费方式 int(0,在线支付 ;1,现金)

 

 

 select p.* ,o.ShortName as OnOrgName from (  
   select a.*,ISNULL(b.OnlineFee,0) OnlineFee,ISNULL(c.CashFee,0) CashFee from (  
   select OnOrgID,cast( cast(year(PayDate) as varchar) +-+cast(month(PayDate) as varchar) +-+cast(day(PayDate) as varchar) as date) as Pdate,sum(RealPay) as Pmoney from MemberMoney 
    where AddYear=2020 and PayMonth=9 and PayDate is not null and OnOrgID=‘‘  group by OnOrgID,year(PayDate),month(PayDate),day(PayDate) )a  
    left join  
     ( select OnOrgID,cast( cast(year(PayDate) as varchar) +-+cast(month(PayDate) as varchar) +-+cast(day(PayDate) as varchar) as date) as Pdate,
     sum(RealPay) as OnlineFee 
     from MemberMoney  where AddYear=2020 and PayMonth=9 and PayDate is not null and OnOrgID=‘‘ and PayType=0  group by OnOrgID,year(PayDate),month(PayDate),day(PayDate) 
     )b  
     on a.OnOrgID=b.OnOrgID   and a.Pdate=b.Pdate
     left join  
      ( select OnOrgID,cast( cast(year(PayDate) as varchar) +-+cast(month(PayDate) as varchar) +-+cast(day(PayDate) as varchar) as date) as Pdate,sum(RealPay) as CashFee from MemberMoney  
      where AddYear=2020 and PayMonth=9 and PayDate is not null  and OnOrgID=‘‘ and PayType=1  group by OnOrgID,year(PayDate),month(PayDate),day(PayDate)
       )c  

    on a.OnOrgID=c.OnOrgID   and a.Pdate=c.Pdate
     
 ) p left join Base_Organize o on p.OnOrgID=o.OrganizeId order by pdate
 

 

ISNULL 判断是否为NULL ,为NULL赋值0
cast(字段 as varchar) 例 int 转 string

year(PayDate) 得到年
month(PayDate)得到月
day(PayDate)得到天

SQL 按年,月,日统计相关函数

 

 



SQL 按年,月,日统计相关函数

上一篇:oracle中for循环的使用


下一篇:delphi中的LISTVIEW怎么与数据库连接