create table test(name varchar(6),course varchar(2),score numeric(6,2)) insert into test select 'Tom' , 'A', 80 union all select 'Tom', 'B', 85 union all select 'Jimmy', 'B', 90 union all select 'Jimmy', 'A', 95select name, [total scroe]=isnull(sum(score),0), [score of A]=isnull(sum(case when course='A' then score else 0 end),0) from test group by name
谢谢两位大侠, 但是如果score里面存的是字串name course score Tom A 80 Tom B 85 Tom C 良好 Jimmy B 90 Jimmy A 95 Jimmy C 优秀这样,如果要看每个人的sum(A,B) 以及C的成绩怎么弄? 因为这里C不是数字,所以不能用sum了啊,怎么单独提取出来? name Tscore Cscore Tom 165 良好 Jimmy 185 优秀
select name,sum(case when course='C' then 0 else score end),max(case when course ='C' then score else 0 end) from dean group by name
--创建测试环境 create table A ( name varchar(6), course varchar(2), score varchar(10) ) insert into A select 'Tom' , 'A', '80' union select 'Tom', 'B','85' union select 'Tom','C','良好' union select 'Jimmy', 'B', '90' union select 'Jimmy','A','95' union select 'Jimmy','C','优秀'--测试 select B.name,B.Tscore,A.score from A join (select name,[Tscore]=sum(case when isnumeric(score)=1 then score else 0 end) from A group by name)B on A.name=B.name where A.course='C'--删除测试环境 drop table A--结果 /*name Tscore score ------ ----------- ---------- Jimmy 185.00 优秀 Tom 165.00 良好(所影响的行数为 2 行) */
不好意思,刚才发错了,应该是: select name,CAST((sum(cast((case when course='C' then 0 else score end) as int))) AS VARCHAR(3)),max(case when course ='C' then score else 0 end) from dean group by name
如果总分数字段不需要转成字符,而且有小数位,请用如下语句 select name,sum(cast((case when course='C' then 0 else score end) AS decimal(10,5)))),max(case when course ='C' then score else 0 end) from dean group by name
select name, [total scroe]= sum(case when ISNUMERIC(score)= 1 then score else 0 end), [Tscore]=max(case when ISNUMERIC(score)= 0 then score else '' end) from test group by name
create table test (name varchar(20), course varchar(10), score varchar(10) ) insert into test select 'Tom','A','80' union all select 'Tom','B','85' union all select 'Tom','C','良好' union all select 'Jimmy','B', '90' union all select 'Jimmy','A','95' union all select 'Jimmy','C','优秀'select name, [tscroe]=isnull(sum(case when course='A' or course='B' then cast(score as numeric(6,2)) end ),0), [Cscore]=max(case when course='C' then score else '' end) from test group by name
insert into test
select 'Tom' , 'A', 80
union all select 'Tom', 'B', 85
union all select 'Jimmy', 'B', 90
union all select 'Jimmy', 'A', 95select name,
[total scroe]=isnull(sum(score),0),
[score of A]=isnull(sum(case when course='A' then score else 0 end),0)
from test
group by name
但是如果score里面存的是字串name course score
Tom A 80
Tom B 85
Tom C 良好
Jimmy B 90
Jimmy A 95
Jimmy C 优秀这样,如果要看每个人的sum(A,B) 以及C的成绩怎么弄?
因为这里C不是数字,所以不能用sum了啊,怎么单独提取出来?
name Tscore Cscore
Tom 165 良好
Jimmy 185 优秀
from dean
group by name
create table A
(
name varchar(6),
course varchar(2),
score varchar(10)
)
insert into A
select 'Tom' , 'A', '80' union
select 'Tom', 'B','85' union
select 'Tom','C','良好' union
select 'Jimmy', 'B', '90' union
select 'Jimmy','A','95' union
select 'Jimmy','C','优秀'--测试
select B.name,B.Tscore,A.score from A
join (select name,[Tscore]=sum(case when isnumeric(score)=1 then score else 0 end) from A group by name)B
on A.name=B.name where A.course='C'--删除测试环境
drop table A--结果
/*name Tscore score
------ ----------- ----------
Jimmy 185.00 优秀
Tom 165.00 良好(所影响的行数为 2 行)
*/
select name,CAST((sum(cast((case when course='C' then 0 else score end) as int))) AS VARCHAR(3)),max(case when course ='C' then score else 0 end)
from dean
group by name
select name,sum(cast((case when course='C' then 0 else score end) AS decimal(10,5)))),max(case when course ='C' then score else 0 end)
from dean
group by name
[total scroe]= sum(case when ISNUMERIC(score)= 1 then score else 0 end),
[Tscore]=max(case when ISNUMERIC(score)= 0 then score else '' end)
from test
group by name
(name varchar(20),
course varchar(10),
score varchar(10)
) insert into test
select 'Tom','A','80'
union all select 'Tom','B','85'
union all select 'Tom','C','良好'
union all select 'Jimmy','B', '90'
union all select 'Jimmy','A','95'
union all select 'Jimmy','C','优秀'select name,
[tscroe]=isnull(sum(case when course='A' or course='B' then cast(score as numeric(6,2)) end ),0),
[Cscore]=max(case when course='C' then score else '' end)
from test
group by name