select c1.FCUSTID 内码, c1.FNUMBER 客户编码, c1.FFORBIDSTATUS 禁用状态, c1.FFAX 报表代理商, c1.F_GN_GROUPNUMBER 客户分组编号, c1.F_QYJL 区域经理, c1.F_KFY 订单员,c2.FNAME 客户名称 from T_BD_CUSTOMER c1, T_BD_CUSTLOCATION c2 inner join T_BD_CUSTLOCATION c2 on (c1. FNUMBER = c2. FNUMBER) where FFORBIDSTATUS = "A"
时间: 2024-04-03 11:36:17 浏览: 121
这是一个SQL查询语句,查询T_BD_CUSTOMER表中的客户内码、客户编码、禁用状态、报表代理商、客户分组编号、区域经理、订单员和T_BD_CUSTLOCATION表中的客户名称,其中条件是禁用状态为A的记录,并且T_BD_CUSTOMER表和T_BD_CUSTLOCATION表是通过客户编码进行关联查询的。
相关问题
debug下面的代码:select c1.FCUSTID 内码, c1.FNUMBER 客户编码, c1.FFORBIDSTATUS 禁用状态, c1.FFAX 报表代理商, c1.F_GN_GROUPNUMBER 客户分组编号, c1.F_QYJL 区域经理, c1.F_KFY 订单员,c2.FNAME 客户名称 from T_BD_CUSTOMER c1, T_BD_CUSTLOCATION c2 inner join T_BD_CUSTLOCATION c2 on (c1. FNUMBER = c2. FNUMBER) where FFORBIDSTATUS = "A"
这个SQL查询语句存在一个语法错误,应该改成下面这样:
```
SELECT c1.FCUSTID 内码, c1.FNUMBER 客户编码, c1.FFORBIDSTATUS 禁用状态, c1.FFAX 报表代理商, c1.F_GN_GROUPNUMBER 客户分组编号, c1.F_QYJL 区域经理, c1.F_KFY 订单员, c2.FNAME 客户名称
FROM T_BD_CUSTOMER c1
INNER JOIN T_BD_CUSTLOCATION c2 ON c1.FNUMBER = c2.FNUMBER
WHERE FFORBIDSTATUS = 'A'
```
这个查询语句使用了INNER JOIN关键字来关联T_BD_CUSTOMER表和T_BD_CUSTLOCATION表,按照FNUMBER字段进行关联。同时,将条件筛选放到了WHERE子句中,使用单引号来表示字符串常量。
debug下面的代码:SELECT c1.FCUSTID 客户内码, c1.FNUMBER 客户编码, c1.FFORBIDSTATUS 禁用状态, c1.FFAX 报表代理商, c1.FSALDEPTID 部门编码, c1.F_GN_GROUPNUMBER 客户分组编号, c1.FSELLER 销售员编码 c1.F_QYJL 区域经理编码, c1.FSELLER 销售员编码, c1.F_KFY 客服员编码, c1.F_KFYH 客服用户编码, c2.FPROVINCE 省份编码, c2.FCITY 地市编码, c3.FNAME 客户名称, c4.FNAME 销售员, c5.FNAME 区域经理, c6.FNAME 客服员, c7.FNAME 客服用户, c8.FDATAVALUE 省份名称, c9.FDATAVALUE 地市名称, c10.FNAME 部门名称 FROM T_BD_CUSTOMER c1 LEFT JOIN T_BD_CUSTOMEREXT c2 ON c1.FCUSTID = c2.FCUSTID LEFT JOIN T_BD_CUSTOMER_L c3 ON c1.FCUSTID = c3.FCUSTID LEFT JOIN T_HR_EMPINFO_L c4 ON c1.FSELLER = C4.fid LEFT JOIN T_HR_EMPINFO_L c5 ON c1.F_QYJL = C4.fid LEFT JOIN T_HR_EMPINFO_L c6 ON c1.F_KFY = C4.fid LEFT JOIN T_HR_EMPINFO_L c7 ON c1.F_KFYH = C4.fid LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L c8 ON c5.FentryID = C2.FPROVINCE LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L c9 ON c6.FentryID = C2.FCITY LEFT JOIN T_BD_DEPARTMENT_L c10 ON c7.FDEPTID = C1.FSALDEPTID
这段代码有一些问题,主要是缺少关键字和别名。修正后的代码如下:
SELECT c1.FCUSTID AS 客户内码, c1.FNUMBER AS 客户编码, c1.FFORBIDSTATUS AS 禁用状态, c1.FFAX AS 报表代理商, c1.FSALDEPTID AS 部门编码, c1.F_GN_GROUPNUMBER AS 客户分组编号, c1.FSELLER AS 销售员编码, c1.F_QYJL AS 区域经理编码, c1.F_KFY AS 客服员编码, c1.F_KFYH AS 客服用户编码, c2.FPROVINCE AS 省份编码, c2.FCITY AS 地市编码, c3.FNAME AS 客户名称, c4.FNAME AS 销售员, c5.FNAME AS 区域经理, c6.FNAME AS 客服员, c7.FNAME AS 客服用户, c8.FDATAVALUE AS 省份名称, c9.FDATAVALUE AS 地市名称, c10.FNAME AS 部门名称
FROM T_BD_CUSTOMER c1
LEFT JOIN T_BD_CUSTOMEREXT c2 ON c1.FCUSTID = c2.FCUSTID
LEFT JOIN T_BD_CUSTOMER_L c3 ON c1.FCUSTID = c3.FCUSTID
LEFT JOIN T_HR_EMPINFO_L c4 ON c1.FSELLER = c4.FID
LEFT JOIN T_HR_EMPINFO_L c5 ON c1.F_QYJL = c5.FID
LEFT JOIN T_HR_EMPINFO_L c6 ON c1.F_KFY = c6.FID
LEFT JOIN T_HR_EMPINFO_L c7 ON c1.F_KFYH = c7.FID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L c8 ON c5.FentryID = c2.FPROVINCEID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L c9 ON c6.FentryID = c2.FCITYID
LEFT JOIN T_BD_DEPARTMENT_L c10 ON c1.FSALDEPTID = c10.FDEPTID
注意修正后的代码中:
1. 每个字段都加上了别名,方便后续使用。
2. JOIN 语句中,每个表都加上了别名,防止重名错误。
3. JOIN 语句中,关联的字段应该是带有 ID 后缀的字段,而非原始字段。
阅读全文