请帮忙简化以下SQL语句，谢谢！

fenghcl · 发表于 2010-4-17 21:41

SELECT * FROM (
SELECT "EO" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[EO$] UNION ALL
SELECT "IF" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[IF$] UNION ALL
SELECT "KG" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[HK_KG$] UNION ALL
SELECT "KG" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[USA_KG$] UNION ALL
SELECT "JP" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[JP$] UNION ALL
SELECT "SL" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[SL$] UNION ALL
SELECT "TM" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[TM$] UNION ALL
SELECT "WW" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[WW$] UNION ALL
SELECT "TY" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[TY$] UNION ALL
SELECT "WO" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[WO$] UNION ALL
SELECT "KA" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[KA$] UNION ALL
SELECT "TI" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[TI$] UNION ALL
SELECT "NK" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[NK$] UNION ALL
SELECT "MT" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[MT$] UNION ALL
SELECT "WD" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[WD$])
WHERE WEEKLY LIKE 'WK__'

上面提到的字段是每个表中都存在的，你们看，所有的SHEET都在同一个工作簿中，能否先给文件名及路径赋值，然后再引用其赋值？
原先我是先每一句SELECT FROM语句后都有加一个WHERE WEEKLY LIKE ‘WK__',后来想来想去才发现可以用SELECT * FROM 来引用整个句子,
再给他一个条件,这样语句就省了很多,因为在OE LDB语句不能超过2024个字符?连空格?这是我反复试验的.

另外一个问题就是，上面表中只是引用了15个工作表，不能再多一个了，如果达到16个的话，就会提示超出系统资源。如何达到60个以上的引用?

谢谢!

[ 本帖最后由 fenghcl 于 2010-4-17 22:20 编辑 ]

livewire · 发表于 2010-4-17 23:00

试着简化一下，但没有条件验证

SELECT * FROM (
SELECT 'EO' AS 客户,ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[EO$] UNION ALL
SELECT 'IF',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[IF$] UNION ALL
SELECT 'KG',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[HK_KG$] UNION ALL
SELECT 'KG',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[USA_KG$] UNION ALL
SELECT 'JP',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[JP$] UNION ALL
SELECT 'SL',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[SL$] UNION ALL
SELECT 'TM',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[TM$] UNION ALL
SELECT 'WW',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[WW$] UNION ALL
SELECT 'TY',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[TY$] UNION ALL
SELECT 'WO',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[WO$] UNION ALL
SELECT 'KA',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[KA$] UNION ALL
SELECT 'TI',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[TI$] UNION ALL
SELECT 'NK',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[NK$] UNION ALL
SELECT 'MT',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[MT$] UNION ALL
SELECT 'WD',ITEM,PRICE,QTY,WEEKLY FROM `I:\Weekly Loading Report-WK02`.[WD$])
WHERE WEEKLY LIKE 'WK__'

复制代码

如果各数据源表中只有ITEM,PRICE,QTY,WEEKLY这四列，则为

SELECT * FROM (
SELECT 'EO' AS 客户,* FROM `I:\Weekly Loading Report-WK02`.[EO$] UNION ALL
SELECT 'IF',* FROM `I:\Weekly Loading Report-WK02`.[IF$] UNION ALL
SELECT 'KG',* FROM `I:\Weekly Loading Report-WK02`.[HK_KG$] UNION ALL
SELECT 'KG',* FROM `I:\Weekly Loading Report-WK02`.[USA_KG$] UNION ALL
SELECT 'JP',* FROM `I:\Weekly Loading Report-WK02`.[JP$] UNION ALL
SELECT 'SL',* FROM `I:\Weekly Loading Report-WK02`.[SL$] UNION ALL
SELECT 'TM',* FROM `I:\Weekly Loading Report-WK02`.[TM$] UNION ALL
SELECT 'WW',* FROM `I:\Weekly Loading Report-WK02`.[WW$] UNION ALL
SELECT 'TY',* FROM `I:\Weekly Loading Report-WK02`.[TY$] UNION ALL
SELECT 'WO',* FROM `I:\Weekly Loading Report-WK02`.[WO$] UNION ALL
SELECT 'KA',* FROM `I:\Weekly Loading Report-WK02`.[KA$] UNION ALL
SELECT 'TI',* FROM `I:\Weekly Loading Report-WK02`.[TI$] UNION ALL
SELECT 'NK',* FROM `I:\Weekly Loading Report-WK02`.[NK$] UNION ALL
SELECT 'MT',* FROM `I:\Weekly Loading Report-WK02`.[MT$] UNION ALL
SELECT 'WD',* FROM `I:\Weekly Loading Report-WK02`.[WD$])
WHERE WEEKLY LIKE 'WK__'

