-
本版专家分：104
-
- 红花 2005年4月 Oracle大版内专家分月排行榜第一
-
-
蓝花
2005年3月 Oracle大版内专家分月排行榜第三
2004年11月 Oracle大版内专家分月排行榜第三
结帖率 93.94% -
-
有表如下
姓名 语文 数学 英语
张三 NA 100 100
李四 50 60 NA
王五 80 90 80
要得到
姓名 语文 数学 英语 平均分
张三 NA 100 100 100
李四 50 60 NA 55
王五 80 90 80 83
NA就代表未参加考试，算平均分的时候要去除，SQL怎么写 ？![]()
-
版主本版专家分：55842
-
- GitHub 绑定GitHub第三方账户获取
-
- 进士 2017年 总版技术专家分年内排行榜第八
-
-
优秀版主
2016年10月优秀大版主
优秀小版主
-
- 银牌 2017年1月 总版技术专家分月排行榜第二
-
-
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 行受影响)
-
本版专家分：15734
-
-
蓝花
2016年9月 MS-SQL Server大版内专家分月排行榜第三
2016年3月 MS-SQL Server大版内专家分月排行榜第三
-
-
还有一种写法参考：
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
-
本版专家分：7709
-
-
蓝花
2016年2月 MS-SQL Server大版内专家分月排行榜第三
2016年1月 MS-SQL Server大版内专家分月排行榜第三
-
-
;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
