SQL语句挑战透视表

none 2000-01-24 03:54:00

假设一个表中的列设置如下:学生,科目,成绩.

用PB的CROSSTAB可以实现以科目为列,学生为行的成绩单,如果数据存储在EXCEL文件里,
可以用透视表来解决.

目前的目标是:仅用SQL语句实现透视表的效果.

目前的进展是:如果科目是已知且固定的(三科:数理化),可以实现(用CASE)

目前的问题是:写出通用的SQL语句,无论表中的科目有多少,科目名称是什么,都能
达到透视表的效果

我已经找了一周了,实在不行,向各位大虾求助!

...全文

365 8 打赏收藏转发到动态举报

写回复

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

none 2000-02-16

打赏
举报

回复

我写了一个存储过程解决了这个问题，用到了动态SQL和光标
谢谢各位的帮助！

july 2000-02-14

打赏
举报

回复

use temp table

none 2000-02-12

打赏
举报

回复

seeker,

不使用CURSOR如何将科目加到CASE中，可否说得再详细一些？

谢谢

seeker 2000-01-30

打赏
举报

回复

我写过很多这种东西，一般可以通过动态生成一个SQL语句完成，
先读出有多少科目，动态生成CASE，把学生成绩按科目分类，然后，
再在综合起来，通过EXEC执行。

无需临时表、cursor。

xeen4 2000-01-28

打赏
举报

回复

你如果要得知每个学生的各科分数。
可以建立科目表，并对科目表和学生成绩表作关联查询。

btw:什么是透视表？？？？？？？？？？？？

Michael 2000-01-27

打赏
举报

回复

如果使用SQL Server，可用Cross Join试试。

rmh 2000-01-26

打赏
举报

回复

1.Create table temp
2.declare cursor for distinct item
3.while fetch itemname
4.alter table add column itemname char(4)(using dynamic SQL)
5.update/insert(first time) temp itemname= select
请考虑以上描述，test...

rmh 2000-01-24

打赏
举报

回复

用动态SQL试试

献给那些将数据透视表和SQL结合又不知道入门的朋友

透视数据表SQL语句入门透视数据表SQL语句入门

SQL+数据透视表+VBA 使数据透视表走向更灵活，更智能，更适用。这个是我和师傅一撇首度合作，他提供了文件并提出了要求，我帮他实现其效果下面从几个方面解释一下： 1、功能一个源文件和一个通过用SQL查询生成的数据透视表将源文件拖到电脑的任意位置，甚至将文件名也改掉，用VBA配上代码和窗体找到文件，数据透视表仍然能够正常工作 2、套用现在来讲讲怎么使做出来的东东适应大家的需要 2、1 用OLE DB窗口引用工作表或写SQL语句，因为用这个方法同VBA相通，copy下来代码区的的语句 2、2 打开透视表文件，将透视表中的字段全部拖出来，也就是变成一个空数据透视表。右击下面工作表图标或者工具》宏》visual basic 编辑器，点击模块看到代码区 2、3 将2、1步骤copy的语句commandtext的数据Array中的引号中 .CommandText = Array(" ") 可能不同版本会有一些差别，同时SQL语句中如果添加了文本生成新字段，双引号要成对翻倍如："出库" AS 表单选项要改成 ""出库"" AS 表单选项 2、4 语句太长的处理：在代码区如果你想好看一些，你可以插入“ _”来换行，当然不能插在一个单词或自动名等中间。 2、5 将文件存盘，重新打开就会有了数据，你可以将字段拖入数据透视表中，创建你自己的数据透视表， 2、6 这样文件就可以使用，相信VBA的引导不用教就可以交给别人使用了下面附上代码，包含3个区： 1、工作簿去，打开文件时工作 Private Sub Workbook_Open() Dim OP If Dir(Sheets("path").Range("A1")) = "" Then OP = MsgBox("源文件已被移走，请选择下列选项" + Chr(10) + "1、选择是，重新输入文件全名" + Chr(10) + "2、选择否，打开原有的数据透视表" + Chr(10) + "3、选择取消，关闭文件", vbYesNoCancel, "Scarlett温馨提示") If OP = vbYes Then UserForm1.Show End If If OP = vbNo Then ActiveWorkbook.Close True End If If OP = vbCancel Then Exit Sub End If Else Call refreshpv End If End Sub 2、窗体区，实现文件的查找 Private Sub CommandButton1_Click() Dim fopen As FileDialog Set fopen = Application.FileDialog(msoFileDialogFilePicker) fopen.Show TextBox1.Value = fopen.SelectedItems(1) Set fopen = Nothing End Sub Private Sub CommandButton2_Click() If InStr(TextBox1.Value, ".") > 0 Then Sheets("path").Range("A1") = TextBox1.Value Call refreshpv unload me Else MsgBox "文件名要带路径含后缀的文件名", "Scarlett_88温馨提示" TextBox1.SetFocus End If End Sub Private Sub CommandButton3_Click() Unload Me End Sub Private Sub TextBox1_Change() End Sub Private Sub UserForm_Activate() End Sub Private Sub UserForm_Click() TextBox1.Value = Sheets("path").Range("A1") End Sub 3、模块区，实现SQL语句的地址更新和刷新数据透视表的数据源 Sub refreshpv() With ActiveSheet.PivotTables("数据透视表1").PivotCache .Connection = Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Sourc

