SQL&Excel结合经典剖析：47期基础题呀，几多疑惑，几多玩味。我们来细细品一品

Scarlett_88 · 发表于 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   Table where   keyField   <=   a.keyField)   as   ID
    from   Table   a   
order   by   keyField
1、2、2、用计数的方法count(*)
   Select   *,
(Select    count(*)   
from   Table where   keyField   <=   a.keyField)   as   ID
    from   Table   a   
order   by   keyField

注： keyField-关键字段
Table-表名
整个句子的结构用了一层嵌套，括号里的为一个查询结果
from   Table   a 将表取名为a，是为了条件判断时区分哪个表中的字段
查询结果为表中字段值满足小于或等于表a中的字段值
在最外层的查询中记录是一条一条查询的，所有内层查询结果就是对外层表a的逐个字段值进行的统计结果，这样就形成了序列，如果该表中没重复值，结果为字符升序的序列，如果有重复，重复字段取最大值。例如第二第三相同，序号就出现两个3，1、3、3….
   as   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 编辑 ]

Scarlett_88 · 发表于 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 编辑 ]

Scarlett_88 · 发表于 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 编辑 ]

Scarlett_88 · 发表于 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 编辑 ]

monvzhilei · 发表于 2009-5-28 06:51

本帖最后由 ExcelHome 于 2012-10-6 16:23 编辑

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

开心萝卜惠州 · 发表于 2009-5-28 06:54

呵呵，感谢，学习了，期待下部

abcd321abcd · 发表于 2009-5-28 06:56

期待继续，慢慢学习。

三戒 · 发表于 2009-5-28 07:16

谢谢分享，好好看看

linpansheng · 发表于 2009-5-28 07:19

斯嘉丽，辛苦了。
建议加精华

sjds333 · 发表于 2009-5-28 09:15

感谢，继续期待，

		自动登录	找回密码
密码			免费注册

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

评分

评分