sqlserver 查询一年中每月最后更新的分数，无则为0...帮帮忙...

[LJDargon]

sqlserver 查询一年中每月最后更新的企业分数（不是最后一天，是最后更新的分数），无则为0...

SELECT TOP 1000 
       companyCode as 企业code
      ,indexCode   as 指标code
      ,inspectDate as 检查时间
      ,score       as 指标分数
      ,levels      as 指标等级算好的
  FROM ZX_RESULT_INDEXSYSTEM

企业指标历史表，里面有每个企业五个指标的历史信息，查询每个指标一年中每个月最后更新的分数，

就是这种效果

帮帮忙...

[卖水果的net]

select CompanyId ,
sum(case when datepart(month,inspectDate) = 1 then score else 0 end) as M1,
sum(case when datepart(month,inspectDate) = 2 then score else 0 end) as M2
...
from T 
group by CompanyId

[LJDargon]

引用 1 楼 wmxcn2000 的回复:

select CompanyId ,
sum(case when datepart(month,inspectDate) = 1 then score else 0 end) as M1,
sum(case when datepart(month,inspectDate) = 2 then score else 0 end) as M2
...
from T 
group by CompanyId

不是求分数的和，就是查询一月中最后更新的内一个分数...

[卖水果的net]

with  m as (
    select CompanyId, inspectDate, score, 
        row_number() over(partition by CompanyId, convert(varchar(7),inspectDate,121)
         order by inspectDate desc
        ) rn
    from t 
)
select CompanyId ,
sum(case when datepart(month,inspectDate) = 1 then score else 0 end) as M1,
sum(case when datepart(month,inspectDate) = 2 then score else 0 end) as M2
...
from m 
where rn = 1 
group by CompanyId

[中国风]

SELECT  企业code ,
        indexCode ,
        YEAR(检查时间) AS YY ,
        MAX(CASE WHEN MONTH(检查时间) = 1 THEN 指标分数
                 ELSE 0
            END) AS [1] ,
        MAX(CASE WHEN MONTH(检查时间) = 2 THEN 指标分数
                 ELSE 0
            END) AS [2] ,
        MAX(CASE WHEN MONTH(检查时间) = 3 THEN 指标分数
                 ELSE 0
            END) AS [3] ,
        MAX(CASE WHEN MONTH(检查时间) = 4 THEN 指标分数
                 ELSE 0
            END) AS [4] ,
        MAX(CASE WHEN MONTH(检查时间) = 5 THEN 指标分数
                 ELSE 0
            END) AS [5] ,
        MAX(CASE WHEN MONTH(检查时间) = 6 THEN 指标分数
                 ELSE 0
            END) AS [6] ,
        MAX(CASE WHEN MONTH(检查时间) = 7 THEN 指标分数
                 ELSE 0
            END) AS [7] ,
        MAX(CASE WHEN MONTH(检查时间) = 8 THEN 指标分数
                 ELSE 0
            END) AS [8] ,
        MAX(CASE WHEN MONTH(检查时间) = 9 THEN 指标分数
                 ELSE 0
            END) AS [9] ,
        MAX(CASE WHEN MONTH(检查时间) = 10 THEN 指标分数
                 ELSE 0
            END) AS [10] ,
        MAX(CASE WHEN MONTH(检查时间) = 11 THEN 指标分数
                 ELSE 0
            END) AS [11] ,
        MAX(CASE WHEN MONTH(检查时间) = 12 THEN 指标分数
                 ELSE 0
            END) AS [12]
FROM    ( SELECT    companyCode AS 企业code ,
                    indexCode AS 指标code ,
                    inspectDate AS 检查时间 ,
                    score AS 指标分数 ,
                    levels AS 指标等级算好的 ,
                    ROW_NUMBER() OVER ( PARTITION BY companyCode, indexCode,
                                        CONVERT(VARCHAR(7), inspectDate, 120) ORDER BY inspectDate DESC ) AS RN
          FROM      ZX_RESULT_INDEXSYSTEM
        ) AS T
WHERE   RN = 1
GROUP BY 企业code ,
        indexCode ,
        YEAR(检查时间);

[中国风]

按每年显示12个月份