巨难的问题.....相信大家都可能会遇到, 将打竖的数据变成打横的数据的问题
巨难的问题.....相信大家都可能会遇到, 将打竖的数据变成打横的数据的问题
是这样的.有两个表第一个表的如下:----------------------&&学号&&&科目名称&&科目编号&&&&成绩&&&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.11990&&&&&&2&&&&&&&&&&&1.21990&&&&&&3&&&&&&&&&&&1.31990&&&&&&4&&&&&&&&&&&1.41991&&&&&&1&&&&&&&&&&&2.11991&&&&&&2&&&&&&&&&&&2.21991&&&&&&3&&&&&&&&&&&2.31991&&&&&&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&NorthwindGOCREATE&TABLE&Pivot(&Year&&&&&&SMALLINT,&&Quarter&&&TINYINT,&&&Amount&&&&&&DECIMAL(2,1)&)GOINSERT&INTO&Pivot&VALUES&(.1)INSERT&INTO&Pivot&VALUES&(.2)INSERT&INTO&Pivot&VALUES&(.3)INSERT&INTO&Pivot&VALUES&(.4)INSERT&INTO&Pivot&VALUES&(.1)INSERT&INTO&Pivot&VALUES&(.2)INSERT&INTO&Pivot&VALUES&(.3)INSERT&INTO&Pivot&VALUES&(.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&Q4FROM&Northwind.dbo.PivotGROUP&BY&YearGO该&SELECT&语句还处理其中每个季度占多行的表。GROUP&BY&语句将&Pivot&中一年的所有行合并成一行输出。当执行分组操作时，SUM&聚合中的&CASE&函数的应用方式是这样的：将每季度的&Amount&值添加到结果集的适当列中，在其它季度的结果集列中添加&0。如果该&SELECT&语句的结果用作电子表格的输入，那么电子表格将很容易计算每年的合计。当从应用程序使用&SELECT&时，可能更易于增强&SELECT&语句来计算每年的合计。例如：SELECT&P1.*,&(P1.Q1&+&P1.Q2&+&P1.Q3&+&P1.Q4)&AS&YearTotalFROM&(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&P1GO带有&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&TGROUP&BY&学生IDORDER&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&TGROUP&BY&学生IDORDER&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&smalldatetimeASbegin--动态生成所有种类邮袋的请领报表&&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&&endendGOEXECUTE&pPostBagReqBranchDayDetail&''GO
发表评论：
TA的最新馆藏智能手机教程子分类QQ表情中小拇指竖起是什么意思_百度知道怎样把QQ网名改为竖着显示的？怎样在网名上加入QQ表情？_百度知道