多表合并,因字段太多,而且都是必须的,命令太长,超出限制,有办法简化吗?谢谢

shimeng185 · 发表于 2010-5-19 15:42

因字段太多,而且都是必须的,命令太长,超出限制,有办法简化吗?谢谢
select "2004年" as 年,姓名 as 姓名查询,姓名,个人账号,"1月个人" as 1月个人,"1月集体" as 1月集体,"2月个人" as 2月个人,"2月集体" as 2月集体,"3月个人" as 3月个人,"3月集体" as 3月集体,"4月个人" as 4月个人,"4月集体" as 4月集体,"5月个人" as 5月个人,"5月集体" as 5月集体,"6月个人" as 6月个人,"6月集体" as 6月集体,"7月个人" as 7月个人,"7月集体" as 7月集体,"8月个人" as 8月个人,"8月集体" as 8月集体,"9月个人" as 9月个人,"9月集体" as 9月集体,"10月个人" as 10月个人,"10月集体" as 10月集体,"11月个人" as 11月个人,"11月集体" as 11月集体,"12月个人" as 12月个人,"12月集体" as 12月集体 from [2004年$] union all
select "2005年" as 年,姓名 as 姓名查询,姓名,个人账号,"1月个人" as 1月个人,"1月集体" as 1月集体,"2月个人" as 2月个人,"2月集体" as 2月集体,"3月个人" as 3月个人,"3月集体" as 3月集体,"4月个人" as 4月个人,"4月集体" as 4月集体,"5月个人" as 5月个人,"5月集体" as 5月集体,"6月个人" as 6月个人,"6月集体" as 6月集体,"7月个人" as 7月个人,"7月集体" as 7月集体,"8月个人" as 8月个人,"8月集体" as 8月集体,"9月个人" as 9月个人,"9月集体" as 9月集体,"10月个人" as 10月个人,"10月集体" as 10月集体,"11月个人" as 11月个人,"11月集体" as 11月集体,"12月个人" as 12月个人,"12月集体" as 12月集体 from [2005年$] union all
select "2006年" as 年,姓名 as 姓名查询,姓名,个人账号,"1月个人" as 1月个人,"1月集体" as 1月集体,"2月个人" as 2月个人,"2月集体" as 2月集体,"3月个人" as 3月个人,"3月集体" as 3月集体,"4月个人" as 4月个人,"4月集体" as 4月集体,"5月个人" as 5月个人,"5月集体" as 5月集体,"6月个人" as 6月个人,"6月集体" as 6月集体,"7月个人" as 7月个人,"7月集体" as 7月集体,"8月个人" as 8月个人,"8月集体" as 8月集体,"9月个人" as 9月个人,"9月集体" as 9月集体,"10月个人" as 10月个人,"10月集体" as 10月集体,"11月个人" as 11月个人,"11月集体" as 11月集体,"12月个人" as 12月个人,"12月集体" as 12月集体 from [2006年$] union all
select "2007年" as 年,姓名 as 姓名查询,姓名,个人账号,"1月个人" as 1月个人,"1月集体" as 1月集体,"2月个人" as 2月个人,"2月集体" as 2月集体,"3月个人" as 3月个人,"3月集体" as 3月集体,"4月个人" as 4月个人,"4月集体" as 4月集体,"5月个人" as 5月个人,"5月集体" as 5月集体,"6月个人" as 6月个人,"6月集体" as 6月集体,"7月个人" as 7月个人,"7月集体" as 7月集体,"8月个人" as 8月个人,"8月集体" as 8月集体,"9月个人" as 9月个人,"9月集体" as 9月集体,"10月个人" as 10月个人,"10月集体" as 10月集体,"11月个人" as 11月个人,"11月集体" as 11月集体,"12月个人" as 12月个人,"12月集体" as 12月集体 from [2007年$] union all
select "2008年" as 年,姓名 as 姓名查询,姓名,个人账号,"1月个人" as 1月个人,"1月集体" as 1月集体,"2月个人" as 2月个人,"2月集体" as 2月集体,"3月个人" as 3月个人,"3月集体" as 3月集体,"4月个人" as 4月个人,"4月集体" as 4月集体,"5月个人" as 5月个人,"5月集体" as 5月集体,"6月个人" as 6月个人,"6月集体" as 6月集体,"7月个人" as 7月个人,"7月集体" as 7月集体,"8月个人" as 8月个人,"8月集体" as 8月集体,"9月个人" as 9月个人,"9月集体" as 9月集体,"10月个人" as 10月个人,"10月集体" as 10月集体,"11月个人" as 11月个人,"11月集体" as 11月集体,"12月个人" as 12月个人,"12月集体" as 12月集体 from [2008年$] union all
select "2009年" as 年,姓名 as 姓名查询,姓名,个人账号,"1月个人" as 1月个人,"1月集体" as 1月集体,"2月个人" as 2月个人,"2月集体" as 2月集体,"3月个人" as 3月个人,"3月集体" as 3月集体,"4月个人" as 4月个人,"4月集体" as 4月集体,"5月个人" as 5月个人,"5月集体" as 5月集体,"6月个人" as 6月个人,"6月集体" as 6月集体,"7月个人" as 7月个人,"7月集体" as 7月集体,"8月个人" as 8月个人,"8月集体" as 8月集体,"9月个人" as 9月个人,"9月集体" as 9月集体,"10月个人" as 10月个人,"10月集体" as 10月集体,"11月个人" as 11月个人,"11月集体" as 11月集体,"12月个人" as 12月个人,"12月集体" as 12月集体 from [2009年$] union all
select "2010年" as 年,姓名 as 姓名查询,姓名,个人账号,"1月个人" as 1月个人,"1月集体" as 1月集体,"2月个人" as 2月个人,"2月集体" as 2月集体,"3月个人" as 3月个人,"3月集体" as 3月集体,"4月个人" as 4月个人,"4月集体" as 4月集体,"5月个人" as 5月个人,"5月集体" as 5月集体,"6月个人" as 6月个人,"6月集体" as 6月集体,"7月个人" as 7月个人,"7月集体" as 7月集体,"8月个人" as 8月个人,"8月集体" as 8月集体,"9月个人" as 9月个人,"9月集体" as 9月集体,"10月个人" as 10月个人,"10月集体" as 10月集体,"11月个人" as 11月个人,"11月集体" as 11月集体,"12月个人" as 12月个人,"12月集体" as 12月集体 from [2010年$]

