判断一条记录中为NULL的字段个数~

[moon190266214]

数据表中保存的是对应不同人的打分记录，我要将数据取出计算最后的平均分，如：
id| name| score |score1|score2| score3|
1 a 10 8 null 9
我想知道四个分有几个为空，还没打分，得出个数来计算平均分（10+8+9）/3！

[快溜]

select isnull(score,0)+isnull(score1,0)+isnull(score2,0)+isnull(score3,0)/
        (case when score is null then 0 else 1 end)+
         (case when score1 is null then 0 else 1 end)+
         (case when score2 is null then 0 else 1 end)+
         (case when score3 is null then 0 else 1 end)+
from tb

[cs_lb]

CREATE TABLE test
    (
      id VARCHAR(20) ,
      name VARCHAR(20) ,
      score INT ,
      score1 INT ,
      score2 INT ,
      score3 INT
    )
    
INSERT  INTO test
        SELECT  1 ,
                'a' ,
                10 ,
                8 ,
                NULL ,
                9
    
SELECT  (ISNULL(score, 0) + ISNULL(score1, 0) + ISNULL(score2, 0)
        + ISNULL(score3, 0))*1.0 / (CASE WHEN score IS NULL THEN 0
                                   ELSE 1
                              END + CASE WHEN score1 IS NULL THEN 0
                                         ELSE 1
                                    END + CASE WHEN score2 IS NULL THEN 0
                                               ELSE 1
                                          END
        + CASE WHEN score3 IS NULL THEN 0
               ELSE 1
          END)
FROM    test