表1 期初表 欄位: 業務員, 客戶,所屬月份，期初余額，期初未開票余額
表2 銷售表 欄位：業務員,客戶,所屬月份，銷售額
表3 開票表 欄位：業務員,客戶,所屬月份，開票金額
表4 收款表 欄位：業務員,客戶,所屬月份，收款金額
要求查詢按特定的所屬月份來查,結果如下
業務員 sum(期初余額) sum(銷售額) sum(收款金額) 期末余額 sum(期初未開票余額) sum(開票金額) 期末未開票余額
其中 期末余額和期末未開票余額是計算欄位 期末余額＝期初余額+銷售額-收款金額
期末未開票余額＝期初未開票余額+銷售額-開票金額
4張表每一條記錄均不能少
SQL陳述句怎么寫，謝謝！
深夜求助!
uj5u.com熱心網友回復：
select 業務員, 所屬月份, sum(期初余額) as 期初余額, sum(銷售額) as 銷售額, sum(收款金額) as 收款金額,
sum(期初余額) + sum(銷售額) + sum(收款金額) as 期末余額,
sum(期初未開票余額) as 期初未開票余額, sum(開票金額) as 開票金額,
sum(期初未開票余額) + sum(銷售額) - sum(開票金額) as 期末未開票余額
from
(
select 業務員, 所屬月份, 期初余額, 0 as 銷售額, 0 as 收款金額, 期初未開票余額, 0 as 開票金額, 0 as 期末未開票余額
from 期初表
union all
select 業務員, 所屬月份, 0 as 期初余額, 銷售額, 0 as 收款金額, 0 as 期初未開票余額, 0 as 開票金額, 0 as 期末未開票余額
from 銷售表
union all
select 業務員, 所屬月份, 0 as 期初余額, 0 as 銷售額, 0 as 收款金額, 0 as 期初未開票余額, 開票金額, 0 as 期末未開票余額
from 開票表
union all
select 業務員, 所屬月份, 0 as 期初余額, 0 as 銷售額, 收款金額, 0 as 期初未開票余額, 0 as 開票金額, 0 as 期末未開票余額
from 收款表
) Org
group by 業務員, 所屬月份
uj5u.com熱心網友回復：
sum(期初余額) + sum(銷售額) + sum(收款金額) as 期末余額,應是
sum(期初余額) + sum(銷售額) - sum(收款金額) as 期末余額,
我寫錯了
uj5u.com熱心網友回復：
SQL.Add('select ywy,sum(qcye) as qc ,sum(ysje) as xsje,sum(je) as skje, '+' sum(qcye)+sum(ysje)-sum(je) as qmye, '+
' sum(qcfpje) as qcfp, sum(jshj) as fpje,'+
' sum(qcfpje)+sum(ysje)-sum(jshj) as qmfpye '+
' from '+
'( '+
' select ywy,qc,0 as xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye '+
' from yskqcye '+
' union all '+
' select ywy,0 as qc,xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye '+
' from xsb '+
' union all '+
' select ywy,0 as qc,0 as xsje,skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye '+
' from skb '+
')org '+
' where cplx='''+comcplx.Text+''' and ssyf='''+comssyf.Text+''''+
' group by ywy ');
編譯后出錯
Invalid use of keyword
Token :select
Line Numbe:1
uj5u.com熱心網友回復：
無效使用關鍵字。應是SQL陳述句寫錯了, 一下看不出來，你測驗一下吧SQL.Add前面有什么嗎？查一查sql.text中'select ywy,sum(qcye) as qc ,sum(ysje) as xsje,sum(je) as skje,'前面有什么沒有，是不是沒有清除。
SQL陳述句最好先在SQL Studio中運行成功后再轉入到Delphi
你可以先測驗中間那段
select ywy,qc,0 as xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye
from yskqcye
union all
select ywy,0 as qc,xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye
from xsb
union all
select ywy,0 as qc,0 as xsje,skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye
from skb
另外不建議 ' where cplx='''+comcplx.Text+''' and ssyf='''+comssyf.Text+''''這種寫法。用Parameters
uj5u.com熱心網友回復：
綜合類匯總資料，往往通SQL不能問題。需要通過資料集回圈，統計資料。uj5u.com熱心網友回復：
忘了說，我用的是很早的padradox資料庫找不出原因
procedure TForm1.comCplxChange(Sender: TObject);
begin
if comHzfs.Text='按業務員匯總' then
begin
with data.xshz do
begin
close;
Sql.Clear;
SQL.Add('select ywy,sum(qcye) as qc ,sum(ysje) as xsje,sum(je) as skje, '+
' sum(qcye)+sum(ysje)-sum(je) as qmye, '+
' sum(qcfpje) as qcfp, sum(jshj) as fpje,'+
' sum(qcfpje)+sum(ysje)-sum(jshj) as qmfpye '+
' from( '+
' select ywy,qc,0 as xsje,0 as skje,0 as qmye,qcfp,0 as fpje,0 as qmfpye '+
' from yskqcye'+
' union all '+
' select ywy,0 as qc,xsje,0 as skje,0 as qmye,0 as qcfp,0 as fpje,0 as qmfpye '+
' from xsb '+
' union all '+
' select ywy,0 as qc,0 as xsje,skje,0 as qmye,0 as qcfp,0 as fpje,0 as qmfpye '+
' from skb '+
' union all '+
' select ywy,0 as qc,0 as xsje,0 as skje,0 as qmye,0 as qcfp,fpje,0 as qmfpye '+
' from xxb '+
') org '+
' where cplx=:cplx and ssyf=:ssyf '+
' group by ywy ');
ParamByName('cplx').AsString:=comcplx.Text;
ParamByName('ssyf').AsString:=comssyf.Text;
open;
end;
end;
end.
uj5u.com熱心網友回復：
我試過，使用這樣的ＳＱＬ語法就要出錯select from (select from union all select from)T
難道paradox資料庫不一樣的?
uj5u.com熱心網友回復：
如果Paradox不支持select from (select from union all select from)T那你把中間那段創建一個臨時表吧
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/111612.html
標籤：數據庫相關