去妄存真——学习SQL的三把破妄之剑

wuxiang_123 · 发表于 2010-4-23 09:22

用肉眼观察,1组和2组这两个工作表的格式是一样的,列数也一样,但是,实际的情况是什么呢?
我们先用SQL语句(参考动画:出错提示.GIF):
select * from [`1组`]
UNION ALL
select * from [`2组`]
发现,SQL提示"列数不匹配".列数不匹配,说明两表的字段数不同.这是什么原因呢,肉眼上看,两表的列数(即字段数)是一致的啊.下面我们用三把宝剑来分析一下数据.

第一把宝剑:文本文档或记事本(参考动画:记事本OR文本文档.GIF):
我们用文本文档查看一下.分别点击两个工作表的第一行-选择第一行记录-复制-粘贴到文本文档中;
选中字段之间的空格,执行-查找替换-替换为",";然后,文本就出现:
员工,"
职位",小组., 目标产量 ,1月1日,1月2日,1月剩余量,,,,,,,,,,
员工,"
职位",小组., 目标产量 ,1月1日,1月2日,1月剩余量
从上面我们观测到,[职位]变成["
职位"],而[目标产量]变成[ 目标产量 ],而且,1组后面还多出一排",".
呵呵,这个就是文本文档观测到的真实面貌.
[职位]变成["
职位"]:是由于我在数据源中,在[职位]插入了强制换行符.于是,文本文档就告诉我们,[职位]存在强制换行符.至于"
"就是强制换行号在名称中的位置.
[目标产量]变成[ 目标产量 ]:是由于数据源中的真实名称是[ 目标产量 ],而将数据居中处理,这样肉眼看起来是没有前后空格的.
1组后面还多出一排",":这个是因为1组后面存在多个假空列.

找到原因了,我们就可以为上面的连接找到解决方法:
方法1:删除假空列,并保存工作簿,再使用上面的SQL语句
方法2:精确区域,排除假空列.将上面的SQL语句更改为:
select * from [1组$A:G]
UNION ALL
select * from [2组$A:G]

小结:利用文本文档或记事本,可以为我们快速提取字段名称,和识别假空列和部分特殊字符.

第二把宝剑:利用外部数据源直接生成表(参考动画:外部数据源.GIF和留意出错提示和修改过程.GIF).(这个我最常用,强烈推荐)
选中任意一单元格-执行数据-导入外部数据-导入数据-选择你要查看的表-新建工作表-确定.
这是,我们看到利用这种方法创建的表,与文本文档的不一样.
员工,_职位,小组#, 目标产量 ,F5,F6,1月剩余量,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17
员工,_职位,小组#, 目标产量 ,F5,F6,1月剩余量
其中,一组的[职位]变成_职位,[目标产量]肉眼上看并没有变化,但利用文本文档的方法提取字段名称时,我们发现,依然是有前后空格的.[小组.]中的"."被"#"代替,日期则用F5、F6代替.而假空列也是用Fx的形式代替.
呵呵,这个就是SQL观测到真实面貌.
[职位]变成_职位:这是由于在SQL中,"_"表示强制换行符号.
[小组.]变成小组#:个人认为,这是由于"."在SQL中常用于表示字段和表之间的关系符号(不知道这样表述是否正确).为避免混淆,所以以"#"代替;也可以理解成在SQL中,字段尾是"."时,以"#"代替.
日期用Fx代替:在SQL中,字段名称是日期型,可以用Fx的形式,其中X为该字段在表中的位置.
假空列同样是用Fx代替.

不过,假如我们想不用*,而是想提取具体字段时发现SQL又出错了.
在解释前,先说明一下,字段的表示SQL可以使用两种格式.一种是"[]",如SELECT [员工] FROM [`1组`];一种是"``",如SELECT `员工` FROM [`1组`],"[]、``"一般可以忽略。
但当字段含空格或特殊意思值或以数字为开头等时,"[]、``"不能省略.

找到原因后,我们又可以用另外一种解法:
select [员工],[_职位],[小组#],[ 目标产量 ],F5,F6,[1月剩余量] from ['1组$']
UNION ALL
select [员工],[_职位],[小组#],[ 目标产量 ],F5,F6,[1月剩余量] from ['2组$']

小结,利用外部数据源法,可以直接获得字段在SQL的正确写法,当遇到含特殊字符的字段名称,可以用此法获得正确的表述.结合文本文档,更是如虎添翼.

第三把宝剑:利用Microsoft Query(简称MQ)(参考动画:MQ.GIF)
这种方法跟第二种的原理差不多,不再罗嗦.