--这个?
select * from 表 where student_name='kern'--统计
select student_name
,sum( result) as 'result'
from 表
where student_name='kern'
group by student_name
select * from 表 where student_name='kern'--统计
select student_name
,sum( result) as 'result'
from 表
where student_name='kern'
group by student_name
我的意思是这样的
MATHE PE
kern 100 87
我想得到这样的一个表,如何做
from your_table a
join your_table b on b.student_name<>a.student_name and b.lesson<>a.lesson
join your_table c on c.student_name<>a.student_name and c.lesson<>a.lesson
and c.student_name<>b.student_name and c.lesson<>b.lesson
原表是这样的
student_name lesson_name result
kern maths 100
bob p.e 90
bob maths 80
kern p.e 87
想得到的新表是这样的
student_name Mathes p.e
kern 100 87
bob 80 90
如何用sql实现
谢谢各位了
(
student_name varchar(10),
lesson_name varchar(10),
result int
)
insert @tb
select 'kern','maths',100 union
select 'bob','p.e',90 union
select 'bob','maths',80 union
select 'kern','p.e',87--查询
select student_name
,max(case when lesson_name='maths' then result else 0 end) as 'maths'
,max(case when lesson_name='p.e' then result else 0 end) as 'p.e'
from @tb
group by student_name--结果
/*student_name maths p.e
------------ ----------- -----------
bob 80 90
kern 100 87(所影响的行数为 2 行)
*/
(
student_name varchar(10),
lesson_name varchar(10),
result int
)
insert A
select 'kern','maths',100 union
select 'bob','p.e',90 union
select 'bob','maths',80 union
select 'kern','p.e',87
go--查询
declare @sql varchar(8000)
set @sql='select student_name'
select @sql=@sql+',max(case when lesson_name='''+lesson_name+''' then result else 0 end) as '''+lesson_name+''''
from A group by lesson_name
select @sql=@sql+' from A group by student_name'
exec(@sql)--删除测试环境
drop table A--结果
/*student_name maths p.e
------------ ----------- -----------
bob 80 90
kern 100 87(所影响的行数为 2 行)
*/
declare @sql varchar(8000)
set @sql='select student_name'
select @sql=@sql+',max(case when lesson_name='''+lesson_name+''' then result else 0 end) as '''+lesson_name+''''
from A group by lesson_name
select @sql=@sql+' from A group by student_name'
exec(@sql)就解决了多课程的问题.学习
set @sql='select student_name'
select @sql=@sql+',max(case when lesson_name='''+lesson_name+''' then result else 0 end) as '''+lesson_name+''''
from A group by lesson_name
select @sql=@sql+' from A group by student_name'
exec(@sql)
这个你们可以用吗,我现在用他的第一个方法实现的,谢谢了