Access911.net   |   a9BBS   |   OTaA System  
  搜索文章:  
Access911欢迎您光临  
   主页      上传      繁體版       论坛     
设为首页  |  加入收藏   
  
你现在的位置:文章索引 -> 文章分类 -> 查询  
 首页|  近日更新|  下载  |  文章索引  |  搜索|  术语|  承接工程|  
 
系统正在加载内容,请耐心等待...
 
 查询
 窗体
 报表
 
 
 VBA
 函数
 ADO/DAO/ADO.NET
 API
 ADP
 安全
 发布
 OA
 ASP/ASP.NET
 其他语言
 控件
 DELPHI
 C#/.Net
 本站
 其他
 小例程
 常用软件
 参考文档
 业主作品
 网友大作
 
 
友情链接
 access911.net
 
访问人次
 1702023
 
站长 E-Mail
 net911@sina.com
 access911@gmail.com
 
RSS 订阅

显示附加信息 >>>

分段或者交叉统计的查询语句怎么写?

作者:cg1  摘自:access911.net  :cg1  更新日期:2006-3-10  浏览人次:

 

问题:


现在有一个表,结构如下:

姓名       班级       科目      分数
王五       初一3班    语文        70
王五       初一3班    数学        80
王五       初一3班    英语        50
周天       初一3班    数学       100
李小       初一2班    语文        99
李小       初一2班    物理       100

想用 JET SQL 语句生成类似以下结构的结果,如何生成?

班级    科目   总分   不及格人次    60到80分人次    80到90分人次    90到100分人次

 

回答:


虽然文字很多,但是并不复杂,请认真仔细查看
上述情况的特点是分段不均匀且可能分段段数很多,比如 60-70 70-80 80-90 90-95 95-100。可以使用 access中的 IIF 和 SWITCH 来实现,但是 access911 推荐使用 jet sql 分步来实现。

首先获取第一段的人次(不及格人次)

select 姓名,班级,科目,分数,1 as 不及格人次 from 表 where 分数< 60

但是这样只有一个列显示不及格人次,其他人次没办法显示,其实只要使用一个简单的技巧就可以得到其他列,我们可以用一个固定的数字代表其他列,比如 0

select 姓名,班级,科目,分数,1 as 不及格人次,0 as 60到80分人次,0 as 80到90分人次,0 as 90到100分人次 from 表 where 分数< 60

然后获得获取第二段的人次(60到80分人次)

select 姓名,班级,科目,分数,0 as 不及格人次,1 as 60到80分人次,0 as 80到90分人次,0 as 90到100分人次 from 表 where 分数>=60 and 分数< 80

注意上述语句将固定值“1”移到了“60到80分人次”列,其他列都用固定值“0”代替
好了,这样我们已经得到了两个分段,如何将其组合在一起呢?用 JET SQL 的 UNION ALL 就可以了

select 姓名,班级,科目,分数,1 as 不及格人次,0 as 60到80分人次,0 as 80到90分人次,0 as 90到100分人次 from 表 where 分数< 60 union all
select 姓名,班级,科目,分数,0 as 不及格人次,1 as 60到80分人次,0 as 80到90分人次,0 as 90到100分人次 from 表 where 分数>=60 and 分数< 80

好了,再用同样的原理我们获取所有分段

select 姓名,班级,科目,分数,1 as 不及格人次,0 as 60到80分人次,0 as 80到90分人次,0 as 90到100分人次 from 表 where 分数< 60 union all
select 姓名,班级,科目,分数,0 as 不及格人次,1 as 60到80分人次,0 as 80到90分人次,0 as 90到100分人次 from 表 where 分数>=60 and 分数< 80 union all
select 姓名,班级,科目,分数,0 as 不及格人次,0 as 60到80分人次,1 as 80到90分人次,0 as 90到100分人次 from 表 where 分数>=80 and 分数< 90 union all
select 姓名,班级,科目,分数,0 as 不及格人次,0 as 60到80分人次,0 as 80到90分人次,1 as 90到100分人次 from 表 where 分数>=90 and 分数<=100

 

