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) tSET @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)
下面的语句用于创建 Pivot 表并在其中填入第一个表中的数据:USE Northwind GOCREATE 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 都计算与本例显示相同的信息种类,但格式稍有不同。
CUBE 的 GROUP BY 和带有 ROLLUP 的 GROUP BY 请查看帮助了,呵呵
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) tSET @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)
to lavare(): 能否稍微讲解一下? 因为我虽然在测试数据库中通过了测试,可是我在真实数据库中却出了错误. 分一定给! 非常感谢! 还有,如果还要分,还可以再给(1000内).
再次说声感谢!
---------學生表---------------- 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 ckdrop table ck drop table lxq drop table kx
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)
FROM (SELECT DISTINCT sub,type FROM records) tSET @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)
有时候需要旋转结果以便在水平方向显示列,而在垂直方向显示行。这就是所谓的创建 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
GOCREATE 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 都计算与本例显示相同的信息种类,但格式稍有不同。
FROM (SELECT DISTINCT sub,type FROM records) tSET @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)
能否稍微讲解一下?
因为我虽然在测试数据库中通过了测试,可是我在真实数据库中却出了错误.
分一定给! 非常感谢!
还有,如果还要分,还可以再给(1000内).
再次说声感谢!
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 ckdrop table ck
drop table lxq
drop table kx
只是还有点纰漏:
我还需要进行一个成绩的总评,就是还要把每一种期中、期末、平时这三个成绩乘以各自的百分比并相加然后形成一个新的名为科目总评的字段在里面苦啊
比黄莲还苦谢谢楼上所有的了
学会了不少的东西
谢谢你了
用你的方法实现了我帖子上的问题
而且也已经移植成功
并做了部分修改
也成功了
可是修改到最后
还是有了难题
上面的需求中现在要加上一些字段了kemu1zongping kemu2zongping
6 18
30 42
54 66
... ...所以我现在感觉嘴里在泛苦,比黄莲都苦的感觉.
哎~~~~~~~!
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)
前者是第一个写出可以测试通过的代码的。
后者是昨天晚上说了给答案而后来的确给了答案的。
如果你们两觉得分少了,可以给我留言,我另贴给分。(lavare650以内,revision350以内。)