ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[原创] SQL&Excel结合经典剖析:47期基础题呀,几多疑惑,几多玩味。我们来细细品一品

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2009-5-28 06:24 | 显示全部楼层 |阅读模式
先来个英文版的数据源和SQL语句
一、从SQL语句严格的替换法分析,见sheet2中的效果,也就是SQL语句处理出来的结果还是保留原来的字段,本帖主要分析这个效果语句

1、1 生成唯一的方法
1、1、1DISTINCT 默认升序
           SELECT DISTINCT Location FROM [Sheet1$]
1、1、2、group by 默认升序
        SELECT  Location FROM [Sheet1$] group by Location
1、2 生成序号的方法
1、2、1、用对1求和的方法SUM(1)
    Select   *,
   (Select   SUM(1)                                    
    from&nbsp;&nbsp;&nbsp;Table where&nbsp;&nbsp;&nbsp;keyField&nbsp;&nbsp;&nbsp;<=&nbsp;&nbsp;&nbsp;a.keyField)&nbsp;&nbsp;&nbsp;as&nbsp;&nbsp;&nbsp;ID
&nbsp;&nbsp; &nbsp;from&nbsp;&nbsp;&nbsp;Table&nbsp;&nbsp;&nbsp;a&nbsp;&nbsp;&nbsp;
    order&nbsp;&nbsp;&nbsp;by&nbsp;&nbsp;&nbsp;keyField
1、2、2、用计数的方法count(*)
     Select&nbsp;&nbsp;&nbsp;*,
   (Select&nbsp;&nbsp;&nbsp; count(*) &nbsp;&nbsp;                                 
    from&nbsp;&nbsp;&nbsp;Table where&nbsp;&nbsp;&nbsp;keyField&nbsp;&nbsp;&nbsp;<=&nbsp;&nbsp;&nbsp;a.keyField)&nbsp;&nbsp;&nbsp;as&nbsp;&nbsp;&nbsp;ID
&nbsp;&nbsp; &nbsp;from&nbsp;&nbsp;&nbsp;Table&nbsp;&nbsp;&nbsp;a&nbsp;&nbsp;&nbsp;
    order&nbsp;&nbsp;&nbsp;by&nbsp;&nbsp;&nbsp;keyField

注: keyField-关键字段
    Table-表名
    整个句子的结构用了一层嵌套,括号里的为一个查询结果
    from&nbsp;&nbsp;&nbsp;Table&nbsp;&nbsp;&nbsp;a&nbsp;将表取名为a,是为了条件判断时区分哪个表中的字段
    查询结果为表中字段值满足小于或等于表a中的字段值
    在最外层的查询中记录是一条一条查询的,所有内层查询结果就是对外层表a的逐个字段值进行的统计结果,这样就形成了序列,如果该表中没重复值,结果为        字符升序的序列,如果有重复,重复字段取最大值。例如第二第三相同,序号就        出现两个3,1、3、3….
       as&nbsp;&nbsp;&nbsp;ID :查询结果加上一个字段名“ID” 这里用来方便后面的调用。
1、3 求个数
1、3、1、 SUM(1) 方法:
    Select   SUM(1)   from Table
1、3、2、 count(*)方法:
    Select   count(*) from Table
注:这里为求最大序号,等于求不重复值个数。为后面自动处理数据透视表中显示从小到大做准备的
1、4 唯一值的提取及序列的组合 (两个唯一两个求和可以组合4种方法)
1、4、1 方法1、DISTINCT+SUM
Select   (Select   SUM(1)   from  
(select DISTINCT Location
FROM [Sheet1$]) where   location   <=   a.location) as ID , (Select   SUM(1)   from  
(select DISTINCT Location
FROM [Sheet1$])) as NUM, location
from  
(select DISTINCT Location FROM [Sheet1$])  a
1、4、2方法1、DISTINCT+SUM的解释
我们以一对括号作为一个查询结果,可以用作一个记录集,对它进行字段的处理。也可以用作一个表。
(select DISTINCT Location FROM [Sheet1$])

