ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 11266|回复: 27

[原创] 香帅说EXCEL 2010 SQL

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2013-4-21 17:28 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:SQL应用
本帖最后由 wuxiang_123 于 2013-4-22 09:32 编辑

在阅读本贴前,请确保完整安装OFFICE2010软件,如果你想与他人分享,请注明出处。
本贴除练习题外,不提供Excel文件(要学好SQL,首先要学会自己虚拟数据进行测试,虚拟记录一般不建议超过10条),
每篇PPT的练习题答案在下一篇PPT中贴出。
欢迎直接在本贴交流讨论,练习题答案可直接提交SQL代码(如非必要,不用传附件,只贴出语句即可)

操作步骤(在导入数据对话框中选择表而非数据透视表):
http://club.excelhome.net/thread-966084-1-1.html

============第一篇PPT SELECT查询============

香帅说EXCEL 2010 SQL-【SELECT查询】.rar (1.4 MB, 下载次数: 1631)
附件练习6的模拟效果不对,已修改,请重新下载。

============  我是华丽的分割线  ============



该贴已经同步到 wuxiang_123的微博


评分

6

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-4-21 20:06 | 显示全部楼层
本帖最后由 moon_2000 于 2013-4-22 16:04 编辑

接触透视表有4年多了,用SQL有两年多。都是用的时候遇到不会的就赶紧查帮助,搜贴子,来论坛。这期间得到很多版主和网友的帮助,自己也学了一些知识。随着会的多,就越来越想用EXCEL,特别是透视表处理日常的一些数据。而在这过程中,我发现很多时候都不用SQL就得用VBA,VBA相对更门外汉,学习SQL多一些,主要是论坛里包括吴版的帖子和ACCESS帮助。但总是用哪儿看哪,不系统,不扎实。这回希望跟着吴版好好学学。
谢谢EH,谢谢吴版!
首先感谢mps777 兄。我想到了表头别名的方法,但不知道怎么写原表头。特别是“部门”是硬回车换行,“员工”还有个#号。全篇的这个都是抄的,不明白为什么这样做
题1
  1. select 部_门 as 部门,`员工#` as 员工,`1` as 第1季度金额,`2` as 第2季度金额,`3` as 第3季度金额,`4` as 第4季度金额 from [查询练习$] where 部_门 is not null
复制代码
  1. select 部_门 as 部门,`员工#` as 员工,`1` as 第1季度金额,`2` as 第2季度金额,`3` as 第3季度金额,`4` as 第4季度金额 from [查询练习$] where 部_门
复制代码


我一开始写的是第一行,用 is not null排除第2行那个空行。结果发现mps777的不用也可以,就拿来试试,二者都行。我想,是不是 "where 部_门"是缺省句式,而完整的应该是“where 部_门 is not null”?
题2
  1. select 部_门 as 部门,`员工#` as 员工,`1` as 第1季度金额,`2` as 第2季度金额,`3` as 第3季度金额,`4` as 第4季度金额 from [查询练习$] where 部_门 ="A"
复制代码
题3
  1. select 部_门 as 部门,sum(`1`) as 第1季度合计金额 from [查询练习$] where 部_门 group by 部_门
复制代码
  1. select 部_门 as 部门,sum(`1`) as 第1季度合计金额 from [查询练习$] group by 部_门
复制代码
                 第一条的结果与要求一致。第二条是我一开始做错的,这句不能排除原数据第2行,就是那个空行。因为有sum,所以必要用group by了。我想是不是不用where 也能排除掉空行(尽管吴版PPT说where是前置约束条件,而group是分组条件)。结果发现不成。这句结果如下图

题3不用where限定

题3不用where限定

这之后,就每题后边乖乖把where给约束上了。

题4
  1. select 部_门 as 部门,`员工#` as 员工,`1` as 第1季度金额,`2` as 第2季度金额,`3` as 第3季度金额,`4` as 第4季度金额 from [查询练习$] where `3`> 700000
复制代码
题5
  1. select 部_门 as 部门,max(`4`) as 第4季度最大金额 from [查询练习$] where 部_门 group by 部_门
