select gy_xmgb.xmmc as gbxm ,
gy_xmgb.gbxm as gbxm1,
sum(t.ylje) as ylje
from
( SELECT gy_xmgb.gbxm,
0.00 AS YLJE
FROM gy_xmgb
WHERE gy_xmgb.bbbh = 005203UNION ALL SELECT gy_xmgb.gbxm,
MS_SFMX.ZJJE as ylje
FROM MS_SFMX,
MS_MZXX,
gy_xmgb,
ms_hzrb
WHERE MS_SFMX.FPHM = MS_MZXX.FPHM and
ms_hzrb.jzRQ >= :adt_start and
ms_hzrb.jzRQ <= :adt_end and
(ms_hzrb.jzwz = :al_jzwz or 0 = :al_jzwz) and
ms_hzrb.czgh = ms_mzxx.czgh and
ms_hzrb.jzRQ = ms_mzxx.jzrq and
(MS_MZXX.brxz = :al_brxz or 0 = :al_brxz) and
gy_xmgb.sfxm = MS_SFMX.SFXM and
gy_xmgb.bbbh = 005203 and
(ms_mzxx.tbbz = :al_tbbz or -1 = :al_tbbz)
UNION ALL SELECT gy_xmgb.gbxm,
- MS_SFMX.ZJJE as ylje
FROM MS_SFMX,
MS_MZXX,
gy_xmgb,
ms_hzrb,
ms_zffp
WHERE MS_SFMX.FPHM = MS_MZXX.FPHM and
MS_MZXX.FPHM = MS_zffp.FPHM and
ms_hzrb.jzRQ >= :adt_start and
ms_hzrb.jzRQ <= :adt_end and
(ms_hzrb.jzwz = :al_jzwz or 0 = :al_jzwz) and
ms_hzrb.czgh = MS_zffp.czgh and
ms_hzrb.jzRQ = MS_zffp.jzrq and
(MS_MZXX.brxz = :al_brxz or 0 = :al_brxz) and
gy_xmgb.sfxm = MS_SFMX.SFXM and
gy_xmgb.bbbh = 005203 and
(ms_mzxx.tbbz = :al_tbbz or -1 = :al_tbbz)
) as t,
gy_xmgb
where gy_xmgb.bbbh = 005203 and
gy_xmgb.gbxm = gy_xmgb.sfxm and
t.gbxm = gy_xmgb.gbxm group by gy_xmgb.gbxm,
gy_xmgb.xmmc
order by gy_xmgb.gbxm上面语句中的参数须每一个都指定吗?
我在下面填完后提示参数不够:
procedure TForm1.Button1Click(Sender: TObject);
begin
adoquery2.Close;
with adoquery2.Parameters do
begin
ParamByName('adt_start').Value:=DateTimeP1.date;
ParamByName('adt_end').Value:=DateTimeP2.date;
ParamByName('al_jzwz').Value:=0;
ParamByName('al_brxz').Value:=0;
ParamByName('al_tbbz').Value:=-1;
end;
adoquery2.Open;
end;
gy_xmgb.gbxm as gbxm1,
sum(t.ylje) as ylje
from
( SELECT gy_xmgb.gbxm,
0.00 AS YLJE
FROM gy_xmgb
WHERE gy_xmgb.bbbh = 005203UNION ALL SELECT gy_xmgb.gbxm,
MS_SFMX.ZJJE as ylje
FROM MS_SFMX,
MS_MZXX,
gy_xmgb,
ms_hzrb
WHERE MS_SFMX.FPHM = MS_MZXX.FPHM and
ms_hzrb.jzRQ >= :adt_start and
ms_hzrb.jzRQ <= :adt_end and
(ms_hzrb.jzwz = :al_jzwz or 0 = :al_jzwz) and
ms_hzrb.czgh = ms_mzxx.czgh and
ms_hzrb.jzRQ = ms_mzxx.jzrq and
(MS_MZXX.brxz = :al_brxz or 0 = :al_brxz) and
gy_xmgb.sfxm = MS_SFMX.SFXM and
gy_xmgb.bbbh = 005203 and
(ms_mzxx.tbbz = :al_tbbz or -1 = :al_tbbz)
UNION ALL SELECT gy_xmgb.gbxm,
- MS_SFMX.ZJJE as ylje
FROM MS_SFMX,
MS_MZXX,
gy_xmgb,
ms_hzrb,
ms_zffp
WHERE MS_SFMX.FPHM = MS_MZXX.FPHM and
MS_MZXX.FPHM = MS_zffp.FPHM and
ms_hzrb.jzRQ >= :adt_start and
ms_hzrb.jzRQ <= :adt_end and
(ms_hzrb.jzwz = :al_jzwz or 0 = :al_jzwz) and
ms_hzrb.czgh = MS_zffp.czgh and
ms_hzrb.jzRQ = MS_zffp.jzrq and
(MS_MZXX.brxz = :al_brxz or 0 = :al_brxz) and
gy_xmgb.sfxm = MS_SFMX.SFXM and
gy_xmgb.bbbh = 005203 and
(ms_mzxx.tbbz = :al_tbbz or -1 = :al_tbbz)
) as t,
gy_xmgb
where gy_xmgb.bbbh = 005203 and
gy_xmgb.gbxm = gy_xmgb.sfxm and
t.gbxm = gy_xmgb.gbxm group by gy_xmgb.gbxm,
gy_xmgb.xmmc
order by gy_xmgb.gbxm上面语句中的参数须每一个都指定吗?
我在下面填完后提示参数不够:
procedure TForm1.Button1Click(Sender: TObject);
begin
adoquery2.Close;
with adoquery2.Parameters do
begin
ParamByName('adt_start').Value:=DateTimeP1.date;
ParamByName('adt_end').Value:=DateTimeP2.date;
ParamByName('al_jzwz').Value:=0;
ParamByName('al_brxz').Value:=0;
ParamByName('al_tbbz').Value:=-1;
end;
adoquery2.Open;
end;
建议以上SQL语句用储存过程编写,在程序中调用储存过程,传参数。、
[email protected]
当然,本人也认为像这样的SQL最好还是使用存储过程
ParamByName(1).Value:=DateTimeP2.date;
。全部指定
有多个相同的参数名存在时只需指定一个值
还应缺少对象参数
@PARA
还是比较好用的哦