类似于这样:create table classscore
(
student int,
subject varchar(10),
score int
)insert into classscore values(201001,'日本语',80)
insert into classscore values(201001,'化学',80)
insert into classscore values(201002,'日本语',90)
insert into classscore values(201003,'日本语',100)
insert into classscore values(201001,'数学',80)
insert into classscore values(201001,'物理',85)
insert into classscore values(201002,'数学',86)
insert into classscore values(201004,'物理',87)select student,
(select score from classscore b
where subject='日本语' and b.student=a.student) as '日本语',
(select score from classscore b
where subject='化学' and b.student=a.student) as '化学',
(select score from classscore b
where subject='数学' and b.student=a.student) as '数学',
(select score from classscore b
where subject='物理' and b.student=a.student) as '物理'
from classscore a
group by student
(
student int,
subject varchar(10),
score int
)insert into classscore values(201001,'日本语',80)
insert into classscore values(201001,'化学',80)
insert into classscore values(201002,'日本语',90)
insert into classscore values(201003,'日本语',100)
insert into classscore values(201001,'数学',80)
insert into classscore values(201001,'物理',85)
insert into classscore values(201002,'数学',86)
insert into classscore values(201004,'物理',87)select student,
(select score from classscore b
where subject='日本语' and b.student=a.student) as '日本语',
(select score from classscore b
where subject='化学' and b.student=a.student) as '化学',
(select score from classscore b
where subject='数学' and b.student=a.student) as '数学',
(select score from classscore b
where subject='物理' and b.student=a.student) as '物理'
from classscore a
group by student
解决方案 »
- 请问怎么通过查询上表,要求查询出的数据如下(即根据student_id将各自的分数横向显示)
- 救命救命想把存储过程查询处的数据插入到一个表中间
- 这是什么意思!!
- SQLServer2k sp3 不要打丁吗?
- SQL的存储过程能不能实现这样的功能
- 求助:用DTS从SQLSERVER往ORACLE中导数据太慢了,有没有好办法
- dmp 文件,没人答对
- 关于SQL中日期格式转换问题急请高手帮忙!!
- 我用SQL SEVER数据库开发数据库软件,如何做才能把数据库移植到别的机器呢?(自己写程序)
- 新手刚学SQL,学生依弧画瓢写的这个存储过程,菜鸟请各位多多指教!
- 这个语句哪里有错?在线等待
- 如何将SELECT COUNT(*) FROM TABLE的结果存入变量中
CREATE TABLE test (id char(),date char(10),ctime char(5))insert into test (id,date,ctime) values ('019710','2005-08-18','08:00')
insert into test (id,date,ctime) values ('019710','2005-08-18','12:00')
insert into test (id,date,ctime) values ('019710','2005-08-18','13:00')
insert into test (id,date,ctime) values ('019710','2005-08-18','17:30')
insert into test (id,date,ctime) values ('019710','2005-08-18','18:30')
insert into test (id,date,ctime) values ('019710','2005-08-18','21:30')
insert into test (id,date,ctime) values ('019710','2005-08-19','08:00')查询语句:
declare @s varchar(8000)
set @s = ''
select @s = @s + ','+'ctime'+'=max(case ctime when '''+ctime+''' then ctime else '''' end)'
from test group by ctime
set @s = 'select id,date'+@s+' from test group by date,id'
exec(@s)
查询结果:
id date ctime ctime ctime ctime ctime ctime
019710 2005-08-18 08:00 12:00 13:00 17:30 18:30 21:30
019710 2005-08-19 08:00 现在的问题是没解决 ctime N 的问题,等带高手.
CREATE TABLE test (id char(6),date char(10),ctime char(5))
declare @s varchar(8000)
declare @i int
set @s = ''
set @i=0
select @i=@i+1,@s = @s + ','+'ctime'+convert(char(1),@i)+'=max(case ctime when '''+ctime+''' then ctime else '''' end)'
from test group by ctime
set @s = 'select id,date'+@s+' from test group by date,id'
exec(@s)
create table 表1 (id varchar(20),DATE varchar(10),CTIME varchar(10))
insert into 表1 select '019710','2005-08-18','08:00'
union all select '019710','2005-08-18','12:00'
union all select '019710','2005-08-18','13:30'
union all select '019710','2005-08-18','17:30'
union all select '019710','2005-08-18','18:30'
union all select '019710','2005-08-18','21:30'
union all select '019710','2005-08-19','12:00'
--动态SQL
declare @s varchar(4000)
set @s='select a.ID,a.DATE'
select @s=@s+', [CTIME'+(select distinct convert(varchar(10),sum(1)) from 表1 where id=a.id and date=a.date and CTIME<=a.CTIME)+']=min(case when b.xh='+(select distinct convert(varchar(10),sum(1)) from 表1 where id=a.id and date=a.date and CTIME<=a.CTIME)+' then b.CTIME else NULL end ) '
from 表1 a
where exists (select 1 from 表1 where ID=a.ID and DATE=a.DATE and CTIME<>a.CTIME)
group by ID,DATE,CTIME
set @s=@s+' from 表1 a right join
(select *,xh=(select sum(1) from 表1 where id=x.id and date=x.date and CTIME<=x.CTIME)
from 表1 x ) b
on a.id=b.id and a.date=b.date and a.CTIME=b.CTIME Group by a.ID,a.Date'
exec(@s)--结果
ID DATE CTIME1 CTIME2 CTIME3 CTIME4 CTIME5 CTIME6
-------- ---------- ---------- ---------- ---------- ---------- ---------- ---
019710 2005-08-18 08:00 12:00 13:30 17:30 18:30 21:30
019710 2005-08-19 12:00 NULL NULL NULL NULL NULL--删除测试环境
drop table 表1
动态SQL的好处就是适用与列数不定的情况下
大数据量无法完成执行啊!有没有更好的方法啊?谢