复制代码
题6没做出来,原因是钻牛角尖了,一直想用sum函数,错误语句如下:
  1. select 部_门 as 部门,`员工#` as 员工,`1` as 第1季度金额,`2` as 第2季度金额,`3` as 第3季度金额,`4` as 第4季度金额 from [查询练习$] where 部_门 order by sum(`1`,`2`,`3`,`4`) desc
复制代码

  此外,还试了sum(1+2+3+4),sum(`1` to `4`)等几个方法,结果都不行。后来看mps777 兄,是如此easy。


TA的精华主题

TA的得分主题

发表于 2013-4-21 20:36 | 显示全部楼层
在数据连接时,不选择“数据首先包含列标题”
题1:
  1. select f1 as 部门,f2 as 员工,f3 as 第1季度金额,f4 as 第2季度金额,f5 as 第3季度金额,f6 as 第4季度金额 from [查询练习$a3:f]
复制代码
题2:
  1. select f1 as 部门,f2 as 员工,f3 as 第1季度金额,f4 as 第2季度金额,f5 as 第3季度金额,f6 as 第4季度金额 from [查询练习$a3:f] where f1 ="A"
复制代码
题3:
  1. select f1 as 部门,sum(f3) as 第1季度金额 from [查询练习$a3:f] group by f1
复制代码
题4:
  1. select f1 as 部门,f2 as 员工,f3 as 第1季度金额,f4 as 第2季度金额,f5 as 第3季度金额,f6 as 第4季度金额 from [查询练习$a3:f] where f2 in ('丙','丁','戊','庚','辛')
复制代码
题5:
  1. select f1 as 部门,max(f6) as 第4季度金额 from [查询练习$a3:f] group by f1
复制代码
题6:
  1. select f1 as 部门,f2 as 员工,f3 as 第1季度金额,f4 as 第2季度金额,f5 as 第3季度金额,f6 as 第4季度金额 from [查询练习$a3:f] order by f6 desc
复制代码

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-4-21 22:24 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
感谢香帅
哈哈

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-4-22 09:28 | 显示全部楼层
本帖最后由 wuxiang_123 于 2013-4-22 09:31 编辑
mps777 发表于 2013-4-21 20:36
在数据连接时,不选择“数据首先包含列标题”
题1:题2:题3:题4:题5:题6:


题4返回的是第三季度金额>70万的记录。其他题目没问题,可试试不用【HDR=NO]的方法(即不勾选【数据包含列标题),题6更正了,要求是按4个季度(所有季度)金额之和对记录降序排序。

TA的精华主题

TA的得分主题