求唯一的这个查询就是作为一个表来引用
(Select   SUM(1)   from  
(select DISTINCT Location
FROM [Sheet1$]) where   location   <=   a.location) as ID

这个嵌套的查询求出序列,命名为ID
(Select   SUM(1)   from  
(select DISTINCT Location
FROM [Sheet1$])) as NUM

这个嵌套的查询求出最大序号,命名为NUM
后面仍然保留唯一值,是为了后面要调用的
这样我们就有了序号ID、最大序号NUM、和唯一值Location

1、5 、1  方法1、DISTINCT+SUM的字段的组合

select  right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2,location from
(Select   (Select   SUM(1)   from  
(select DISTINCT Location
FROM [Sheet1$]) where   location   <=   a.location) as ID , (Select   SUM(1)   from  
(select DISTINCT Location
FROM [Sheet1$])) as NUM,location
from  
(select DISTINCT Location FROM [Sheet1$])  a)

1、5、2  方法1、DISTINCT+SUM的字段的组合的解释

select  right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2,location from
(……)
1、5、2、1、括号里的就是我们得到序号ID,最大序号num和唯一的Location
1、5、2、2、用了4个IIF函数嵌套,为了获取序号有多少位
     2003版excel的最大行号为65536,所有5位足够了,最大序号小于10为1位,最大序号小于100为2位,最大序号小于1000为3位,最大序号小于10000为4位,否则为5位。
1、5、2、3、我们在ID前面加5个空格符,通过判断num的大小截取右边几位,这样在用”-”来和Location来组合在一起。
1、5、2、4、我们把组合结果命名一个新字段Location2
1、5、2、5、后面要保留唯一Location的字段,后面要用来作为条件替换的。

1、6、1  方法1、DISTINCT+SUM 实现Location2替换Location的功能
select  b.location2, Month, Product, Quantity, Price, Sale
FROM [Sheet1$] ,
(select  right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2,location from
(Select   (Select   SUM(1)   from  
(select DISTINCT Location
FROM [Sheet1$]) where   location   <=   a.location) as ID , (Select   SUM(1)   from  
(select DISTINCT Location
FROM [Sheet1$])) as NUM,location
from  
(select DISTINCT Location FROM [Sheet1$])  a)) b
where `Sheet1$`.Location=b.location

1、6、2  方法1、DISTINCT+SUM 实现Location2替换Location的功能语句的解释

select  b.location2, Month, Product, Quantity, Price, Sale
FROM [Sheet1$] ,
(……) b
where `Sheet1$`.Location=b.location
1、6、2、1、我们可以简化刚才的语句
1、6、2、2、括号中是刚才解释过的语句,一个得到序号组合Location的结果
           Location2以及Location,我们将结果命名为B表
1、6、2、3、FROM [Sheet1$] ,(……) b
          整个语句是从两个并列的两个表中查询,
1、6、2、4、where `Sheet1$`.Location=b.location
          条件是原始表中的Location和查询结果表B中的Location相同
1、6、2、5、这样就顺利地将查询表B中Location2取代了原来的Location

1、7、1  4种SQL语句 两个唯一两个求和可以组合4种方法
1、7、1、1  方法1: DISTINCT+SUM(上面已分析)
1、7、1、2  方法2: DISTINCT+COUNT
select  b.location2, Month, Product, Quantity, Price, Sale
FROM [Sheet1$] ,
(select  right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2,location from
(Select   (Select   count(*)   from  
(select DISTINCT Location
FROM [Sheet1$]) where   location   <=   a.location) as ID , (Select   count(*)   from  
(select DISTINCT Location
FROM [Sheet1$])) as NUM,location
from  
(select DISTINCT Location FROM [Sheet1$])  a)
) b
where `Sheet1$`.Location=b.location

1、7、1、3 方法3: Group by+SUM

select  b.location2, Month, Product, Quantity, Price, Sale
FROM [Sheet1$] ,
(select  right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2,location from
(Select   (Select   SUM(1)  from  
(SELECT  Location
FROM [Sheet1$]
group by Location) where   location   <=   a.location) as ID , (Select   SUM(1)   from  
(SELECT  Location
FROM [Sheet1$]
group by Location)) as NUM,location
from  
(SELECT  Location
FROM [Sheet1$]
group by Location)  a) ) b
where `Sheet1$`.Location=b.location
order by location2

