select a.name, a.tscore, b.cscore from (select distinct name, sum(cast(course as integer)) as course from deam where course in ('A', 'B') group by name) a, (select name, score as cscore from deam where course = 'C') b where a.name = b.name
select name,sum(score) where isnumber(score) group by name
建立测试环境 create table aa (name varchar(20), course varchar(10), score varchar(10) )insert aa 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,sum(case when course='A' or course='B' then score else 0 end) as Tscore ,max((case when course='C' then score else '' end)) as Cscore from aa group by name,course,score结果 name Tscore Cscore Jimmy 185 优秀 Tom 165 良好善后drop table aa
看我的 字符也一样 sum
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
上边的语句拷错了哈 这个才正确 select name,sum(case when course='A' or course='B' then score else 0 end) as Tscore ,max((case when course='C' then score else '' end)) as Cscore from aa group by name
select name,sum(cast((case when course='C' then 0 else score end) AS decimal(10,2)))),max(case when course ='C' then score else 0 end) from dean group by name
刚才没注意sore字段都是字符,应该如下select name,sum(cast((case when course='C' then '0' else score end) AS decimal(10,2)))),max(case when course ='C' then score else 0 end) from dean 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
from (select distinct name, sum(cast(course as integer)) as course from deam where course in ('A', 'B') group by name) a, (select name, score as cscore from deam where course = 'C') b
where a.name = b.name
create table aa
(name varchar(20),
course varchar(10),
score varchar(10)
)insert aa
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,sum(case when course='A' or course='B' then score else 0 end) as Tscore
,max((case when course='C' then score else '' end)) as Cscore
from aa
group by name,course,score结果
name Tscore Cscore
Jimmy 185 优秀
Tom 165 良好善后drop table aa
[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
select name,sum(case when course='A' or course='B' then score else 0 end) as Tscore
,max((case when course='C' then score else '' end)) as Cscore
from aa
group by name
from dean
group by name
from dean
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