QL语句1、我有三个表(ACCESS,ADO连接),如下:yf(月份表)
字段:yf
记录:1到12sr(收入表)
字段:rq(日期),srje(收入金额)
zc(支出表)
字段:rq(日期),zcje(支出金额)现在我想做一个查询,根据sr表每月的收入金额,zc表中每月的支出金额,结余金额(收入金额-支出金额),分别对应于各自的月份,如果为0,也要显示为0,查询后的结果如下:月份 收入金额 支出金额 结余金额
1 1000 500 500
2 0 0 0
..
12 0 0 0
我写的SQL语句如下:
with ADOQuery1 do
begin
sql.Clear;
sql.Add('select yf.yf as 月份,'+
'sum(iif(sr.srje=0,0,sr.srje)) as 收入金额,'+
'sum(iif(zc.zcje=0,0,zc.zcje)) as 支出金额,'+
'(收入金额-支出金额) as 结余金额 '+
'from yf LEFT OUTER JOIN zc on yf.yf=MONTH(zc.rq) '+
'LEFT OUTER JOIN sr on yf.yf=MONTH(sr.rq) '+
'group by yf.yf');
open;
end;function IIF(b:boolean;s1,s2:variant):variant;
begin
if b then
IIF:=s1
else
IIF:=s2;
end;却提示:“语法错误(操作符丢失)在查询表达式‘yf.yf=month(zc.rq) left outer join sr on yf.yf=month(sr.rq)’中”,请各位帮忙解决。2、我把查询后的结果放到DBGrid中,怎样控制显示格式,即收入金额、支出金额、结余金额的小数点后边为2位。不用字段对象,在程序中控制。
字段:yf
记录:1到12sr(收入表)
字段:rq(日期),srje(收入金额)
zc(支出表)
字段:rq(日期),zcje(支出金额)现在我想做一个查询,根据sr表每月的收入金额,zc表中每月的支出金额,结余金额(收入金额-支出金额),分别对应于各自的月份,如果为0,也要显示为0,查询后的结果如下:月份 收入金额 支出金额 结余金额
1 1000 500 500
2 0 0 0
..
12 0 0 0
我写的SQL语句如下:
with ADOQuery1 do
begin
sql.Clear;
sql.Add('select yf.yf as 月份,'+
'sum(iif(sr.srje=0,0,sr.srje)) as 收入金额,'+
'sum(iif(zc.zcje=0,0,zc.zcje)) as 支出金额,'+
'(收入金额-支出金额) as 结余金额 '+
'from yf LEFT OUTER JOIN zc on yf.yf=MONTH(zc.rq) '+
'LEFT OUTER JOIN sr on yf.yf=MONTH(sr.rq) '+
'group by yf.yf');
open;
end;function IIF(b:boolean;s1,s2:variant):variant;
begin
if b then
IIF:=s1
else
IIF:=s2;
end;却提示:“语法错误(操作符丢失)在查询表达式‘yf.yf=month(zc.rq) left outer join sr on yf.yf=month(sr.rq)’中”,请各位帮忙解决。2、我把查询后的结果放到DBGrid中,怎样控制显示格式,即收入金额、支出金额、结余金额的小数点后边为2位。不用字段对象,在程序中控制。
'LEFT OUTER JOIN sr on yf.yf=MONTH(sr.rq) '+
'group by yf.yf');
//access中不要用到SQL的一些函数,不支持的,month(),left outer join;
from yf,
(select month(rq) yf, sum(isnull(zcje, 0)) zcje from zc group by month(rq)) zcb,
(select month(rq) yf, sum(isnull(srje, 0)) srje from sr group by month(rq)) srb
where yf.yf *= zcb.yf
and yf.yf *= srb.yf
order by yf.yf
TO: lianshaohua(永远深爱一个叫“然”的好女孩儿!) 在access中支持month(),left(right) outer join,但不支持完全连接
(SELECT YF.yf AS 月份, IIF(SUM(Srje) IS NULL,0,SUM(Srje)) AS 收入金额 FROM YF LEFT JOIN SR ON YF.Yf=MONTH(SR.RQ) GROUP BY YF.yf) AS S
LEFT JOIN
(SELECT YF.yf AS 月份, IIF(SUM(Zcje) IS NULL,0,SUM(Zcje)) AS 支出金额 FROM YF LEFT JOIN ZC ON YF.Yf=MONTH(ZC.RQ) GROUP BY YF.yf) AS Z
ON S.月份=Z.月份