1、7、1、4  方法4: Group by+COUNT

select  b.location2, Month, Product, Quantity, Price, Sale
FROM [Sheet1$] ,
(select  right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2,location from
(Select   (Select   count(*)   from  
(SELECT  Location
FROM [Sheet1$]
group by Location) where   location   <=   a.location) as ID , (Select   count(*)   from  
(SELECT  Location
FROM [Sheet1$]
group by Location)) as NUM,location
from  
(SELECT  Location
FROM [Sheet1$]
group by Location)  a)) b
where `Sheet1$`.Location=b.location
order by location2

后3中方法组合原来同方法1本人不再累述。



二、考虑到数据导入到数据透视表的应用情况,我们忽略SQL语句中产生的字段,所以SQL语句可以简化
2、1 方法1
select * ,(select sum(1) from (select distinct Location FROM [sheet1$]) where Location<=a.Location) as ID,(select sum(1) from (select distinct Location FROM [sheet1$])) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2 from [sheet1$] a

2、2 方法2
select * ,(select count(*) from (select distinct Location FROM [sheet1$]) where Location<=a.Location) as ID,(select count(*) from (select distinct Location FROM [sheet1$])) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2 from [sheet1$] a

2、3 方法3
select * ,(select sum(1) from (SELECT  Location
FROM [Sheet1$] group by Location) where Location<=a.Location) as ID,(select sum(1) from (SELECT  Location
FROM [Sheet1$] group by Location)) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2 from [sheet1$] a

2、4 方法4
select * ,(select count(*) from (SELECT  Location
FROM [Sheet1$] group by Location) where Location<=a.Location) as ID,(select count(*) from (SELECT  Location
FROM [Sheet1$] group by Location)) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  LOCATION as location2 from [sheet1$] a

[ 本帖最后由 BIN_YANG168 于 2009-5-28 13:03 编辑 ]

47-sacrlett--88.rar

11.67 KB, 下载次数: 3239

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-5-28 06:25 | 显示全部楼层
现在来谈谈中文数据源的情况
源和结果不同工作簿的情况,并且两个工作簿不同时打开的效果。
中文版的简化4个SQL语句

2、1 方法1
select * ,(select sum(1) from (select distinct 地区 FROM [sheet1$]) where 地区<=a.地区) as ID,(select sum(1) from (select distinct 地区 FROM [sheet1$])) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  地区 as 地区2 from [sheet1$] a

2、2 方法2
select * ,(select count(*) from (select distinct 地区 FROM [sheet1$]) where 地区<=a.地区) as ID,(select count(*) from (select distinct 地区 FROM [sheet1$])) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  地区 as 地区2 from [sheet1$] a

2、3 方法3
select * ,(select sum(1) from (SELECT  地区
FROM [Sheet1$] group by 地区) where 地区<=a.地区) as ID,(select sum(1) from (SELECT  地区
FROM [Sheet1$] group by 地区)) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  地区 as 地区2 from [sheet1$] a

2、4 方法4
select * ,(select count(*) from (SELECT  地区
FROM [Sheet1$] group by 地区) where 地区<=a.地区) as ID,(select count(*) from (SELECT  地区
FROM [Sheet1$] group by 地区)) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  地区 as 地区2 from [sheet1$] a

这种方式处理出来的结果是按拼音排序的,且页字段默认就能很正常显示排序效果。
迷惑之一:源和结果要分开,并不能同时打开。SQL默认的方式是按拼音排序的。是bug吗?有办法解决吗 ?
如果同时打开,排序就改为按笔画排序了。关掉源后又可以回到拼音方式排序。
两种方式转换前会提出丢失数据之类,往下点,进行第二次刷新就会有结果。

[ 本帖最后由 Scarlett_88 于 2009-5-28 09:54 编辑 ]

结果表-源和结果分开并不同时打开结果.rar