MS Query基础语法讲解：一、单表查询单表查询是指仅涉及一个表的查询 1、查询指定列例1、查询材料进货明细表中的定额名称及规格 SELECT 定额名称,规格 FROM [材料进货明细表$] 注意：在excel中，一个工作表的表示是这样的：[工作表名称$] select的意思是：查询上面那一句SQL语句的意思就是：从表材料进货明细表中取出定额名称及规格的值例2、查询材料进货明细表的详细记录 SELECT * FROM [材料进货明细表$] 等价于： SELECT 序号, 定额名称,规格,日期,单位,数量 FROM [材料进货明细表$] 注意：*的用法 2、查询经过计算的值例3、查询材料进货明细表的定额名称、规格、年份及数量 SELECT 定额名称,规格,year(日期) as 年份,数量 FROM [材料进货明细表$] 注意：里面的年份已经通过了计算的了，成为一个新的变量。加多一列自定义的列A厂： SELECT “A厂” ,定额名称,规格,year(日期) as 年份,数量 FROM [材料进货明细表$] SELECT “A厂” as 工厂 ,定额名称,规格,year(日期) as 年份,数量 FROM [材料进货明细表$] 比较一下上面两句SQL语句的区别，没有as 工厂与有as 工厂的区别二、选择表中若干元组 1、消除取重复的行例4、查询材料进货明细表的定额名称 SELECT 定额名称 FROM [材料进货明细表$] 该查询结果会包含很多重复的行。消除重复行的话，必须指定关键词 distinct SELECT distinct 定额名称 FROM [材料进货明细表$] 2、查询满足条件的元组例5、查询材料进货明细表中单位为“套”的所有记录 SELECT * FROM [材料进货明细表$] where 单位= ‘套’ 本句语句中，要学会where的用法：要查询满足指定条件的元组，可以通过where子句实现。where子句查询条件是：比较： =,>,<,>=,<=,!=,<>,!>,!<;not +上述比较运算符确定范围： between and ,not between and 确定集合：in,not in 字符匹配：like,not like 空值：is null,is not null 多重条件：and,or,not 例6、查询材料进货明细表中数量在50—100之间的所有记录 SELECT * FROM [材料进货明细表$] where 数量 between 50 and 100 如果是不在50-100之间的话，直接改成： SELECT * FROM [材料进货明细表$] where 数量 not between 50 and 100 例7、查询材料进货明细表中单位为“只”或“支”的所有记录 SELECT * FROM [材料进货明细表$] where 单位 in(‘只’,'支’) 3、字符匹配可以用like来实现，通配符%和_ a、%代表任意长度的字符串，如a%b表示以a开头，以b结尾的任意的字符串 b、_代表任意单个字符例8、查询材料进货明细表中定额名称以“天津”开头的所有记录 SELECT * FROM [材料进货明细表$] where 定额名称 like ‘天津%’ 例9、查询材料进货明细表中定额名称以“天津”开头且字符为4个的所有记录 SELECT * FROM [材料进货明细表$] where 定额名称 like ‘天津__’ 例10、查询材料进货明细表中定额名称不以“天津”开头的所有记录 SELECT * FROM [材料进货明细表$] where 定额名称 not like ‘天津%’ 4、涉及空值的查询主要是以:null出现例11、查询材料进货明细表中规格为空的的所有记录 SELECT * FROM [材料进货明细表$] where 规格 is null 5、多条件查询例12、查询材料进货明细表中定额名称为“天津三通”的并且数量大于30的所有记录 SELECT * FROM [材料进货明细表$] where 定额名称 = ‘天津三通’ and 数量>30 三、order by 子句 desc(降序)、asc(升序) 例13、查询材料进货明细表中数量大于30的所有记录，并且要按照数量来降序排列。 SELECT * FROM [材料进货明细表$] where 数量>30 order by 数量 desc 四、聚集函数 count(distinct/all 列名):统计元组个数 sum:求和 avg：求平均值 max:最大值 min:最小值例14、查询材料进货明细表中天津大小头的最大数量。 SELECT max(数量) FROM [材料进货明细表$] where 定额名称 = ‘天津大小头’ 或：SELECT max(数量) as 最大数量 FROM [材料进货明细表$] where 定额名称 = ‘天津大小头’ 2. Excel源数据及分析下载：Excel数据透视表教程：分类百分比 SQL语句： select *, Hz1.分类销量/Hz2.分类销量 as 分类百分比 from (select 品种,分公司, sum(数量) as 分类销量 from [数据$] group by 品种,分公司) Hz1, (select 分公司, sum(数量) as 分类销量 from [数据$] group by 分公司) Hz2 where hz1.分公司=Hz2.分公司 SQL语句解释： select 品种,分公司, sum(数量) as 分类销量 from [数据$] group by 品种,分公司实现对品种、分公司两字段分组的统计求和 select 分公司, sum(数量) as 分类销量 from [数据$] group by 分公司实现对分公司字段分组的统计求和对分公司分组统计求和数是对品种、分公司两字段分组统计求和项目再对不同品种的总就和，也就是后者包含前者。整句语句的意思就是，将两个查询结果作为新的查询表分别命名为Hz1、Hz2，用 ”where hz1.分公司=Hz2.分公司“来组合数据，没有条件的制约的话，将统计的结果再进行除数运算，各品种的分组统计数（分组含品种字段）除以各品种已求和了的分组统计数（分组不含品种字段）求得所占比率，最后在字段单元格设置为百分数就可以了。 3 在Excel中使用MS Query查询外部数据库的内容的优点是：不用设置公式、编写VBA代码、源数据库不用打开。但要注意：源数据库的记录要有字段名，由于设置查询时的路径固定，。因此源数据库文件不能随意移动（如确实要移动可以通过手工修改查询或VBA解决）以下示例采用MS Query在“查询”工作簿中查询关闭的“销售”工作簿中sheet1的指定 “店铺”和指定“颜色”的内容（sheet1有“店铺”、“数量”、“颜色”等字段名及若干数据）。操作前请先确定是否安装有MS Query。附上举例文件，请解压到D盘根目录下：销售.xls为源数据，查询.xls中设置了msquery查询。下载：MS Query查询未打开工作簿的内容例子 1、新建一个工作表，选择菜单【数据】—【导入外部数据】—【新建数据库查询】，界面如图，由于查询excel数据库的内容，因此选择【Excel Files*】并确定； 2、弹出〖选择工作簿〗对话框，选择“销售”工作簿，〖确定〗； 3、弹出〖选择列〗对话框，如果此时弹出“没用内容”，确定后在〖选项〗中将“系统表”勾上。将所选工作簿的各个工作表及工作表中的字段名添加到查询结果中，全选可直接将工作表名添加，〖下一步〗〖下一步〗，选择“在MS query中继续编辑查询”； 4、弹出MS Query查询编辑，点击【显示/隐藏条件】图标，在“条件字段”中添加“店铺”，值改为“[店]”(方括号中内容随意），再添加个条件为“颜色”，值改为“[色]”，点击【将数据返回Excel】 5、回到excel的“导入数据”对话框，点击〖参数〗，选中“店”字段，再选择“从下列单元格中获取数据”，选择一个用来更改查询关键字的单元格（如B1），并勾选“单元格值更改时自动刷新”， “色”字段改成从单元格“B2”中获取，〖确定〗。数据放置位置选择“A3”。〖确定〗。 6、当更改B1和B2单元格的内容（做个数据有效性）时，A3及以下的数据会即时刷新。此法对于需要经常在局域网中查询数据非常方便，比如：数据放在局域网内的一台主机上，通过MS Query即可不打开工作簿查询数据。

禅道上的统计报表非常匮乏，专业版的水晶报表又不划算，自己动手弄了套工时统计的方案还挺好用的希望对大家有所帮助附件就是统计个人名下所有需求，任务和bug的sql语句，以及Excel统计模板操作步骤：建议使用Navicat工具 1.直接复制语句到Navicat查询编辑器 2.复制查询结果到Excel文档（参考压缩包中的Excel模板） 3.使用Excel中自带的插入数据透视表功能做统计报表 sql查询内容包括：以人为经度查询产品需求数量以产品为经度查询各需求所处阶段以项目为经度查询当前所有任务归属人和所处阶段等等

34,588

社区成员

254,588

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

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

+ 用AI写文章