（超紧急！）高手请进！求一个高难的sql语句！（100分送上）

Calian 2005-01-09 07:23:41
表1：（学生）
stno stname
0001 姓名1
0002 姓名2
0003 姓名3
.... ....

表2：（成绩）
stno sub year xueqi type chengji
0001 数学 2004 第一学期 平时 1
0001 数学 2004 第一学期 期中 2
0001 数学 2004 第一学期 期末 3
0001 数学 2005 第二学期 期中 4
0001 语文 2004 第一学期 平时 5
0001 语文 2004 第一学期 期中 6
0001 语文 2004 第一学期 期末 7
0001 语文 2005 第二学期 期中 8
0002 数学 2004 第一学期 平时 9
0002 数学 2004 第一学期 期中 10
0002 数学 2004 第一学期 期末 11
0002 数学 2005 第二学期 期中 12
0002 语文 2004 第一学期 平时 13
0002 语文 2004 第一学期 期中 14
0002 语文 2004 第一学期 期末 15
0002 语文 2005 第二学期 期中 16
0003 数学 2004 第一学期 平时 17
0003 数学 2004 第一学期 期中 18
0003 数学 2004 第一学期 期末 19
0003 数学 2005 第二学期 期中 20
0003 语文 2004 第一学期 平时 21
0003 语文 2004 第一学期 期中 22
0003 语文 2004 第一学期 期末 23
0003 语文 2005 第二学期 期中 24
.... .... .... .... .... ....

问题：
写一个sql语句得到2004年第一学期的成绩并显示为如下的一个结果：
stno stname kemu1type1 kemu1type2 kemu1type3 kemu2type1 kemu2type2 kemu2type3 ...
0001 姓名一 1 2 3 5 6 7 ...
0002 姓名二 9 10 11 13 14 15 ...
0003 姓名三 17 18 19 21 22 23 ...
... ... ... ... ... ... ... ... ...
学号 姓名 数学平时 数学期中 数学期末 语文平时 语文期中 语文期末 其他科目...

注意：
1、学生可能没有考试，而导致成绩表里没有记录，此时成绩相应为0。
2、科目数量不一定（sub字段，不只数学，语文，而且事先不知道有多少科），但考试类型一定（type字段，只有平时、期中、期末三种类型）。
3、其实现实的问题比这个还要严重一点，因为还有总和，比例的问题，不过如果能够得到我问的答案，我已经很满足了，其他的我自己可以想想办法。

谢谢！本菜鸟的sql语句购差劲的，哎~~~~~~~~~~~！
100分一定送上，如果觉得不够，可以再另贴给！我还有1000多分！分我无所谓了，就要答案。
谢谢！十二万分的感谢！而且问题非常紧急！
...全文
288 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
Softlee81307 2005-01-10
  • 打赏
  • 举报
 回复
---------學生表----------------
Create Table LXQ(
stno varchar(4),
stname varchar(10)
)

