联合查询的SQL语句写出来总说有错,帮一下忙哈!

cofe2000

以下是引用yoyo2008在2005-4-26 15:23:00的发言： "" as t1,"" 能解释一下是什么意思吗?

"" as t1的意思就是没有意思，主要是保持列数一致

yoyo2008

你试过吗?我试了,但很乱的,能否再看看前面我的需求?谢谢!

cofe2000

我看的眼睛都晕了，试试看吧

SELECT [Contract and Revenue(2002-2005)].[存档年Year], [Contract and Revenue(2002-2005)].[存档季Quarter], [Contract and Revenue(2002-2005)].[存档月份Month], [Contract and Revenue(2002-2005)].[区域/Area], [Contract and Revenue(2002-2005)].[客户编号/Subscriber Code], [Contract and Revenue(2002-2005)].[合同编号/ Contract Number], [Contract and Revenue(2002-2005)].[客户名称/ Subscriber Name], [Contract and Revenue(2002-2005)].[席位/小时License/Hour], [Contract and Revenue(2002-2005)].[月数/Months], [Contract and Revenue(2002-2005)].[合同金额/Contract Value], [Contract and Revenue(2002-2005)].[签约人/Handled By], [Contract and Revenue(2002-2005)].[开始日期Star Date], [Contract and Revenue(2002-2005)].[结束日期End Date], [Contract and Revenue(2002-2005)].[存档日期], [Contract and Revenue(2002-2005)].[回款金额Accepted], [Contract and Revenue(2002-2005)].[待收金额 Due], [Contract and Revenue(2002-2005)].[坏账Dead], [Contract and Revenue(2002-2005)].[备注 Remark] FROM [Contract and Revenue(2002-2005)]; union all

SELECT [Contract and Revenue(2002-2005)].[存档月份Month] & " 小计" AS 表达式1, "" as t1,[Contract and Revenue(2002-2005)].[存档月份Month] & " 小计" AS t2,"" as t3,"" as t4,"" as t5,"" as t6,"" as t7,"" as t8,"" as t9,"" as t10,"" as t11,"" as t12,"" as t13,"" as t14,"" as t15,"" as t16,Sum([Contract and Revenue(2002-2005)].[合同金额/Contract Value])AS ValueAll FROM [Contract and Revenue(2002-2005)] GROUP BY [Contract and Revenue(2002-2005)].[存档月份Month] & " 小计"

UNION ALL select "总计", "" as t1,"总计","" as t3,"" as t4,"" as t5,"" as t6,"" as t7,"" as t8,"" as t9,"" as t10,"" as t11,"" as t12,"" as t13,"" as t14,"" as t15,"" as t16,sum([合同金额/Contract Value]) from [Contract and Revenue(2002-2005)] ORDER BY [存档月份Month];

yoyo2008

为什么有"2002-3 小计 "等等一行写了两次, 这样不好看的,怎么改?

小计和总计的数据怎么放在了备注里了呀,希望还在合同金额下面.

烦再看看

yoyo2008

我明白了

yoyo2008

谢谢

[em04]

yoyo2008

还有问题[em04]

若想继续按年小计,按季度小计,再总计,我已经照上面的格式做了,但显示"试图执行的查询中不包含作为合计函数一部分的特定表达式[Contract and Revenue(2002-2005)].[存档年Year] & "小计" 为什么??

以下是我加了想作按年小计的语句

UNION ALL SELECT [Contract and Revenue(2002-2005)].[存档年Year] & "小计" AS t1,"" as t2,"" as t3,"" as t4,"" as t5,"" as t6,"" as t7,"" as t8,"" as t9,Sum([Contract and Revenue(2002-2005)].[合同金额/Contract Value])AS t10,"" as t11,"" as t12,"" as t13,"" as t14,"" as t15,"" as t16,"" as t17,"" as t18 FROM [ Contract and Revenue(2002-2005)]

cofe2000

SELECT [Contract and Revenue(2002-2005)].[存档年Year], [Contract and Revenue(2002-2005)].[存档季Quarter], [Contract and Revenue(2002-2005)].[存档月份Month], [Contract and Revenue(2002-2005)].[区域/Area], [Contract and Revenue(2002-2005)].[客户编号/Subscriber Code], [Contract and Revenue(2002-2005)].[合同编号/ Contract Number], [Contract and Revenue(2002-2005)].[客户名称/ Subscriber Name], [Contract and Revenue(2002-2005)].[席位/小时License/Hour], [Contract and Revenue(2002-2005)].[月数/Months], [Contract and Revenue(2002-2005)].[合同金额/Contract Value], [Contract and Revenue(2002-2005)].[签约人/Handled By], [Contract and Revenue(2002-2005)].[开始日期Star Date], [Contract and Revenue(2002-2005)].[结束日期End Date], [Contract and Revenue(2002-2005)].[存档日期], [Contract and Revenue(2002-2005)].[回款金额Accepted], [Contract and Revenue(2002-2005)].[待收金额 Due], [Contract and Revenue(2002-2005)].[坏账Dead], [Contract and Revenue(2002-2005)].[备注 Remark] FROM [Contract and Revenue(2002-2005)]; union all

SELECT [Contract and Revenue(2002-2005)].[存档月份Month] & " 小计" AS 表达式1, "" as t1,[Contract and Revenue(2002-2005)].[存档月份Month] & " 小计" AS t2,"" as t3,"" as t4,"" as t5,"" as t6,"" as t7,"" as t8,"" as t9,"" as t10,"" as t11,"" as t12,"" as t13,"" as t14,"" as t15,"" as t16,Sum([Contract and Revenue(2002-2005)].[合同金额/Contract Value])AS ValueAll FROM [Contract and Revenue(2002-2005)] GROUP BY [Contract and Revenue(2002-2005)].[存档月份Month] & " 小计"

union all

SELECT [Contract and Revenue(2002-2005)].[存档年Year] & "小计" AS 表达式1, "" as t1,[Contract and Revenue(2002-2005)].[存档年Year] & "小计" AS t2,"" as t3,"" as t4,"" as t5,"" as t6,"" as t7,"" as t8,"" as t9,"" as t10,"" as t11,"" as t12,"" as t13,"" as t14,"" as t15,"" as t16,Sum([Contract and Revenue(2002-2005)].[合同金额/Contract Value])AS ValueAll FROM [Contract and Revenue(2002-2005)] GROUP BY [Contract and Revenue(2002-2005)].[存档年Year] & "小计"

UNION ALL select "总计", "" as t1,"总计","" as t3,"" as t4,"" as t5,"" as t6,"" as t7,"" as t8,"" as t9,"" as t10,"" as t11,"" as t12,"" as t13,"" as t14,"" as t15,"" as t16,sum([合同金额/Contract Value]) from [Contract and Revenue(2002-2005)] ORDER BY [存档月份Month];

cofe2000

我对access也不懂的

请问一下楼主，为什么这段SQL代码中有两个分号，不是分号代表结束的吗？

如果说用UNION ALL联合查询要分号的话，为什么后面几个就不需要分号呢？

请多多指教！！

cofe2000

测试了一下，前面几个分号加或不加并不影响最后的结果，期待高手指点一下

这几天陈版主不知上哪里去了，呵呵