求SQL

[zhaokeke2004]

有表如下
姓名 语文 数学 英语
张三 NA 100 100
李四 50 60 NA
王五 80 90 80

要得到

姓名 语文 数学 英语 平均分
张三 NA 100 100 100
李四 50 60 NA 55
王五 80 90 80 83

NA就代表未参加考试，算平均分的时候要去除，SQL怎么写 ？

[卖水果的net]

create table test(name varchar(10),S1 varchar(10),S2 varchar(10),S3 varchar(10))
go
insert into test values
('张三','NA','100','100'),
('李四','50','60' ,'NA'),	
('王五','80','90' ,'80')
go
select name , S1,S2,S3, 
      (case S1 when 'NA' then 0 else convert(int,S1) end + 
       case S2 when 'NA' then 0 else convert(int,S2) end +
       case S3 when 'NA' then 0 else convert(int,S3) end 
      ) /       
      (case S1 when 'NA' then 0 else 1 end + 
       case S2 when 'NA' then 0 else 1 end +
       case S3 when 'NA' then 0 else 1 end 
      )
  from test 
go
drop table test 
go


name       S1         S2         S3         
---------- ---------- ---------- ---------- -----------
张三         NA         100        100        100
李四         50         60         NA         55
王五         80         90         80         83

(3 行受影响)

[Ginnnnnnnn]

;WITH CTE AS 
(
	SELECT '张三' AS Name,
			'NA' AS 语文,
			'100' AS 数学,
			'100' AS 英语
	UNION ALL
	SELECT '李四' AS Name,
			'50' AS 语文,
			'60' AS 数学,
			'NA' AS 英语
	UNION ALL
	SELECT '王五' AS Name,
			'80' AS 语文,
			'90' AS 数学,
			'80' AS 英语
),CTE2 AS
(
	SELECT CTE.Name,
		   CONVERT(INT,NULLIF(语文,'NA')) AS  语文,
		   CONVERT(INT,NULLIF(数学,'NA')) AS  数学,
		   CONVERT(INT,NULLIF(英语,'NA')) AS  英语
		FROM CTE
)
SELECT 	a.*,b.分数
	FROM CTE a
	INNER JOIN (
SELECT Name ,AVG(分数) AS 分数
	FROM CTE2 UNPIVOT( [分数] FOR [科目]
					IN ([语文],[数学],[英语])) AS c
		GROUP BY Name) b ON a.Name = b.Name

[道素]

还有一种写法参考：

SELECT test.*,t.average FROM test LEFT JOIN (	
    SELECT  t.name , avg(convert(int,tt.s)) AS average FROM test AS t
    CROSS APPLY (VALUES(s1),(s2),(s3)) tt(s)
    WHERE  ISNUMERIC(tt.s)=1
    GROUP BY t.name
) t ON test.name=t.name