复制代码

如果就在本工作簿中建立此查询
那么 `I:\Weekly Loading Report-WK02`. 全部替换为空

[ 本帖最后由 livewire 于 2010-4-18 10:35 编辑 ]

livewire · 发表于 2010-4-17 23:15

关于如何达到60个以上的引用? 我看ACCESS 倒是挺适合！

fenghcl · 发表于 2010-4-18 10:23

多谢2，3楼监察大人的回复。
是我没有讲清楚，这是在另一个文件建立的真正意义上的引用，源文件中有30个SHEET，每个SHEET都有着相同的结构，
现在只是抽取部分字段出来做报表，因为在厂里工作，部门一些老大是看要看每个客户的分表，这样他们看起来会比较直观，
而每周又要汇总一次这个星期的所有客户总出货是多少，就必须做一个透视分析。下一步还要根据这个分析做其他条件的分析。
如果OE LDB中的语句总长度不能超过2024，接下来的表是没办法再添加上去的，
首先文件名及路径是上面指定放在一个服务器上的某个目录下的某个目录的某个目录，长着呢。
对于笨的人，只能想些聪明的方法去应对的吧？

[ 本帖最后由 fenghcl 于 2010-4-18 10:32 编辑 ]

livewire · 发表于 2010-4-18 10:38

明白了！只能简化成这样子

SELECT 客户,ITEM,PRICE,QTY,WEEKLY FROM (
SELECT 'EO' AS 客户,* FROM `I:\Weekly Loading Report-WK02`.[EO$] UNION ALL
SELECT 'IF',* FROM `I:\Weekly Loading Report-WK02`.[IF$] UNION ALL
SELECT 'KG',* FROM `I:\Weekly Loading Report-WK02`.[HK_KG$] UNION ALL
SELECT 'KG',* FROM `I:\Weekly Loading Report-WK02`.[USA_KG$] UNION ALL
SELECT 'JP',* FROM `I:\Weekly Loading Report-WK02`.[JP$] UNION ALL
SELECT 'SL',* FROM `I:\Weekly Loading Report-WK02`.[SL$] UNION ALL
SELECT 'TM',* FROM `I:\Weekly Loading Report-WK02`.[TM$] UNION ALL
SELECT 'WW',* FROM `I:\Weekly Loading Report-WK02`.[WW$] UNION ALL
SELECT 'TY',* FROM `I:\Weekly Loading Report-WK02`.[TY$] UNION ALL
SELECT 'WO',* FROM `I:\Weekly Loading Report-WK02`.[WO$] UNION ALL
SELECT 'KA',* FROM `I:\Weekly Loading Report-WK02`.[KA$] UNION ALL
SELECT 'TI',* FROM `I:\Weekly Loading Report-WK02`.[TI$] UNION ALL
SELECT 'NK',* FROM `I:\Weekly Loading Report-WK02`.[NK$] UNION ALL
SELECT 'MT',* FROM `I:\Weekly Loading Report-WK02`.[MT$] UNION ALL
SELECT 'WD',* FROM `I:\Weekly Loading Report-WK02`.[WD$])
WHERE WEEKLY LIKE 'WK__'

复制代码

fenghcl · 发表于 2010-4-18 12:18

去除小小的纰漏后（第三行开始，每个新增字段后要加一个“AS 客户“），发现提示“在联合查询中所选定的两个数据表或查询的列数不匹配”，
但很奇怪，我的每个SHEET都是统一由第一个SHEET中复制过去再删除除表头之外的其他内容，怎么会提示列数不匹配呢，而且，我已经再一次用CTRL+END来看表的结尾，都是到我最后一行，最后一行数据处，应该说不用有这种现象。
提示列数不匹配：
SELECT 客户,ITEM,PRICE,QTY,WEEKLY FROM (
SELECT 'EO' AS 客户, * from [EO$] UNION ALL
SELECT 'IF' AS 客户, * from [IFi$] UNION ALL
SELECT 'KG' AS 客户, * from [HK_KG$] UNION ALL
SELECT 'KG' AS 客户, * from [USA_KG$] UNION ALL
SELECT 'JP' AS 客户, * from [JP$] UNION ALL
SELECT 'SL' AS 客户, * from [SL$] UNION ALL
SELECT 'TM' AS 客户, * from [TM$] UNION ALL
SELECT 'WW' AS 客户, * from [WW$] UNION ALL
SELECT 'TY' AS 客户, * from [TY$] UNION ALL
SELECT 'WO' AS 客户, * from [WO$] UNION ALL
SELECT 'KA' AS 客户, * from [KA$] UNION ALL
SELECT 'TI' AS 客户, * from [TI$] UNION ALL
SELECT 'NK' AS 客户, * from [NK$] UNION ALL
SELECT 'MT' AS 客户, * from [MT$] UNION ALL
SELECT 'WD' AS 客户, * from [WD$])
WHERE WEEKLY LIKE 'WK__'

