create view v_subject1 as
select stu_id,stu_ from stu_grade
where subject_id = '第1科'依此类推,然后
create view v_total as
select a.stu_id,a.stu_name,b.stu_ 1,....
from stu_grade a,v_subject1 b,....
where b.stu_id = a.stu_id
and ...
select stu_id,stu_ from stu_grade
where subject_id = '第1科'依此类推,然后
create view v_total as
select a.stu_id,a.stu_name,b.stu_ 1,....
from stu_grade a,v_subject1 b,....
where b.stu_id = a.stu_id
and ...
解决方案 »
- 自认为逻辑上,语法上都没问题的简单存储过程出问题了,求救!
- 关于SQL Server事务处理的一系列问题。我分有多,只要能给出详细答案,我愿按字算分。(1)
- 这个SQL语句怎么写
- 不太简单的搜索问题
- 如何在存储过程处理 接收一个多值参数做游标in的目标
- ms-sql2005同步mysql5.1疑难,冰天雪地裸体跪谢大虾指教
- ◆◆在线狂等ing 一个巨简单的sql语句◇◇
- 求一个SQL语句,在线等。。。。
- 标识列 (Identity column) 的事务复制问题
- 如何过滤SQL2000视图中的空值NULL?
- 在sql7.0中,我要把一个表中的一行记录完整的copy到另一个表中,这两个表不在一个同一个数据库中,如何做。。**********急急急急急*******************
- 14,000,000条纪录的一个大表,应该怎么处理以提高速度?
我的意思是只用sql语句。不管多长,写出来最好按一定规范。
to zhhp6489(坐看云起时) :
是在oracle中,不过sql server 也无所谓,要求习题使用sql语句,而不使用特定数据库中的函数。
在这里谢过二位的回贴。
Insert Into tablequery
Select stu_id, stu_name, stu_,0, 0 From stu_grade Where subject_id = '第一科'
Union
Select stu_id, stu_name, 0, stu_,0 From stu_grade Where subject_id = '第二科'
Union
Select stu_id, stu_name, 0, 0,stu_ From stu_grade Where subject_id = '第三科'
然后查询
Select stu_id, stu_name, Sum(第一科), Sum(第二科), Sum(第三科), Sum(第一科) + Sum(第二科) + Sum(第三科)
From tablequery
Group By stu_id, stu_name
select a.stu_id , a.stu_name,b.stu_ end as 第1科,c.stu_ end as 第2科,
d.stu_ end as 第3科,b.stu_ +c.stu_ +d.stu_ as sum_
from (select DISTINCT stu_id from stu_grade ) a
inner join (select stu_id ,stu_ from stu_grade
where subject_id = '第1科') b on a.stu_id = b.stu_id
inner join (select stu_id ,stu_ from stu_grade
where subject_id = '第2科') c on a.stu_id = c.stu_id
inner join (select stu_id ,stu_ from stu_grade
where subject_id = '第3科') d on a.stu_id = d.stu_id 如果有的人在某一学科没有值,为了保证不在合计中出现null,可用
select m.stu_id , m.stu_name,
m.subject_id1 as 第1科,
m.subject_id2 as 第2科,
m.subject_id3 as 第3科,
m.subject_id1+m.subject_id2+m.subject_id3 as sum_
from (select a.stu_id , a.stu_name,
case when b.stu_ is null then 0 else b.stu_ end as subject_id1,
case when c.stu_ is null then 0 else c.stu_ end as subject_id2,
case when d.stu_ is null then 0 else d.stu_ end as subject_id3
from (select DISTINCT stu_id from stu_grade ) a
left outer join (select stu_id ,stu_ from stu_grade
where subject_id = '第1科') b on a.stu_id = b.stu_id
left outer join (select stu_id ,stu_ from stu_grade
where subject_id = '第2科') c on a.stu_id = c.stu_id
left outer join (select stu_id ,stu_ from stu_grade
where subject_id = '第3科') d on a.stu_id = d.stu_id ) m
a.stu_name,
isnull(第1科,0) as 第1科,
isnull(第2科,0) as 第2科,
isnull(第3科,0) as 第3科,
isnull(sum,0) as sum
from (select distinct stu_id, stu_name from stu_grade) as a
left outer join
(select stu_id, subject_id as 第1科 from stu_grade where subject_id='第1科') as b
on a.stu_id=b.stu_id
left outer join
(select stu_id, subject_id as 第2科 from stu_grade where subject_id='第2科') as c
on a.stu_id=c.stu_id
left outer join
(select stu_id, subject_id as 第3科 from stu_grade where subject_id='第3科') as d
on a.stu_id=d.stu_id
left outer join
(select stu_id, sum(subject_id) as sum from stu_grade group by stu_id) as e
on a.stu_id=e.stu_id
凑个热闹.
from(select a.stu_id,a.stu_name,a.第1科,b.第2科)
from(select a.stu_id,a.stu_name,b.第1科)
from (select stu_id, stu_name total=sum(stu_) from stu_grade
group by stu_id,stu_name) a
left outer join
(select stu_id,stu_name,第1科=sum(stu_) from stu_grade
where subject_id='第1科' group by stu_id,stu_name) b
on a.stu_id=b.stu_id and a.stu_name= b.stu_name) a
left outer join
(select stu_id,stu_name,第2科=sum(stu_) from stu_grade
where subject_id='第2科' group by stu_id,stu_name) b
on a.stu_id=b.stu_id and a.stu_name= b.stu_name) a
left outer join
(select stu_id,stu_name,第3科=sum(stu_) from stu_grade
where subject_id='第3科' group by stu_id,stu_name) b
on a.stu_id=b.stu_id and a.stu_name= b.stu_name
此为外联,可以得到如下效果
--------------------------------------------------
stu_id, stu_name , 第1科 , 第2科 , 第3科 ,total
1 a 45 50 52 147
2 b 12 89 56 157
3 c null 34 null 34
4 d null null null 0
--------------------------------------------------
select a.stu_id,a.stu_name,a.total,a.第1科,a.第2科,b.第3科
from(select a.stu_id,a.stu_name,a.total,a.第1科,b.第2科)
from(select a.stu_id,a.stu_name,a.total,b.第1科)
from (select stu_id, stu_name ,total=sum(stu_) from stu_grade
group by stu_id,stu_name) a
left outer join
(select stu_id,stu_name,第1科=sum(stu_) from stu_grade
where subject_id='第1科' group by stu_id,stu_name) b
on a.stu_id=b.stu_id and a.stu_name= b.stu_name) a
left outer join
(select stu_id,stu_name,第2科=sum(stu_) from stu_grade
where subject_id='第2科' group by stu_id,stu_name) b
on a.stu_id=b.stu_id and a.stu_name= b.stu_name) a
left outer join
(select stu_id,stu_name,第3科=sum(stu_) from stu_grade
where subject_id='第3科' group by stu_id,stu_name) b
on a.stu_id=b.stu_id and a.stu_name= b.stu_name
此为外联,可以得到如下效果
--------------------------------------------------
stu_id, stu_name , 第1科 , 第2科 , 第3科 ,total
1 a 45 50 52 147
2 b 12 89 56 157
3 c null 34 null 34
4 d null null null 0
--------------------------------------------------
declare @strsql varchar(8000)
declare @strInsert varchar(8000)
declare @strSelect varchar(8000)
declare cur_get cursor for
select subject_id from
stu_grade group subject_iddeclare @subject_id varchar(30)
select @strsql='create table #stu_grade(stu_id int null,stu_name varchar(20) null, '
select @strInsert='insert into #stu_grade(stu_id,stu_name,'
select @strSelect='select stu_id,stu_name,'
fetch cur_get into @subject_id
while @@fetch_status=0
begin
select @strsql=@strsql+'['+@subject_id+'] decimal(28,2) null,'
select @strInsert=@strInsert+'['+@subject_id+'],'
select @strSelect=@strSelect+'sum(select stu_ from stu_grade where stu_grade.stu_id=stu_gradeA.stu_id and stu_grade.subject_id='+char(39)+@subject_id+char(39)+'),'
fetch cur_get into @subject_id
end
close cur_get
DEALLOCATE cur_get
select @strInsert=@strInsert+'[sum])'
select @strSelect=@strSelect+' sum(stu_) as stu_ from stu_grade as stu_gradeA group by stu_id, stu_name'
select @strsql=@strsql+'[sum] decimal(28,10) null)'+char(13)+char(10)
select @strsql=@strsql+ @strInsert+char(13)+char(10)+@strSelect
select @strsql=@strsql+'select * from #stu_grade'
select @strsql=@strsql+char(13)+char(10)+'drop table #stu_grade'exec(strsql)