- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 关于SQL查询问题 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=116538)
-- 发布时间：2018/3/26 16:23:00
-- 关于SQL查询问题
S elect t1.[项目编号],t1.[客户名称],t1.[物料代码],t1.[物料名称],t1.[规格型号],t1.[客户对应物料代码],
t1.[客户对应物料名称],t1.[实发数量],t1.[批号],t2.[FJianYanShuiPing],t2.[FAQLC],t2.[FAQLDF],t2.[FAQLUT],
t2.[FAQLIR],t2.[FAQLWaiGuan],t2.[FAQLYaMinDianYa],t2.[FAQLLouDianLiu],t2.[FAQLXianXingXiShu],
t2.[FAQLChongJiDianLiu],t2.[FCeDianYa],t2.[FCePinLv],t2.[FCeDianRongLiangC],t2.[FCeSunHaoDF],
t2.[FCeNaiDianYaUT],t2.[FJueYuanDianZu],t2.[FCSL],t2.[FCUSL],t2.[FCLSL],t2.[FDFSL],t2.[FDFUSL],
t2.[FDFLSL],t2.[FD],t2.[FT],t2.[FF],t2.[FD_d],t2.[FL],t2.[FWenDu],t2.[FShiDu],t2.[FYaMinWaiGuan],
t2.[FYaMinDianZuChiCun],t2.[FYaMinDianYa],t2.[FLouDianLiu],t2.[FXianXingXiShu],t2.[FZuiDaChongJiDianLiu],
t2.[FJingDianRongLiang],t2.[FSunHaoQieJiao],t2.[FHuiLuDianYa],t2.[FLangYongDianYa],t2.[FNaiDianYa],
t2.[FJueYuanDianZu_1],t2.[FZuRan],t2.[FKeHanXing],t2.[FLaShengQiangDu],t2.[FWanQuQiangDu],
t2.[FCeYaMinWaiGuan],t2.[FCeYaMinDianZuChiCun],t2.[FCeYaMinDianYa],t2.[FCeLouDianLiu],
t2.[FCeXianXingXiShu],t2.[FCeZuiDaChongJiDianLiu],t2.[FCeJingDianRongLiang],t2.[FCeSunHaoZhengQieJiao],
t2.[FCeHuiLuDianYa],t2.[FCeLangYongDianLiu],t2.[FCeNaiDianYa],t2.[FCeJueYuanDianZu],t2.[FCeZuRan],
t2.[FCeKeHanXing],t2.[FCeLaShengQiangDu],t2.[FCeWanQuQiangDu],t2.[FCeShiBaoGao],
t2.[客户料号] from 项目测试数据看板 As t1
INNER JOIN 报告指标项次 As t2 ON t1.[客户对应物料代码]=t2.[客户料号]
where 项目编号= \'13\'
这段在数据库执行是可以的
——————————————————————————
Dim sql2 As String ="S elect t1.[项目编号],t1.[客户名称],t1.[物料代码],t1.[物料名称],t1.[规格型号],t1.[客户对应物料代码]," & _
"t1.[客户对应物料名称],t1.[实发数量],t1.[批号],t2.[FJianYanShuiPing],t2.[FAQLC],t2.[FAQLDF],t2.[FAQLUT]," & _
"t2.[FAQLIR],t2.[FAQLWaiGuan],t2.[FAQLYaMinDianYa],t2.[FAQLLouDianLiu],t2.[FAQLXianXingXiShu]," & _
"t2.[FAQLChongJiDianLiu],t2.[FCeDianYa],t2.[FCePinLv],t2.[FCeDianRongLiangC],t2.[FCeSunHaoDF]," & _
"t2.[FCeNaiDianYaUT],t2.[FJueYuanDianZu],t2.[FCSL],t2.[FCUSL],t2.[FCLSL],t2.[FDFSL],t2.[FDFUSL]," & _
"t2.[FDFLSL],t2.[FD],t2.[FT],t2.[FF],t2.[FD_d],t2.[FL],t2.[FWenDu],t2.[FShiDu],t2.[FYaMinWaiGuan]," & _
"t2.[FYaMinDianZuChiCun],t2.[FYaMinDianYa],t2.[FLouDianLiu],t2.[FXianXingXiShu],t2.[FZuiDaChongJiDianLiu]," & _
"t2.[FJingDianRongLiang],t2.[FSunHaoQieJiao],t2.[FHuiLuDianYa],t2.[FLangYongDianYa],t2.[FNaiDianYa]," & _
"t2.[FJueYuanDianZu_1],t2.[FZuRan],t2.[FKeHanXing],t2.[FLaShengQiangDu],t2.[FWanQuQiangDu]," & _
"t2.[FCeYaMinWaiGuan],t2.[FCeYaMinDianZuChiCun],t2.[FCeYaMinDianYa],t2.[FCeLouDianLiu]," & _
"t2.[FCeXianXingXiShu],t2.[FCeZuiDaChongJiDianLiu],t2.[FCeJingDianRongLiang],t2.[FCeSunHaoZhengQieJiao]," & _
"t2.[FCeHuiLuDianYa],t2.[FCeLangYongDianLiu],t2.[FCeNaiDianYa],t2.[FCeJueYuanDianZu],t2.[FCeZuRan]," & _
"t2.[FCeKeHanXing],t2.[FCeLaShengQiangDu],t2.[FCeWanQuQiangDu],t2.[FCeShiBaoGao]," & _
"t2.[客户料号] from 项目测试数据看板 As t1" & _
"INNER JOIN 报告指标项次 As t2 ON t1.[客户对应物料代码]=t2.[客户料号]" & _
"where 项目编号= \'" & bh & "\'"
这段执行就报错：无法绑定由多个部分组成的标识符"t1.批号"等等，这是什么原因？我只分行加了" & _这个啊
-- 发布时间：2018/3/26 16:42:00
--
1、在这里执行第一段sql语句，是否可以？
http://www.foxtable.com/webhelp/scr/1484.htm
2、在1可以正常测试的前提下，如果第二段无法测试，请贴出完整代码。
-- 发布时间：2018/3/26 17:01:00
--
1可以
2，前面Dim bh As String = Forms("测试窗口").Controls("TextBox39").Value
后面
Dim q2 As new QueryBuilder
q2.TableName = "统计"
q2.SelectString = sql2
q2.C
q2.Build
-- 发布时间：2018/3/26 17:04:00
--
2、加上和执行代码 output.show(sql2)
在命令窗口弹出sql2的文本以后，拷贝到sql测试窗口运行，看能否运行。
-- 发布时间：2018/3/26 17:35:00
--
老师还是不行，单独在窗口运行SQL语句(不带分行的)都正常
此主题相关图片如下：qq截图20180326173401.jpg
[此贴子已经被作者于2018/3/26 17:35:47编辑过]
-- 发布时间：2018/3/26 18:16:00
--
执行output.show(sql2)，在命令窗口显示的sql语句是什么？
对比和你第一段的代码，有什么不同。
-- 发布时间：2018/3/28 16:01:00
--
老师，命令窗口也是报上面图中一样的错，但是我把所有分行去了，连成一行，这句语句就是对的，这是什么原因？
我想分行，这样能看的清晰点也方便后面添加新的。请问如何处理？之前有句别的也是用分行处理就可以的
-- 发布时间：2018/3/28 16:06:00
--
贴出你【我把所有分行去了，连成一行，这句语句就是对的】的sql2的代码看看。
添加分行不会导致sql语句错误，认真对比之间的细微区别，特别是空格符号。
-- 发布时间：2018/3/28 16:32:00
--
Dim sql2 As String ="S elect t1.[项目编号],t1.[客户名称],t1.[物料代码],t1.[物料名称],t1.[规格型号],t1.[客户对应物料代码],t1.[客户对应物料名称],t1.[实发数量],t1.[电容量单位],t1.[批号],t2.[FJianYanShuiPing],t2.[FAQLC],t2.[FAQLDF],t2.[FAQLUT],t2.[FAQLIR],t2.[FAQLWaiGuan],t2.[FAQLYaMinDianYa],t2.[FAQLLouDianLiu],t2.[FAQLXianXingXiShu],t2.[FAQLChongJiDianLiu],t2.[FCeDianYa],t2.[FCePinLv],t2.[FCeDianRongLiangC],t2.[FCeSunHaoDF],t2.[FCeNaiDianYaUT],t2.[FJueYuanDianZu],t2.[FCSL],t2.[FCUSL],t2.[FCLSL],t2.[FDFSL],t2.[FDFUSL],t2.[FDFLSL],t2.[FD],t2.[FT],t2.[FF],t2.[FD_d],t2.[FL],t2.[FWenDu],t2.[FShiDu],t2.[FYaMinWaiGuan],t2.[FYaMinDianZuChiCun],t2.[FYaMinDianYa],t2.[FLouDianLiu],t2.[FXianXingXiShu],t2.[FZuiDaChongJiDianLiu],t2.[FJingDianRongLiang],t2.[FSunHaoQieJiao],t2.[FHuiLuDianYa],t2.[FLangYongDianYa],t2.[FNaiDianYa],t2.[FJueYuanDianZu_1],t2.[FZuRan],t2.[FKeHanXing],t2.[FLaShengQiangDu],t2.[FWanQuQiangDu],t2.[FCeYaMinWaiGuan],t2.[FCeYaMinDianZuChiCun],t2.[FCeYaMinDianYa],t2.[FCeLouDianLiu],t2.[FCeXianXingXiShu],t2.[FCeZuiDaChongJiDianLiu],t2.[FCeJingDianRongLiang],t2.[FCeSunHaoZhengQieJiao],t2.[FCeHuiLuDianYa],t2.[FCeLangYongDianLiu],t2.[FCeNaiDianYa],t2.[FCeJueYuanDianZu],t2.[FCeZuRan],t2.[FCeKeHanXing],t2.[FCeLaShengQiangDu],t2.[FCeWanQuQiangDu],t2.[FCeShiBaoGao],t2.[客户物料代码] from 项目测试数据看板 As t1 INNER JOIN 报告指标项次 As t2 ON t1.[客户对应物料代码]=t2.[客户物料代码] where 项目编号= \'" & bh & "\'"
这句一整行的是对的，发帖的分行是错的
-- 发布时间：2018/3/28 16:58:00
--
老师，解决了，在from前面加2个空格，在第一个INNER JOIN前加1个空格，现在可以运行了，请告知原因。
Dim sql2 As String ="S elect t1.[项目编号],t1.[客户名称],t1.[物料代码],t1.[物料名称],t1.[规格型号],t1.[客户对应物料代码],t1.[客户对应物料名称],t1.[实发数量],t1.[电容量单位],t1.[批号]," & _
"t2.[FJianYanShuiPing],t2.[FAQLC],t2.[FAQLDF],t2.[FAQLUT],t2.[FAQLIR],t2.[FAQLWaiGuan],t2.[FAQLYaMinDianYa],t2.[FAQLLouDianLiu],t2.[FAQLXianXingXiShu],t2.[FAQLChongJiDianLiu]," & _
"t2.[FCeDianYa],t2.[FCePinLv],t2.[FCeDianRongLiangC],t2.[FCeSunHaoDF],t2.[FCeNaiDianYaUT],t2.[FJueYuanDianZu],t2.[FCSL],t2.[FCUSL],t2.[FCLSL],t2.[FDFSL],t2.[FDFUSL],t2.[FDFLSL]," & _
"t2.[FD],t2.[FT],t2.[FF],t2.[FD_d],t2.[FL],t2.[FWenDu],t2.[FShiDu],t2.[FYaMinWaiGuan],t2.[FYaMinDianZuChiCun],t2.[FYaMinDianYa],t2.[FLouDianLiu],t2.[FXianXingXiShu]," & _
"t2.[FZuiDaChongJiDianLiu],t2.[FJingDianRongLiang],t2.[FSunHaoQieJiao],t2.[FHuiLuDianYa],t2.[FLangYongDianYa],t2.[FNaiDianYa],t2.[FJueYuanDianZu_1],t2.[FZuRan],t2.[FKeHanXing]," & _
"t2.[FLaShengQiangDu],t2.[FWanQuQiangDu],t2.[FCeYaMinWaiGuan],t2.[FCeYaMinDianZuChiCun],t2.[FCeYaMinDianYa],t2.[FCeLouDianLiu],t2.[FCeXianXingXiShu],t2.[FCeZuiDaChongJiDianLiu]," & _
"t2.[FCeJingDianRongLiang],t2.[FCeSunHaoZhengQieJiao],t2.[FCeHuiLuDianYa],t2.[FCeLangYongDianLiu],t2.[FCeNaiDianYa],t2.[FCeJueYuanDianZu],t2.[FCeZuRan],t2.[FCeKeHanXing]," & _
"t2.[FCeLaShengQiangDu],t2.[FCeWanQuQiangDu],t2.[FCeShiBaoGao],t2.[客户物料代码]," & _
"t3.[样本量字码],t4.[接收数],t4.[拒收数],t4.[变更样本量],t5.[接收数],t5.[拒收数],t5.[变更样本量],t6.[接收数],t6.[拒收数],t6.[变更样本量]" & _
" from 项目测试数据看板 As t1" & _
" INNER JOIN 报告指标项次 As t2 ON t1.[客户对应物料代码]=t2.[客户物料代码]" & _
"INNER JOIN 样本量 As t3 ON t2.[FJianYanShuiPing]=t3.[检验水平]" & _
"INNER JOIN 接收质量水平表 As t4 ON t4.[接收质量水平]=t2.[FAQLC] and t4.[样本量字码]=t3.[样本量字码]" & _
"INNER JOIN 接收质量水平表 As t5 ON t5.[接收质量水平]=t2.[FAQLDF] and t5.[样本量字码]=t3.[样本量字码]" & _
"INNER JOIN 接收质量水平表 As t6 ON t6.[接收质量水平]=t2.[FAQLWaiGuan] and t6.[样本量字码]=t3.[样本量字码]" & _
"where 项目编号= \'" & bh & "\' and 批量= \'" & pl & "\'"
[此贴子已经被作者于2018/3/28 16:59:09编辑过]