执行结果如下:

姓名   班级       科目   分数    不及格人次    60到80分人次    80到90分人次    90到100分人次
王五   初一3班    语文     70            0             1               0               0
王五   初一3班    数学     80            0             0               1               0
王五   初一3班    英语     50            1             0               0               0
周天   初一3班    数学    100            0             0               0               1
李小   初一2班    语文     99            0             0               0               1
李小   初一2班    物理    100            0             0               0               1

但是如果需要得到合计人数怎么办?直接用 SELECT ... GROUP BY  语句就可以做到

select 班级,科目,SUM(分数) as 总分,sum(不及格人次),sum(60到80分人次),sum(80到90分人次),sum(90到100分人次) from ([刚才的执行结果]) group by 班级,科目

即可,好了,我们将刚才的 SQL 语句替换进去可以得到

select 班级,科目,SUM(分数) as 总分,sum(不及格人次),sum(60到80分人次),sum(80到90分人次),sum(90到100分人次) from (select 姓名,班级,科目,分数,1 as 不及格人次,0 as 60到80分人次,0 as 80到90分人次,0 as 90到100分人次 from 表 where 分数< 60 union all
select 姓名,班级,科目,分数,0 as 不及格人次,1 as 60到80分人次,0 as 80到90分人次,0 as 90到100分人次 from 表 where 分数>=60 and 分数< 80 union all
select 姓名,班级,科目,分数,0 as 不及格人次,0 as 60到80分人次,1 as 80到90分人次,0 as 90到100分人次 from 表 where 分数>=80 and 分数< 90 union all
select 姓名,班级,科目,分数,0 as 不及格人次,0 as 60到80分人次,0 as 80到90分人次,1 as 90到100分人次 from 表 where 分数>=90 and 分数<=100 ) group by 班级,科目

 

问题:

查询一个字段中值分别0-3,3-6,6-15,15-30,>30的记录的个数怎么做?

 

回答:

非常简单,你要做的只是写不同的WHERE 子句,然后将他们组合起来而已。用 ASNI SQL 就可以做到。

select 呼叫 as 小于3,0 as 大于3小于6,0 as  大于6小于15,0 as 大于15小于30,0 as 大于30 from where 时长<3

union all

select 0 小于3,呼叫 as 大于3小于6,0 as  大于6小于15,0 as 大于15小于30,0 as 大于30 from where 时长>3 and 时长<6

union all ....


一直持续下去

如果你要汇总只要写一个 sum语句即可


select sum(小于3),sum(大于3小于6),sum(大于6小于15),sum(大于15小于30),sum( 大于30 ) from (

select 呼叫 as 小于3,0 as 大于3小于6,0 as  大于6小于15,0 as 大于15小于30,0 as 大于30 from where 时长<3

union all

select 0 小于3,呼叫 as 大于3小于6,0 as  大于6小于15,0 as 大于15小于30,0 as 大于30 from where 时长>3 and 时长<6

union all ....)


太复杂了吧,有没有简单点的,用IIF什么的?
回答上述问题:请注意!用 IIF 才是最复杂的,因为它涉及到 JET DB / ACCESS JET SQL /VBA 三方面!而用 UNION ALL 只涉及到 SQL,该语句通用性非常强

 

本站文章旨在为该问题提供解决思路及关键性代码,并不能完成应该由网友自己完成的所有工作,请网友在仔细看文章并理解思路的基础上举一反三、灵活运用。

access911.net 原创文章,作者本人对文章保留一切权利。
如需转载必须征得作者同意并注明本站链接

 

 
相关文章
     没有手动相关文章
 
评论
     查看或发表更多的评论,请单击这里。
 
 
 
 
 
   
  Access911.net   |   a9BBS   |   OTaA System   |
建站日期:2000年4月2日  |  设计施工:陈格 ( access911 & cg1 )
 Copyright © 2000 - 2003 COMET, 陈格 保留所有权利