8.76 KB, 下载次数: 1613

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-5-28 06:26 | 显示全部楼层
源和结果同一工作簿的情况
源和结果在同一工作簿的情况和两个文件同时打开出来的效果一样,排序变成了案笔画排序
SQL语句同上,但同时还会出现紊乱的情况,刷新数据,排序会出现乱排现象
解决这个方法有二:

一、页字段必须重新设置
右击也字段-字段设置-高级-自动排序选项-勾升序

二、强制用SQL语句排序
2、1 方法1
select * ,(select sum(1) from (select distinct 地区 FROM [sheet1$]) where 地区<=a.地区) as ID,(select sum(1) from (select distinct 地区 FROM [sheet1$])) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  地区 as 地区2 from [sheet1$] a order by 地区

2、2 方法2
select * ,(select count(*) from (select distinct 地区 FROM [sheet1$]) where 地区<=a.地区) as ID,(select count(*) from (select distinct 地区 FROM [sheet1$])) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  地区 as 地区2 from [sheet1$] a order by 地区

2、3 方法3
select * ,(select sum(1) from (SELECT  地区
FROM [Sheet1$] group by 地区) where 地区<=a.地区) as ID,(select sum(1) from (SELECT  地区
FROM [Sheet1$] group by 地区)) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  地区 as 地区2 from [sheet1$] a order by 地区

2、4 方法4
select * ,(select count(*) from (SELECT  地区
FROM [Sheet1$] group by 地区) where 地区<=a.地区) as ID,(select count(*) from (SELECT  地区
FROM [Sheet1$] group by 地区)) as num, right('     '& ID,IIF(num<10, 1, IIF(num<100, 2, IIF(num<1000, 3, IIF(num<10000, 4, 5))))) & '-' &  地区 as 地区2 from [sheet1$] a order by 地区
迷惑之二: 为什么在源和结果同时打开排序方法会变成按笔画排序?

[ 本帖最后由 Scarlett_88 于 2009-5-28 10:54 编辑 ]

47-sacrlett_88.rar

6.93 KB, 下载次数: 1137

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-5-28 06:35 | 显示全部楼层
序列组合不加空格的处理方法
3、1 方法1
select * ,(select sum(1) from (select distinct 地区 FROM [sheet1$]) where 地区<=a.地区)  & '-' &  地区 as 地区2 from [sheet1$] a
order by 地区


3、2 方法2
select * ,(select count(*) from (select distinct 地区 FROM [sheet1$]) where 地区<=a.地区) & '-' &  地区 as 地区2 from [sheet1$] a order by 地区

3、3 方法3
select * ,(select sum(1) from (SELECT  地区
FROM [Sheet1$] group by 地区) where 地区<=a.地区) & '-' &  地区 as 地区2 from [sheet1$] a order by 地区

3、4 方法4
select * ,(select count(*) from (SELECT  地区
FROM [Sheet1$] group by 地区) where 地区<=a.地区) & '-' &  地区 as 地区2 from [sheet1$] a order by 地区

用页字段10多个的数据记录来生产一个数据透视表,将页字段拖到行字段,将10以上的拖到9下面,建议一次选中拖下来,在行字段中手动排序好后,再将该字段拖回页字段,我添加了1万多个页字段记录,刷新结果很正常。
因为文件太大,上传一个页字段数1000多的文件。

[ 本帖最后由 Scarlett_88 于 2009-5-28 09:09 编辑 ]

Scarlett_88-不加空格处理的结果.rar

36.95 KB, 下载次数: 1299

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2009-5-28 06:51 | 显示全部楼层
本帖最后由 ExcelHome 于 2012-10-6 16:23 编辑

嗯。占个空位。好好学习。分析的这么透。建议置顶,经常回来看看。

TA的精华主题

TA的得分主题

发表于 2009-5-28 06:54 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-5-28 06:56 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-5-28 07:16 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-5-28 07:19 | 显示全部楼层
斯嘉丽,辛苦了。
建议加精华

TA的精华主题

TA的得分主题

发表于 2009-5-28 09:15 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-28 22:30 , Processed in 0.053447 second(s), 11 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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