另外，因为所有的数据源都在同一个工作簿中，而且已经在连接中指定了源，能否在后面的语句中不用再反映呢？试试看后，是行的，但新问题又来了，QTY求和时都是0，原来它将QTY数据当成了文本形式。也许是电脑的原因吧。

最后的问题还是没有解决，再多加一个SHEET上去的话，就不行了，提示超出系统资源：（已经删除最后一行的SHEET，可以用，但QTY求和变文本了，求出来是0。）
select * from (SELECT "EO" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [EO$] UNION ALL
SELECT "IFI" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [IFI$] UNION ALL
SELECT "KG" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [HK_KG$] UNION ALL
SELECT "KG" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [USA_KG$] UNION ALL
SELECT "JP" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [JP$] UNION ALL
SELECT "SL" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [SL$] UNION ALL
SELECT "TM" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [TM$] UNION ALL
SELECT "WW" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [WW$] UNION ALL
SELECT "TY" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [TY$] UNION ALL
SELECT "WO" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [WO$] UNION ALL
SELECT "KA" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [KA$] UNION ALL
SELECT "TI" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [TI$] UNION ALL
SELECT "NK" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [NK$] UNION ALL
SELECT "MT" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [MT$] UNION ALL
SELECT "WD" AS 客户,ITEM,PRICE,QTY,WEEKLY FROM [WD$]) WHERE WEEKLY LIKE 'WK__‘

[ 本帖最后由 fenghcl 于 2010-4-18 15:58 编辑 ]

livewire · 发表于 2010-4-18 12:44

呵呵！俺只是提供一个思路，未经验证。
数据源文件只在楼主手上，钦佩楼主的钻研精神！

至于超出系统资源， 3楼已回复。退而求其次，看看2007能否胜任？

另外，透视表可以再透视，同样地，查询可以再查询。

wuxiang_123 · 发表于 2010-4-18 13:18

没有附件，只能从SQL中猜测原因：
一：用*时，发现查询列数不匹配的可能原因，说明连接的表的字段数不一致。
可能部分表存在假空的列或有多余的列。检查方法可以用记事本、OLE DO 直接生成表和MQ。假如存在F1等含Fx的字段名称，则数据源存在假空列。假如有其他名称，则存在多余的字段。这时候我们只需要将表精确定位即可。如：指明列。[IFI$A：H]
二：字段求和为0。这个与SQL查询有关。假如表的字段下第一条记录为NULL值，SQL往往容易将此字段定义为文本属性，自动将非文本处理成NULL值。解决方法：将一条记录输入0，或用四则运算符。如QTY字段可以用QTY+0代替。
PS：假如可以的话，楼主最好上传附件。名称可以更改，数值也可以更改，只要格式保持不变就可以分析出错原因。

fenghcl · 发表于 2010-4-18 15:52

你意思是说,用ACCESS将EXCEL几十个SHEET导入,然后再在ACCESS中透视,最后再导出到EXCEL?
由于文件是另一个部门的同事的,我只是对新问题感兴趣,也想利用这种机会来了解并学习,要是这样的话,ACCESS在办公室里的人不会有几个懂的,我这个三脚猫功夫,别的同事都对我另眼相看了,可见他们的水平.
我取得了那同事的同意,现上传文件给大伙把把脉,看问题在哪?谢谢!

fenghcl · 发表于 2010-4-18 15:55

你的建议很好.特别是第二点,关于求和数出现0的情况,我再研究研究.
附件我上传了,有时间的话,帮我分析分析.谢谢!

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

[讨论] 请帮忙简化以下SQL语句，谢谢！

回复 5楼 livewire 的帖子

回复 6楼 fenghcl 的帖子

回复 7楼 livewire 的帖子

回复 8楼 wuxiang_123 的帖子