ClientDataSet1 SQL表自己关联自己报错？请大神指点

[weixin_43788560]

建立一个客户表，表里有个字段是介绍者，介绍者本身也是客户表里的记录
在SQL里查询执行没有问题：
select CLI.BC_ID,CLI.BC_Number,CLI.BC_FULLNAME,CLI.BC_DZ,CLI.BC_DH,CLI.BC_LXR,isnull(BG.G_Name,'') as BC_CPFL,
CLI.BC_LXRDH,CLI.BC_FZR,CLI.BC_FZRDH,
isnull(BL.BCL_Name,'') as BC_KHJB,CLI.BC_ZJSSL,ISNULL(DQ.BDQ_Name,'') AS BC_KHDQ,
isnull(PQ.BCR_Name,'') as BC_KHPQ,isnull(bc2.BC_FULLNAME,'') as BC_JSZ,
isnull(be1.E_Name,'') as BC_SCKF,isnull(BBM.BBM_Name,'') as BC_SHBM,CLI.BC_JYFW,CLI.BC_QTSM,
CLI.BC_HZRQ,CLI.BC_JDRQ from B_CLIENTS AS CLI
left join B_Goods as BG on G_ID=BC_CPFL
left join B_Clients_Level as BL on BCL_ID=BC_KHJB
left join B_Clients_DQ as DQ on BDQ_ID=BC_KHDQ
left join B_Clients_Region as PQ on BCR_ID=BC_KHPQ
left join B_CLIENTS as bc2 on bc2.BC_ID=cli.bc_jsz
left join B_Employee as BE1 on be1.E_ID=CLI.BC_SCKF
left join B_Clients_BM as BBM on BBM_ID=CLI.BC_SHBM
where CLI.BC_DELETED=0

在DELPHI 中三层
sql:='select CLI.BC_ID,CLI.BC_Number,CLI.BC_FULLNAME,CLI.BC_DZ,CLI.BC_DH,CLI.BC_LXR,isnull(BG.G_Name,'''') as BC_CPFL,';
sql:=sql+'CLI.BC_LXRDH,CLI.BC_FZR,CLI.BC_FZRDH,';
sql:=sql+'isnull(BL.BCL_Name,'''') as BC_KHJB,CLI.BC_ZJSSL,ISNULL(DQ.BDQ_Name,'''') AS BC_KHDQ,';
sql:=sql+'isnull(PQ.BCR_Name,'''') as BC_KHPQ,isnull(bc2.BC_FULLNAME,'''') as BC_JSZ,';
sql:=sql+'isnull(be1.E_Name,'''') as BC_SCKF,isnull(BBM.BBM_Name,'''') as BC_SHBM,CLI.BC_JYFW,CLI.BC_QTSM, ';
sql:=sql+'CLI.BC_HZRQ,CLI.BC_JDRQ from B_CLIENTS AS CLI';
sql:=sql+'left join B_Goods as BG on G_ID=BC_CPFL ';
sql:=sql+'left join B_Clients_Level as BL on BCL_ID=BC_KHJB ';
sql:=sql+'left join B_Clients_DQ as DQ on BDQ_ID=BC_KHDQ ';
sql:=sql+'left join B_Clients_Region as PQ on BCR_ID=BC_KHPQ ';

sql:=sql+'left join B_CLIENTS as bc2 on bc2.BC_ID=(select a2.BC_ID from B_Clients as a2 where a2.bc_id=cli.bc_jsz) ';
sql:=sql+'left join B_Employee as BE1 on be1.E_ID=CLI.BC_SCKF ';
sql:=sql+'left join B_Clients_BM as BBM on BBM_ID=CLI.BC_SHBM ';
sql:=sql+' where CLI.BC_DELETED=0 ';
ClientModule1.ClientDataSet1.Close;
ClientModule1.ClientDataSet1.CommandText:=sql;
ClientModule1.ClientDataSet1.Open;

执行时：提示：
Remote error :[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]无法绑定由多个部分组成的标识符“cli.bc_jsz”

请问是哪里出了问题？

[victor_yang]

(select a2.BC_ID from B_Clients as a2 where a2.bc_id=cli.bc_jsz) ';

这里是个子查询，不认识外部定义的cli.bc_jsz

[victor_yang]

兄弟，我建议你用TStrings来写sql代码，你这样写检查起来很痛苦，我平时我的写法是这样的
vsql.add('select a.XXX,b.XXX,c.YYY');
vsql.add('from tbTable1 a ');
vsql.add('left join tbTable2 b on a.PKID=b.PKID');
vsql.add('left join tbTable3 c on b.FKID=c.FKID');
vsql.add(Format('where a.bc_jsz=''%s''',[sKey]));

这样看起来会不会清晰很多呢？