SHAPE {SELECT * from yhxx } AS Command1 COMPUTE Command1 --聚集函数才需要别名,Command1 已经是别名了,再取别名大概语法不对吧 BY [用户编号],[用户姓名] --列名要用方括号,单引号是字符串值
非常感谢,那请帮我看一下,以下是完整的语句:SHAPE {SELECT c.用户编号, c.用户姓名, c.小区, c.楼号, c.单元号, c.房间号, c.表序号, c.表型名称, c.计费类型, c.计费类别, c.单价, c.购买次数, c.购买量, c.购买金额, c.操作日期, c.操作员, c.操作次数, c.总操作次数, c.操作类型, c.票据单号, c.有效性, d.上次余额, d.现金余额, 实收金额 FROM ( ( SELECT b.用户编号, b.用户姓名, b.小区, b.楼号, b.单元号, b.房间号, b.表序号, b.表型名称,b.计费类型, b.计费类别, b.单价, b.购买次数, b.购买量, b.购买金额, b.操作日期, b.操作员, b.操作次数, b.总操作次数, b.操作类型, b.票据单号, b.有效性, b.修正次数 FROM (SELECT * FROM yhxx WHERE 用户编号 = 3 ) AS a LEFT JOIN ( SELECT * FROM sdjl WHERE 票据单号 = 1 AND 操作类型 = '购买' AND 有效性 = '有效' ORDER BY 表序号 ) AS b ON a.用户编号 = b.用户编号 ) AS c LEFT JOIN (SELECT * FROM yueb WHERE 票据单号 = 1 ) AS d ON c.用户编号 = d.用户编号 AND c.票据单号 = d.票据单号 ) order by c.用户编号,c.表序号,c.操作日期 } AS Command1 COMPUTE Command1 as Command2 BY '用户编号','用户姓名','小区','楼号','单元号','房间号','票据单号','上次余额','现金余额','实收金额','操作员','操作类型'
1)子查询不能有 ORDER BY 2)最外层的 FROM (...) 缺少别名 3)不需要 as Command2 4)列名要用方括号
最终结果改为:SHAPE {SELECT c.用户编号, c.用户姓名, c.小区, c.楼号, c.单元号, c.房间号, c.表序号, c.表型名称, c.计费类型, c.计费类别, c.单价, c.购买次数, c.购买量, c.购买金额, c.操作日期, c.操作员, c.操作次数, c.总操作次数, c.操作类型, c.票据单号, c.有效性, d.上次余额, d.现金余额, 实收金额 FROM ( SELECT b.用户编号, b.用户姓名, b.小区, b.楼号, b.单元号, b.房间号, b.表序号, b.表型名称,b.计费类型, b.计费类别, b.单价, b.购买次数, b.购买量, b.购买金额, b.操作日期, b.操作员, b.操作次数, b.总操作次数, b.操作类型, b.票据单号, b.有效性, b.修正次数 FROM (SELECT * FROM yhxx WHERE 用户编号 = 3 ) AS a LEFT JOIN ( SELECT * FROM sdjl WHERE 票据单号 = 1 AND 操作类型 = '购买' AND 有效性 = '有效' ) AS b ON a.用户编号 = b.用户编号 ) AS c LEFT JOIN (SELECT * FROM yueb WHERE 票据单号 = 1 ) AS d ON c.用户编号 = d.用户编号 AND c.票据单号 = d.票据单号 } AS Command1 COMPUTE Command1 BY [用户编号],[用户姓名],[小区],[楼号],[单元号],[房间号],[票据单号],[上次余额],[现金余额],[实收金额],[操作员],[操作类型]经验证{}内部语句返回结果正常,但是整语句执行时就有错误提示: [Microsoft][ODBC SQL Server Driver]语法错误或违反访问规则请帮忙再分析一下,谢谢。
首先谢谢TIGER_ZHAO,我的不少问题你都回答过,但是甭管重复不重复提问,我急着用呢。我需要答案,此刻。
COMPUTE Command1 --聚集函数才需要别名,Command1 已经是别名了,再取别名大概语法不对吧
BY [用户编号],[用户姓名] --列名要用方括号,单引号是字符串值
非常感谢,那请帮我看一下,以下是完整的语句:SHAPE {SELECT c.用户编号, c.用户姓名, c.小区, c.楼号, c.单元号, c.房间号, c.表序号, c.表型名称, c.计费类型, c.计费类别, c.单价, c.购买次数, c.购买量, c.购买金额, c.操作日期, c.操作员, c.操作次数,
c.总操作次数, c.操作类型, c.票据单号, c.有效性, d.上次余额, d.现金余额, 实收金额 FROM
(
(
SELECT b.用户编号, b.用户姓名, b.小区, b.楼号, b.单元号, b.房间号, b.表序号, b.表型名称,b.计费类型, b.计费类别, b.单价, b.购买次数, b.购买量, b.购买金额, b.操作日期, b.操作员,
b.操作次数, b.总操作次数, b.操作类型, b.票据单号, b.有效性, b.修正次数
FROM
(SELECT * FROM yhxx WHERE 用户编号 = 3 ) AS a
LEFT JOIN
( SELECT * FROM sdjl WHERE 票据单号 = 1 AND 操作类型 = '购买' AND 有效性 = '有效' ORDER BY 表序号 ) AS b
ON a.用户编号 = b.用户编号
) AS c
LEFT JOIN (SELECT * FROM yueb WHERE 票据单号 = 1 ) AS d ON c.用户编号 = d.用户编号 AND c.票据单号 = d.票据单号
)
order by c.用户编号,c.表序号,c.操作日期
} AS Command1 COMPUTE Command1 as Command2 BY '用户编号','用户姓名','小区','楼号','单元号','房间号','票据单号','上次余额','现金余额','实收金额','操作员','操作类型'
2)最外层的 FROM (...) 缺少别名
3)不需要 as Command2
4)列名要用方括号
最终结果改为:SHAPE {SELECT c.用户编号, c.用户姓名, c.小区, c.楼号, c.单元号, c.房间号, c.表序号, c.表型名称, c.计费类型, c.计费类别, c.单价, c.购买次数, c.购买量, c.购买金额, c.操作日期, c.操作员, c.操作次数,
c.总操作次数, c.操作类型, c.票据单号, c.有效性, d.上次余额, d.现金余额, 实收金额 FROM
(
SELECT b.用户编号, b.用户姓名, b.小区, b.楼号, b.单元号, b.房间号, b.表序号, b.表型名称,b.计费类型, b.计费类别, b.单价, b.购买次数, b.购买量, b.购买金额, b.操作日期, b.操作员,
b.操作次数, b.总操作次数, b.操作类型, b.票据单号, b.有效性, b.修正次数
FROM
(SELECT * FROM yhxx WHERE 用户编号 = 3 ) AS a
LEFT JOIN
( SELECT * FROM sdjl WHERE 票据单号 = 1 AND 操作类型 = '购买' AND 有效性 = '有效' ) AS b
ON a.用户编号 = b.用户编号
) AS c
LEFT JOIN (SELECT * FROM yueb WHERE 票据单号 = 1 ) AS d ON c.用户编号 = d.用户编号 AND c.票据单号 = d.票据单号 } AS Command1 COMPUTE Command1 BY [用户编号],[用户姓名],[小区],[楼号],[单元号],[房间号],[票据单号],[上次余额],[现金余额],[实收金额],[操作员],[操作类型]经验证{}内部语句返回结果正常,但是整语句执行时就有错误提示:
[Microsoft][ODBC SQL Server Driver]语法错误或违反访问规则请帮忙再分析一下,谢谢。
使用 SHAPE 有一个专门的驱动。