表A如下:
Code Name Scores
1 A 100
1 B 90
1 C 85
1 D 60
2 A 92
2 C 72
3 B 83现在需要将Name是ABC的数据抽出来,并且显示为如下
注:S1 是 Name 为A 的分数,S2是B的分数,以此类推Code S1 S2 S3
1 100 90 85
2 92 72
3 83根据“能抓到老鼠的猫就是好猫”的原则,方法不限,任何方法均可,只要能得到显示结果既可
Code Name Scores
1 A 100
1 B 90
1 C 85
1 D 60
2 A 92
2 C 72
3 B 83现在需要将Name是ABC的数据抽出来,并且显示为如下
注:S1 是 Name 为A 的分数,S2是B的分数,以此类推Code S1 S2 S3
1 100 90 85
2 92 72
3 83根据“能抓到老鼠的猫就是好猫”的原则,方法不限,任何方法均可,只要能得到显示结果既可
case Name when 'A' then Scores end as S1,
case Name when 'B' then Scores end as S2,
case Name when 'C' then Scores end as S3
from 表A
group by code
(select top 1 score from score where code = a.code and name = 'A') as S1,
(select top 1 score from score where code = a.code and name = 'B') as S2,
(select top 1 score from score where code = a.code and name = 'C') as S3,
from score a
(SELECT TOP 1 scores
FROM tmp
WHERE code = a.code AND name = 'A') AS S1,
(SELECT TOP 1 scores
FROM tmp
WHERE code = a.code AND name = 'B') AS S2,
(SELECT TOP 1 scores
FROM tmp
WHERE code = a.code AND name = 'C') AS S3
FROM tmp a
union
select '' as s1,score as s2,'' as s3 from A where name='b'
union
select '' as s1,'' as s2,score as s3 from A where name='c') aaa oreder by score
code为1的Code S1 S2 S3
1 100 90 85
2 100 90 85
3 100 90 85
S1 =sum(case Name when 'A' then (Score) end) ,
S2 =sum(case Name when 'B' then (Score) end) ,
s3 =sum(case Name when 'C' then (Score) end)
from TABLE1
group by code
SELECT * FROM TABLE1
sum(case name when 'A' then score else null end) S1 ,
sum(case name when 'B' then score else null end) S2 ,
sum(case name when 'C' then score else null end) S3
from ABC
group by code
(SELECT TOP 1 scores
FROM test
WHERE code = a.code AND name = 'A') AS S1,
(SELECT TOP 1 scores
FROM test
WHERE code = a.code AND name = 'B') AS S2,
(SELECT TOP 1 scores
FROM test
WHERE code = a.code AND name = 'C') AS S3
FROM test a
DECLARE @s VARCHAR(4000)
SET @s='Code'
SELECT @s=@s+',['+Name+']=sum(case Name when '''+Name+''' then score else 0 end)'
from table1 group by Codeset @s='select '+@s+' from table1 group by Code
exec (@s)
不过我的运行结果就是这样的Code S1 S2 S3
1 100 90 85
2 100 90 85
3 100 90 85Name列不一定有多少,但是,最多选3个就够了,呵呵~~
S1 = (Select scores from 表名 where code=b.code and name='A'),
S1 = (Select scores from 表名 where code=b.code and name='B'),
S1 = (Select scores from 表名 where code=b.code and name='C')
from(select DISTINCT code
from 表名)b
试试这个
select code,
S1 =sum(case Name when 'A' then (Score) end) ,
S2 =sum(case Name when 'B' then (Score) end) ,
s3 =sum(case Name when 'C' then (Score) end)
from TABLE1
group by code
SELECT * FROM TABLE1
好兄弟,你的可以,我试过了,
不过存在一个小小的问题,我题目的是scores,你写的是score,看题不认真,
扣1分,哈哈,不过肯定不会给你1分,放心好了。
别人也都有份
的方法也不错
SET @s='Code'
SELECT @s=@s+',['+Name+']=sum(case Name when '''+Name+''' then score else 0 end)'
from table1 group by Codeset @s='select '+@s+' from table1 group by Code
exec (@s)
create table grade(
Code int not null,
Name varchar(10),
Scores int)insert into grade
select 1, 'A', 100
union select 1, 'B', 90
union select 1, 'C', 85
union select 1, 'D', 60
union select 2, 'A', 92
union select 2, 'C', 72
union select 3, 'B', 83
----查询---------------------------
select * from grade----测试---------------------------declare @sql varchar(8000)
set @sql = 'select code '
select @sql = @sql + ', sum(case [Name] when ''' + [Name] + ''' then scores else null end) as [s'+ [name] + ']'
from grade group by Name
select @sql = @sql + ' from grade group by code'
print @sql
exec (@sql)----清除---------------------------
drop table grade----结果--------------------------
code sA sB sC sD
1 100 90 85 60
2 92 NULL 72 NULL
3 NULL 83 NULL NULL
还是选择Name是ABC的
但是要将分数都统计出来,又该怎么写呢?
from table1 group by Code这种方法太强了.我是头一次看到,刚研究一下,看明白了,不知道下次是否记得用.呵.
这里强人云集啊
(select score from table1 where code =a.code and name ='A') as s1,
(select score from table1 where code =a.code and name ='A') as s2,
(select score from table1 where code =a.code and name ='A') as s3
from table1 a还可以用执行字符串的方式不限制列数,也就是A,B,C不限制
可以和我交流QQ43950411