whsfhwm · 发表于 2010-5-19 16:13

提供一个简化方法：
union all之后所有字段不用再命名为别名，即取消字段名称后面的“as 1月个人”等
因为查询结果是以第一条SQL语句的字段名为名字的

shimeng185 · 发表于 2010-5-19 22:12

谢谢了，原来写的也不对，提取不了数值，成文本了，如果字段以数字开头提取该字段数值是否应这样写 `1月单位`,`1月集体`,.......

抹香鲸2009 · 发表于 2010-5-20 08:19

如果取数值，应该写成：1月单位（如果文字之间有空格，必须用中括号引起来）；如果想命名为归类字段，则用引号引起来“1月单位”

whsfhwm · 发表于 2010-5-20 08:54

原帖由 抹香鲸2009 于 2010-5-20 08:19 发表
如果取数值，应该写成：1月单位（如果文字之间有空格，必须用中括号引起来）；如果想命名为归类字段，则用引号引起来“1月单位”

向你请教一下：归类字段和普通字段有什么区别啊？具体应用上有什么不同呢？

shimeng185 · 发表于 2010-5-20 15:04

原帖由 抹香鲸2009 于 2010-5-20 08:19 发表
如果取数值，应该写成：1月单位（如果文字之间有空格，必须用中括号引起来）；如果想命名为归类字段，则用引号引起来“1月单位”

我试过了,如果只写成1月单位的话会提示 "语法错误(操作符丢失)在查询表达式'1月单位'中." 还有我理解归类字段和普通字段的区别是,归类字段加引号,我试了试好像单双引号作用一样,显示结果为引号内的内容,如果是普通字段的话,显示结果为原字段下内容,不知道理解对不对?

shimeng185 · 发表于 2010-5-20 15:35

为什么导入外部数据显示结果正确,而使用数据透视表就没内容呢?

wuxiang_123 · 发表于 2010-5-20 15:54

select "2004" AS 年,* from [2004年$]
UNION ALL
select "2005",* from [2005年$]
UNION ALL
select "2006",* from [2006年$]
UNION ALL
select "2007",* from [2007年$]
UNION ALL
select "2008",* from [2008年$]
UNION ALL
select "2009",* from [2009年$]
UNION ALL
select "2010",* from [2010年$]

没有看见"X月集体"的,只有"X月单位".既然全部汇总,就可以用*表示所有字段.

wuxiang_123 · 发表于 2010-5-20 15:58

当字段名称以数字为开头,不能省略标识符.在EXCEL中,可用的标识符号有:[]和``
以本贴为例,要提取1月个人的数据,可用这样写:
select [1月个人] from [2004年$]
或
select `1月个人` from [2004年$]

shimeng185 · 发表于 2010-5-20 16:07

参数不足,期待是1 参数不足的代码有多少种?都代表什么意思呢?我见过1、2和26

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

[求助] 多表合并,因字段太多,而且都是必须的,命令太长,超出限制,有办法简化吗?谢谢

回复 3楼 shimeng185 的帖子