sqlserver isnull()，Count()，sum()，month(getdate()) 的用法 

 1 select c.PayForYear,c.DptID,c.ZongShu,c.HasPayShu,c.NoPayShu
 2 --,d.DepName
 3  from (
 4     select PayForYear,DptID
 5         ,isnull(Count(PartyMemberID),0) as ZongShu
 6         ,isnull(sum(iHasPay),0) HasPayShu, 
 8         --isnull()：PartyMemberID为空返回逗号后面的0
 9         --Count():不同PartyMemberID的数目。
10         --sum():字段的总数  值相加
11          case when isnull(Count(PartyMemberID),0) =0 then 0 
12         else isnull(isnull(Count(PartyMemberID),0) - isnull(sum(iHasPay),0),0) end
13          NoPayShu
15      from ( 
16             select pm.PartyMemberID,pm.DptID,isnull(pfr.PayForYear, year(getdate())) PayForYear,
17         case when pfr.PayForMonth is null    then 0 
18                 when pfr.PayForMonth = month(getdate()) then 1 --当前月份select month(getdate()) 
19                 else 0 end as iHasPay 
20                 from partyMember pm
21          left join PartyFeeRecord pfr on  (pm.PartyMemberID = pfr.PartyMemberID and pfr.PayForMonth is not null  
22           and pfr.PayForMonth = month(getdate()) and isnull(pfr.PayForYear, year(getdate())) = year(getdate())) 
23           ) b group by PayForYear,DptID 
24    ) c 
25    left join Department d on (c.DptID = d.DptID and IsTripAndKH =1 
26    ) where c.NoPayShu >0 and d.[Path] like '%P10010012%'

总结：

1：--isnull()：PartyMemberID为空返回逗号后面的0；
2： --Count():不同PartyMemberID的数目。；
3：　　　　  --sum():字段的总数  值相加；
4：--month(getdate())； select month(getdate()) 得出当前月份
posted @ 2020-08-31 15:12  3939!  阅读(432)  评论(0)  编辑  收藏  举报