发表于 2013-4-22 09:51 | 显示全部楼层
wuxiang_123 发表于 2013-4-22 09:28
题4返回的是第三季度金额>70万的记录。其他题目没问题,可试试不用【HDR=NO]的方法(即不勾选【数据包含 ...

不勾选【数据包含列标题】
题1:
  1. select 部_门 as 部门,`员工#` as 员工,`1` as 第1季度金额,`2` as 第2季度金额,`3` as 第3季度金额,`4` as 第4季度金额 from [查询练习$] where 部_门
复制代码
题2:
  1. select 部_门 as 部门,`员工#` as 员工,`1` as 第1季度金额,`2` as 第2季度金额,`3` as 第3季度金额,`4` as 第4季度金额 from [查询练习$] where 部_门='A'
复制代码
题3:
  1. select 部_门 as 部门,sum(`1`) as 第1季度金额 from [查询练习$] where 部_门 group by 部_门
复制代码
题4:
  1. select 部_门 as 部门,`员工#` as 员工,`1` as 第1季度金额,`2` as 第2季度金额,`3` as 第3季度金额,`4` as 第4季度金额 from [查询练习$] where `3`>700000
复制代码
题5:
  1. select 部_门 as 部门,max(`4`) as 第4季度金额 from [查询练习$] where 部_门 group by 部_门
复制代码
题6:
  1. select 部_门 as 部门,`员工#` as 员工,`1` as 第1季度金额,`2` as 第2季度金额,`3` as 第3季度金额,`4` as 第4季度金额 from [查询练习$] where 部_门 order by `1`+`2`+`3`+`4` desc
复制代码

点评

OK,非常好。若有时间,再试试以第二行作为标题行的写法。  发表于 2013-4-22 09:57

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-4-22 10:09 | 显示全部楼层
本帖最后由 mps777 于 2013-4-22 10:17 编辑

香版,这样吗?
以第二行作为标题的……
题1:
  1. select f1 as 部门,f2 as 员工,`金 额` as 第1季度金额,`金 额1` as 第2季度金额,`金 额2` as 第3季度金额,`金 额3` as 第4季度金额 from [查询练习$a2:f]
复制代码
题2:
  1. select f1 as 部门,f2 as 员工,`金 额` as 第1季度金额,`金 额1` as 第2季度金额,`金 额2` as 第3季度金额,`金 额3` as 第4季度金额 from [查询练习$a2:f] where f1='A'
复制代码
题3:
  1. select f1 as 部门,sum(`金 额`) as 第1季度金额 from [查询练习$a2:f] group by f1
复制代码
题4:
  1. select f1 as 部门,f2 as 员工,`金 额` as 第1季度金额,`金 额1` as 第2季度金额,`金 额2` as 第3季度金额,`金 额3` as 第4季度金额 from [查询练习$a2:f] where `金 额2`>700000
复制代码
题5:
  1. select f1 as 部门,max(`金 额3`) as 第4季度金额 from [查询练习$a2:f] group by f1
复制代码
题6:
  1. select f1 as 部门,f2 as 员工,`金 额` as 第1季度金额,`金 额1` as 第2季度金额,`金 额2` as 第3季度金额,`金 额3` as 第4季度金额 from [查询练习$a2:f] order by `金 额`+`金 额1`+`金 额2`+`金 额3` desc
复制代码

点评

两个字:完美  发表于 2013-4-22 10:35

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-4-22 16:18 | 显示全部楼层
mps777 发表于 2013-4-22 10:09
香版,这样吗?
以第二行作为标题的……
题1:

三处不明白,请教一下。
1,为什么用f1, f2指定单位格位置,或者说字段名。我套用这个语句,改a1,a2或者 b1,b2都不行。
2、看起来,金 额1,金 额2,金 额3就是同名的情况下,从第二个重复者开始默认赋予一个编号来定位。是这样吗?
3、在保留表头的做法里,部门和员工为什么是那样的书写格式。“部_门”,`员工#`?
谢谢!

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-4-22 16:53 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
moon_2000 发表于 2013-4-21 20:06
接触透视表有4年多了,用SQL有两年多。都是用的时候遇到不会的就赶紧查帮助,搜贴子,来论坛。这期间得到很 ...

F1等标题的来历及SQL对非法标题的处理,请参考:
http://club.excelhome.net/thread-565133-1-1.html

WHERE 字段的含义请参考:
http://club.excelhome.net/thread-833489-1-1.html

GROUP BY是分组依据,不是条件。

题6你的确是钻牛角尖了。{:soso_e120:}

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-4-22 16:59 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
moon_2000 发表于 2013-4-22 16:18
三处不明白,请教一下。
1,为什么用f1, f2指定单位格位置,或者说字段名。我套用这个语句,改a1,a2或者 ...

问题1:字段名称为非法名称时,会用Fx的形式对字段重命名,其中x为要检索表中的列位置。
问题2:SQL不能存在两个相同的字段名称,对相同名称会自动重命名
问题3:字段标题使用强制换行符市,SQL会以下划线"_"表示强制换行的位置,使标题合法,同理,实心原点“.”则用#代替。尽管我们不建议使用乱七八糟的标题,但是我们需要掌握如何处理这些情况。处理方法:http://club.excelhome.net/thread-565133-1-1.html
学会这种方法,以后再遇到乱七八糟的字段标题时,就用上面的方法让SQL告诉我们如何去表示。

评分

2

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 06:11 , Processed in 0.075020 second(s), 18 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表