建立一個客戶表，表里有個欄位是介紹者，介紹者本身也是客戶表里的記錄
在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”
請問是哪里出了問題？
uj5u.com熱心網友回復：
兄弟，我建議你用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]));
這樣看起來會不會清晰很多呢？
uj5u.com熱心網友回復：
(select a2.BC_ID from B_Clients as a2 where a2.bc_id=cli.bc_jsz) ';這里是個子查詢，不認識外部定義的cli.bc_jsz
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/11137.html
標籤：數據庫相關
下一篇：求解floattostr((366.32-375)-(-8.68)) 或是floattostr((366.32-375)+8.68); 不等0