求助SQL语句

Question

'A表的字段是:姓名       科目与成绩
小明  英语:95 数学:98 语文:100
小陈  英语:96 数学:99 语文:95'如何做才能把A表中"科目与成绩"中的数据单独提取,'如:小明:英语:95
   '小明:数学:98
   '小明:语文:100   '小陈:英语:96
   '小陈:数学:99
   '小陈:语文:95

Answer 1

declare @A table([姓名] varchar(20),[科目] varchar(30),[成绩] numeric(10,2))insert into  @A values( '小明','英语',95)
insert into  @A  values( '小明','数学',98)
insert into  @A  values( '小明','语文',100)
insert into  @A  values( '小陈','英语',96)
insert into  @A  values( '小陈','数学',96)
insert into  @A  values( '小陈','语文',95)
select * from @A

Answer 2

select 科目，成绩 from A where name = 小明

Answer 3

表的设计不符合关系型数据库规范,建议修改...就你的示例,ACCESS实现如下:select 姓名,iif(instr(科目,'英语')>0,mid(mid(科目,instr(科目,'英语')),1,instr(mid(科目,instr(科目,'英语')),' ')),'') as T from tt
union all
select 姓名,iif(instr(科目,'数学')>0,mid(mid(科目,instr(科目,'数学')),1,instr(mid(科目,instr(科目,'数学')),' ')),'') from tt
union all
select 姓名,iif(instr(科目,'语文')>0,mid(科目,instr(科目,'语文')),'') from tt

Answer 4

select 姓名
      ,'英语' as 科目
      ,trim(mid(科目,instr(科目,'英语')+ len('英语:'),3))
 from a 
where instr(科目,'英语')>0
union all
select 姓名
      ,'数学' as 科目
      ,trim(mid(科目,instr(科目,'数学')+ len('数学:'),3))
 from a 
where instr(科目,'数学')>0  
union all 
select 姓名
      ,'语文' as 科目
      ,trim(mid(科目,instr(科目,'语文')+ len('语文:'),3))
 from a 
where instr(科目,'语文')>0

Answer 5

select 姓名 
      ,'英语' as 科目 
      ,trim(mid(科目与成绩,instr(科目与成绩,'英语')+ len('英语:'),3)) 
from a 
where instr(科目与成绩,'英语')>0 
union all 
select 姓名 
      ,'数学' as 科目 
      ,trim(mid(科目与成绩,instr(科目与成绩,'数学')+ len('数学:'),3)) 
from a 
where instr(科目与成绩,'数学')>0  
union all 
select 姓名 
      ,'语文' as 科目 
      ,trim(mid(科目与成绩,instr(科目与成绩,'语文')+ len('语文:'),3)) 
from a 
where instr(科目与成绩,'语文')>0 

Answer 6

access中测试了select 姓名 
      ,'英语' as 科目 
      ,trim(mid(科目与成绩,instr(科目与成绩,'英语')+ len('英语:'),3)) 
from a 
where instr(科目与成绩,'英语')>0 
union all 
select 姓名 
      ,'数学' as 科目 
      ,trim(mid(科目与成绩,instr(科目与成绩,'数学')+ len('数学:'),3)) 
from a 
where instr(科目与成绩,'数学')>0  
union all 
select 姓名 
      ,'语文' as 科目 
      ,trim(mid(科目与成绩,instr(科目与成绩,'语文')+ len('语文:'),3)) 
from a 
where instr(科目与成绩,'语文')>0 
order by 姓名;

Answer 7

改表吧,现在这样的表,一个简单查询效率就如此低,还有其它操作效率就更不能看了,比如要增加科目,所有这这样的union语句都要重写...

Answer 8

select 姓名 
      ,'英语' as 科目 
      ,trim(mid(科目与成绩,instr(科目与成绩,'英语')+ len('英语:'),3)) as 成绩 
from a 
where instr(科目与成绩,'英语')>0 
union all 
select 姓名 
      ,'数学' as 科目 
      ,trim(mid(科目与成绩,instr(科目与成绩,'数学')+ len('数学:'),3)) as 成绩 
from a 
where instr(科目与成绩,'数学')>0  
UNION ALL select 姓名 
      ,'语文' as 科目 
      ,trim(mid(科目与成绩,instr(科目与成绩,'语文')+ len('语文:'),3)) as 成绩 
from a 
where instr(科目与成绩,'语文')>0
ORDER BY 姓名;结果姓名 科目 成绩
小陈 语文 95
小陈 数学 99
小陈 英语 96
小明 语文 100
小明 数学 98
小明 英语 95