|
是这样的.有两个表 第一个表的如下:---------------------- 学号 科目名称 科目编号 成绩 1 数学 01 76 1 语文 02 80 2 化学 03 90 2 英语 04 78 2 数学 01 99 第二个表的如下:------------------------- 科目名称 科目编号 数学 01 语文 02 化学 03 英语 04 数学 01 地理 08 要求生成以下的统计表: 学号 数学 语文 化学 英语 数学 地理 1 88 90 88 78 56 77 2 页且,要生成的表的科目名称不是固定的,是从表二中读出来的, 表二中有多少个科目,那么就要生成多少个列....这样如何得到,不知大家有经验没有? 其实这是交叉表生成的问题 help上有帮助： 交叉数据报表 有时候需要旋转结果以便在水平方向显示列，而在垂直方向显示行。这就是所谓的创建 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 都计算与本例显示相同的信息种类，但格式稍有不同。 3.按平均成绩从高到低顺序,列印所有学生的四门(数学,语文,英语,政治)课程成绩: (就是每个学生的四门课程的成绩单) 学生ID,学生姓名,数学,语文,英语,政治,有效课程数,有效平均分 (注: 有效课程即在 T 表中有该学生的成绩记录,如不明白可不列印"有效课程数"和"有效平均分") 需要说明的是: 题目之所以明确提出"四门(数学,语文,英语,政治)课程"是有道理的, 因为实现时,的确无法避免使原基本表中的行上的数据的值影响列,这又是一个典型的 "行变列"的相关子查询: SELECT 学生ID,MAX(学生姓名) AS 学生姓名 ,(SELECT 成绩 FROM 成绩表 WHERE 学生ID=T.学生ID AND 课程ID='K1') AS 数学 ,(SELECT 成绩 FROM 成绩表 WHERE 学生ID=T.学生ID AND 课程ID='K2') AS 语文 ,(SELECT 成绩 FROM 成绩表 WHERE 学生ID=T.学生ID AND 课程ID='K3') AS 英语 ,(SELECT 成绩 FROM 成绩表 WHERE 学生ID=T.学生ID AND 课程ID='K4') AS 政治 ,COUNT(*) AS 有效课程数, AVG(T.成绩) AS 平均成绩 FROM 成绩表 AS T GROUP BY 学生ID ORDER BY 平均成绩 这可以说也是一个很规矩的解法,在这种应用场合,子查询要比联接代码可读性强得多。 如果数据库引擎认 为把它解析成联接更好,那就由它去吧,其实本来相关子查询也肯定含有连接。 回到原题,再介绍一个比较取巧的办法,仅需一个简单分组查询就可解决问题,有经验的读者可能已经想到了 ,那就是 CASE: SELECT 学生ID, MIN(学生姓名) ,SUM(CASE 课程ID WHEN 'K1' THEN 成绩 ELSE 0 END) AS 数学 ,SUM(CASE 课程ID WHEN 'K2' THEN 成绩 ELSE 0 END) AS 语文 ,SUM(CASE 课程ID WHEN 'K3' THEN 成绩 ELSE 0 END) AS 英语 ,SUM(CASE 课程ID WHEN 'K4' THEN 成绩 ELSE 0 END) AS 政治 ,COUNT(*) AS 有效课程数, AVG(T.成绩) AS 平均成绩 FROM 成绩表 AS T GROUP BY 学生ID ORDER BY 平均成绩 DESC 虽然可能初看答案感觉有点怪,其实很好理解,可读性并不低,效率也很高。但它不能 像前一个答案那样,在成绩中区分出某一门课这个学生究竟是缺考 (NULL),还是真得 零分。这个解法充分利用了 CASE 语句进行数据分类的作用: CASE 将成绩按课程分 成四类,SUM 用来消去多余的 0。 SELECT [T].[学生ID],MAX([T].[学生姓名]) AS 学生姓名 ,MAX([T1].[成绩]) AS 数学,MAX([T2].[成绩]) AS 语文,MAX([T3].[成绩]) AS 英语,MAX([T4].[成绩]) AS 政治, COUNT([T].[课程ID]) AS 有效课程数 ,(ISNULL(MAX([T1].[成绩]),0) + ISNULL(MAX([T2].[成绩]),0) + ISNULL(MAX([T3].[成绩]),0) + ISNULL(MAX([T4].[成绩]),0)) / COUNT([T].[课程ID]) AS 有效平均分 FROM 成绩表 T LEFT JOIN 成绩表 AS [T1] ON [T].[学生ID] = [T1].[学生ID] AND [T1].[课程ID] = 'K1' LEFT JOIN 成绩表 AS [T2] ON [T].[学生ID] = [T2].[学生ID] AND [T2].[课程ID] = 'K2' LEFT JOIN 成绩表 AS [T3] ON [T].[学生ID] = [T3].[学生ID] AND [T3].[课程ID] = 'K3' LEFT JOIN 成绩表 AS [T4] ON [T].[学生ID] = [T4].[学生ID] AND [T4].[课程ID] = 'K4' GROUP BY [T].[学生ID] ORDER BY 有效平均分 DESC 这个方法是相当正统的联接解法,尽管写起来麻烦了些,但还是不难理解的。再从实用 角度考虑一下,真实需求往往不是象本题明确提出"列印四门 (数学,语文,英语,政治) 课程"这样的相对静态的需求,该是动态 SQL 大显身手的时候了,很明显方法一的写法 无疑是利用程序构造动态 SQL 的最好选择,当然另两个 SQL 规律还是挺明显的,同样 不难构造。以 CASE 版答案为例: 先用一个游标遍历,取出所有课程凑成: SUM(CASE '课程ID' WHEN '课程名称' THEN 成绩 ELSE 0 END) AS 课程名称 形式, 再补上 SELECT 和 FROM、WHERE 等必要条件,一个生成动态成绩单的 SQL 就诞生了, 只要再由相关程序调用执行即可,这样就可以算一个更完善的解决方案了。 动态生成报表
提供一个例子供参考 CREATE PROCEDURE pPostBagReqBranchDayDetail @dOperDate smalldatetime AS begin --动态生成所有种类邮袋的请领报表 declare @nvcSqlString1 nvarchar(3000) declare @nvcSqlString2 nvarchar(3000) declare @nvcColumns nvarchar(20) declare @nvcColumns2 nvarchar(20) set @nvcSqlString1='create table ##tPostBagReqBranchDayDetail('+char(13) set @nvcSqlString1=@nvcSqlString1+'BranchNo char(6)'+char(13) begin DECLARE cDayDetail1 CURSOR FOR SELECT cast(KindNo as nvarchar(20)) FROM PostBag.dbo.tPostBagKind OPEN cDayDetail1 FETCH NEXT FROM cDayDetail1 into @nvcColumns WHILE @@FETCH_STATUS = 0 BEGIN set @nvcSqlString1=@nvcSqlString1+',Bag'+@nvcColumns+' smallint default 0 not null'+char(13) FETCH NEXT FROM cDayDetail1 into @nvcColumns END CLOSE cDayDetail1 DEALLOCATE cDayDetail1 set @nvcSqlString1=@nvcSqlString1+')' EXEC sp_executesql @nvcSqlString1 insert ##tPostBagReqBranchDayDetail (BranchNo) select distinct BranchNo from tPostBagRequire where PostSys.dbo.fnDate(OperDate)=@dOperDate end begin DECLARE cDayDetail2 CURSOR FOR SELECT cast(KindNo as nvarchar(20)) FROM PostBag.dbo.tPostBagKind OPEN cDayDetail2 FETCH NEXT FROM cDayDetail2 into @nvcColumns2 WHILE @@FETCH_STATUS = 0 BEGIN set @nvcSqlString2='' set @nvcSqlString2='update a'+char(13) set @nvcSqlString2=@nvcSqlString2+'set '+'Bag'+@nvcColumns2+'=b.TotalNumber'+char(13) set @nvcSqlString2=@nvcSqlString2+'from ##tPostBagReqBranchDayDetail a'+char(13) set @nvcSqlString2=@nvcSqlString2++',PostBag.dbo.fnReqGrpBranchDayKind('+''''+left(convert(nvarchar(50),@dOperDate,20),10)+''''+')'+' b '+char(13) set @nvcSqlString2=@nvcSqlString2+'where a.BranchNo=b.BranchNo and b.KindNo='+@nvcColumns2 EXEC sp_executesql @nvcSqlString2 FETCH NEXT FROM cDayDetail2 into @nvcColumns2 END CLOSE cDayDetail2 DEALLOCATE cDayDetail2 end end GO EXECUTE pPostBagReqBranchDayDetail '2002-7-17' GO |
|
|
来自： icecity1306 > 《数据库资料》
