急 求 SQL 语句 高手进!

[changlongbaobao]

表Question
num(编号),nam(名字),idzhang(章节),mark(得分)
001 a 2 2
002 b 3 1
003 c 1 2
004 d 2 2
004 d 1 2
要得到的表
num nam 第一章得分 第二章得分 第三章得分 总分
001 a 0 2 0 2
002 b 0 0 1 1
003 c 2 0 0 2
004 d 2 2 0 4

[贝贝壳壳]

到SQL版搜索交叉表

列子

http://community.csdn.net/Expert/topic/4873/4873149.xml?temp=.9126245

[liujia_0421]

写个存储过程试试..

[changlongbaobao]

有人能给出具体的SQL吗?

[雅彦]

declare @sql varchar(8000)
set @sql = 'select num , nam'
select @sql = @sql + ' , sum(case idzhang when '''+cast(idzhang as varchar)+''' then mark else 0 end) [第'+cast(idzhang as varchar)+'章得分]'
from (select distinct idzhang from Question) as a
set @sql = @sql + ' ,sum(mark)[总分] from Question group by num'
exec(@sql)

[changlongbaobao]

yhmodem(潇洒的瓢儿白) 稍微改一下就可以了 DotNET3() 不对

[jhtchina]

Mark

[yhmodem]

create table Question(num varchar(10),name varchar(10),idzhang varchar(10),mark int)
insert into question select '001', 'a', '2', 2 union all
select '002', 'b', '3', 1 union all
select '003', 'c', '1', 2 union all
select '004', 'd', '2', 2 union all
select '004', 'd', '1', 2

select num, name,
第一單=max(case when idzhang='1' then mark else 0 end),
第二單=max(case when idzhang='2' then mark else 0 end),
第三單=max(case when idzhang='3' then mark else 0 end),
總分=sum(case when idzhang='1' then mark else 0 end + case when idzhang='2' then mark else 0 end + case when idzhang='3' then mark else 0 end)
from question group by num,name

[DotNET3]

-- Create table
create table ZJ
(
IID NVARCHAR2(36) not null,
NAM NVARCHAR2(36),
IDZHANG NVARCHAR2(36),
MARK NUMBER
)
tablespace EIMS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ZJ
add constraint FK_ZJ primary key (IID)
using index
tablespace EIMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
insert into ZJ(IID,NAM,IDZHANG,MARK) values('001','a','2',2);
insert into ZJ(IID,NAM,IDZHANG,MARK) values('002','b','3',1);
insert into ZJ(IID,NAM,IDZHANG,MARK) values('003','c','1',2);
insert into ZJ(IID,NAM,IDZHANG,MARK) values('004','d','2',2);
insert into ZJ(IID,NAM,IDZHANG,MARK) values('005','d','1',2);

Select
t.nam,
(select ZJ.idzhang from ZJ where ZJ.IDZHANG='1' and ZJ.NAM=t.nam) as 第一章节得分,
(select ZJ.idzhang from ZJ where ZJ.IDZHANG='2' and ZJ.NAM=t.nam) as 第二章节得分,
(select ZJ.idzhang from ZJ where ZJ.IDZHANG='3' and ZJ.NAM=t.nam) as 第三章节得分,
sum(t.mark) as 总分
from ZJ t group by t.nam

除了我这个SQL，你不可能见到再简单的了！而且很容易理解