Create Table KX(
stno varchar(4),
sub varchar(4),
year1 int,
xueqi varchar(10),
type varchar(4),
chengji int
)
---------------insert data-------
insert into lxq values ('0001', ' 姓名1')
insert into lxq values ('0002', '姓名2')
insert into lxq values ('0003', '姓名3')
--------------------insert the second ----------------
insert into KX values('0001', '數學', 2004, '第一學期', '平時', 1)
insert into KX values('0001', '數學', 2004, '第一學期', '期中', 2)
insert into KX values('0001', '數學', 2004, '第一學期', '期末', 3)
insert into KX values('0001', '數學', 2005, '第二學期', '期中', 4)
insert into KX values('0001', '語文', 2004, '第一學期', '平時', 5)
insert into KX values('0001', '語文', 2004, '第一學期', '期中', 6)
insert into KX values('0001', '語文', 2004, '第一學期', '期末', 7)
insert into KX values('0001', '語文', 2005, '第二學期', '期中', 8)
insert into KX values('0002', '數學', 2004, '第一學期', '平時', 9)
insert into KX values('0002', '數學', 2004, '第一學期', '期中', 10)
insert into KX values('0002', '數學', 2004, '第一學期', '期末', 11)
insert into KX values('0002', '數學', 2005, '第二學期', '期中', 12)
insert into KX values('0002', '語文', 2004, '第一學期', '平時', 13)
insert into KX values('0002', '語文', 2004, '第一學期', '期中', 14)
insert into KX values('0002', '語文', 2004, '第一學期', '期末', 15)
insert into KX values('0002', '語文', 2005, '第二學期', '期中', 16)
insert into KX values('0003', '數學', 2004, '第一學期', '平時', 17)
insert into KX values('0003', '數學', 2004, '第一學期', '期中', 18)
insert into KX values('0003', '數學', 2004, '第一學期', '期末', 19)
insert into KX values('0003', '數學', 2005, '第二學期', '期中', 20)
--------------------------------------------------------------------
select * from KX
----------------------下面是實現的語句-----------------------
Declare @k varchar(1000)
set @k='Create table ck(stno varchar(4),stname varchar(10),'
select @k=@k+sub+type+' int,' from kx group by sub,type
set @k=substring(@k,1,len(@k)-1)
set @k=@k+')'
exec( @k)
Insert Into ck(stno,stname)
select stno,stname from lxq
-------------------------------------------------------------
Declare @sub varchar(8),@sql varchar(1000)
Declare @stno varchar(4),@cheng int
Declare kk cursor for
Select (sub+type) as vj,chengji,stno from KX where year1=2004 and xueqi='第一學期'
open kk
fetch next from kk into @sub,@cheng,@stno
while @@fetch_status=0
begin
set @sql='update ck set '+@sub+'=isnull('+@sub +',0) '
exec (@sql)
set @sql='update ck set '+@sub+'='+cast(@cheng as varchar(2)) +' where stno='''+@stno+''''
exec( @sql)
fetch next from kk into @sub,@cheng,@stno
end
close kk
deallocate kk
-------------------------------------到此結束，下面ck表是輸出----------------------
select * from ck

drop table ck
drop table lxq
drop table kx
Calian 2005-01-10
  • 打赏
  • 举报
 回复
to lavare():
能否稍微讲解一下?
因为我虽然在测试数据库中通过了测试,可是我在真实数据库中却出了错误.
分一定给!

非常感谢!
还有,如果还要分,还可以再给(1000内).

再次说声感谢!
long_205 2005-01-10
  • 打赏
  • 举报
 回复
DECLARE @stmt varchar(8000)

SET @stmt='SELECT students.stno,students.stname'

SELECT @stmt=@stmt+',SUM(CASE WHEN records.sub='''+sub+''' AND records.type='''+type+''' THEN ISNULL(records.chengji,0) ELSE 0 END) '''+sub+type+''''
FROM (SELECT DISTINCT sub,type FROM records) t

SET @stmt=@stmt+' FROM students INNER JOIN records ON students.stno=records.stno WHERE records.subYear=''2004'' AND records.xueqi=''第一学期'' GROUP BY students.stno,students.stname ORDER BY students.stno'

EXECUTE(@stmt)
xf8zbf 2005-01-10
  • 打赏
  • 举报
 回复
是有难度呀，做下标记先
Calian 2005-01-10
  • 打赏
  • 举报
 回复
lavare和revision可以找我要分。
前者是第一个写出可以测试通过的代码的。
后者是昨天晚上说了给答案而后来的确给了答案的。
如果你们两觉得分少了，可以给我留言，我另贴给分。（lavare650以内，revision350以内。）
ReViSion 2005-01-10
  • 打赏
  • 举报
 回复
Declare @sqlstr varchar(3000)

Set @sqlstr='Select cj.stno,S.stName '

select @sqlstr=@sqlstr+',isnull(max(case sub+type when '''+sub+''' then cj end),0) ['+sub+']'
from (Select distinct sub+type sub from cj)a
set @sqlstr=@sqlstr+ ' from 成绩表 cj inner join 学生资料表 S on s.stno
=cj.stno Group by cj.stno,s.stname '
exec(@sqlstr)
Calian 2005-01-10
  • 打赏
  • 举报
 回复
算了，其实还不只上面说的,比上面说的还要难一点
我现在决定改条路试一试了,还是用临时表吧.
Calian 2005-01-10
  • 打赏
  • 举报
 回复
lavare()
谢谢你了
用你的方法实现了我帖子上的问题
而且也已经移植成功
并做了部分修改
也成功了
可是修改到最后
还是有了难题
上面的需求中现在要加上一些字段了

kemu1zongping kemu2zongping
6 18
30 42
54 66
... ...

所以我现在感觉嘴里在泛苦,比黄莲都苦的感觉.
哎~~~~~~~!
lavare 2005-01-10
  • 打赏
  • 举报
 回复
to Calian(无聊的人啊):

你可以在最后用print @stmt看看最后生成的语句，拷贝出来执行那条语句，看看有什么错误提示？
Calian 2005-01-10
  • 打赏
  • 举报
 回复
Softlee81307(孔腎) 也是很猛啊

不过我现在已经把第一个答案应用于实际了
只是还有点纰漏:
我还需要进行一个成绩的总评,就是还要把每一种期中、期末、平时这三个成绩乘以各自的百分比并相加然后形成一个新的名为科目总评的字段在里面

苦啊
比黄莲还苦

谢谢楼上所有的了
学会了不少的东西
ouyangwu 2005-01-09
  • 打赏
  • 举报
 回复
CUBE 的 GROUP BY 和带有 ROLLUP 的 GROUP BY 请查看帮助了，呵呵
ouyangwu 2005-01-09
  • 打赏
  • 举报
 回复
楼上的方法挺好

交叉数据报表
有时候需要旋转结果以便在水平方向显示列，而在垂直方向显示行。这就是所谓的创建 PivotTable®、创建交叉数据报表或旋转数据。

假定有一个表 Pivot，其中每季度占一行。对 Pivot 的 SELECT 操作在垂直方向上列出这些季度：

Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4

生成报表的表必须是这样的，其中每年占一行，每个季度的数值显示在一个单独的列中，如：

Year
Q1
Q2
Q3
Q4

1990
1.1
1.2
1.3
1.4

1991
2.1
2.2
2.3
2.4



下面的语句用于创建 Pivot 表并在其中填入第一个表中的数据：

USE Northwind
GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

下面是用于创建旋转结果的 SELECT 语句：

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

该 SELECT 语句还处理其中每个季度占多行的表。GROUP BY 语句将 Pivot 中一年的所有行合并成一行输出。当执行分组操作时，SUM 聚合中的 CASE 函数的应用方式是这样的：将每季度的 Amount 值添加到结果集的适当列中，在其它季度的结果集列中添加 0。

如果该 SELECT 语句的结果用作电子表格的输入，那么电子表格将很容易计算每年的合计。当从应用程序使用 SELECT 时，可能更易于增强 SELECT 语句来计算每年的合计。例如：

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO

带有 CUBE 的 GROUP BY 和带有 ROLLUP 的 GROUP BY 都计算与本例显示相同的信息种类，但格式稍有不同。
lavare 2005-01-09
  • 打赏
  • 举报
 回复
DECLARE @stmt varchar(8000)

SET @stmt='SELECT students.stno,students.stname'

SELECT @stmt=@stmt+',SUM(CASE WHEN records.sub='''+sub+''' AND records.type='''+type+''' THEN ISNULL(records.chengji,0) ELSE 0 END) '''+sub+type+''''
FROM (SELECT DISTINCT sub,type FROM records) t

SET @stmt=@stmt+' FROM students INNER JOIN records ON students.stno=records.stno WHERE records.subYear=''2004'' AND records.xueqi=''第一学期'' GROUP BY students.stno,students.stname ORDER BY students.stno'

EXECUTE(@stmt)
ReViSion 2005-01-09
  • 打赏
  • 举报
 回复
哦，不好意思看错
edgethinking 2005-01-09
  • 打赏
  • 举报
 回复
難題啊!!

我看你還是多寫一些客戶端(vb?delphi?)代碼去實現算了，sql實現, 我等者看下面的。 :)
Calian 2005-01-09
  • 打赏
  • 举报
 回复
to ReViSion(和尚) :
数学平时 数学期中 数学期末 语文平时
这个当中的数学、语文来自sub字段，所以我感觉对我而言，非常的难。
Calian 2005-01-09
  • 打赏
  • 举报
 回复
超级感谢!明天应该来得及!
这个问题的确是有点棘手!
谢谢您了!
ReViSion 2005-01-09
  • 打赏
  • 举报
 回复
数学平时 数学期中 数学期末 语文平时 这几个怎么分
你给的只有平时，期中，期末
liweijun0829 2005-01-09
  • 打赏
  • 举报
 回复
明天给你行不？回去调试一下先
Calian 2005-01-09
  • 打赏
  • 举报
 回复
顶上去!
高难初中英语竞赛试卷(含答案).docx
高难初中英语竞赛试卷(含答案).docx
八大行业高难废水关键处理技术及典型工艺.pdf
八大行业高难废水关键处理技术及典型工艺.pdf
9月美国CPI点评：美国通胀居高难下.pdf
9月美国CPI点评：美国通胀居高难下.pdf
高难习题集——Problems in Mathematical Analysis, Piotr Biler, Alfred Witkowski, Dekker
高难习题集——Problems in Mathematical Analysis, Piotr Biler, Alfred Witkowski, Dekker,.pdf
国际宏观评论-民生证券-9月美国CPI点评：美国通胀居高难下.pdf
国际宏观评论-民生证券-9月美国CPI点评：美国通胀居高难下.pdf
MS-SQL Server

34,593

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章