27,582
社区成员




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
;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
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 行受影响)