我在ACCESS中建立了一个查询,双击运行时数据显示均正常,
而我在delphi 中用ADOquery 直接打开这个查询时,有的字段内无值,
大侠们有没有遇见到这个问题啊,请指教!
以下是查询内的内容。SELECT a.qh, a.schoolid, a.jsdm, a.jsxm, a.jsnameid,j.areaname,j.schoolname,j.xl,j.zc,j.csny, iif(a.gx1X1A is null,0,a.gx1x1a) as gx1x1a, iif(a.gx1X1B is null,0,a.gx1x1b) as gx1x1b,
iif(a.gx1X1C is null,0,a.gx1X1C) as gx1X1C, iif(a.gx1X1D is null, 0,a.gx1x1d) as gx1x1d,
iif(a.gx1X1ZS is null,0,a.gx1x1zs) as gx1x1zs,
iif(gx1x1zs<>0,round(gx1x1a/gx1x1zs,4)*100 ,0) as gx1x1MYL,
iif(gx1x1zs<>0,round(gx1x1d/gx1x1zs,4)*100 ,0) as gx1x1BMYL, iif(b.gx1X2A is null,0,b.gx1x2A) as gx1x2a, iif(b.gx1X2B is null,0,b.gx1x2b) as gx1x2b,
iif(b.gx1X2C is null,0,b.gx1x2c) as gx1x2c, iif(b.gx1X2D is null,0,b.gx1x2D) as gx1x2d,
iif(b.gx1x2zs is null,0,b.gx1x2zs) as gx1x2zs, iif(gx1x2zs<>0,round(gx1x2a/gx1x2zs,4)*100,0) as gx1x2MYL,
iif(gx1x2zs<>0,round(gx1x2d/gx1x2zs,4)*100,0) as gx1x2BMYL,iif(c.cpgx1ps is null,0,c.cpgx1ps) as cpgx1ps,
iif((gx1x1zs+gx1x2zs)<>0,round((gx1x1a+gx1x2a)/(gx1x1zs+gx1x2zs),4)*100,0) as gx1PGMYL,
iif((gx1x1zs+gx1x2zs)<>0,round((gx1x1d+gx1x2d)/(gx1x1zs+gx1x2zs),4)*100,0) as gx1PGBMYL,
iif(D.gx2x1A is null,0,D.gx2x1A) as gx2x1a, iif(D.gx2X1B is null,0,D.gx2X1B) as gx2x1b,
iif(D.gx2X1C is null,0,d.gx2X1C) as gx2x1c, iif(D.gx2X1D is null,0,D.gx2X1D) as gx2x1d,
iif(D.gx2X1ZS is null,0,D.gx2X1ZS) as gx2x1zs,
iif(gx2x1zs<>0,round(gx2x1a/gx2x1zs,4)*100,0) as gx2x1MYL,
iif(gx2x1zs<>0,round(gx2x1d/gx2x1zs,4)*100,0) as gx2x1BMYL, iif(e.gx2x2A is null,0,e.gx2x2A) as gx2x2a, iif(e.gx2X2B is null,0,e.gx2X2B) as gx2x2b,
iif(e.gx2X2C is null,0,e.gx2X2C) as gx2x2c, iif(e.gx2X2D is null,0,e.gx2X2D) as gx2x2d,
iif(e.gx2X2ZS is null,0,e.gx2X2ZS) as gx2x2zs,
iif(gx2x2zs<>0,round(gx2x2a/gx2x2zs,4)*100,0) as gx2x2MYL,
iif(gx2x2zs<>0,round(gx2x2d/gx2x2zs,4)*100,0) as gx2x2BMYL,
iif(f.cpgx2ps is null,0,f.cpgx2ps) as cpgx2ps,
iif((gx2x1zs+gx2x2zs)<>0,round((gx2x1a+gx2x2a)/(gx2x1zs+gx2x2zs),4)*100,0) as gx2PGMYL,
iif((gx2x1zs+gx2x2zs)<>0,round((gx2x1d+gx2x2d)/(gx2x1zs+gx2x2zs),4)*100,0) as gx2PGBMYL, iif(G.gx3x1A is null,0,G.gx3x1A) as gx3x1a, iif(G.gx3X1B is null,0,G.gx3X1B) as gx3x1b,
iif(G.gx3X1C is null,0,G.gx3X1C) as gx3x1c, iif(G.gx3X1D is null,0,G.gx3X1D) as gx3x1d,
iif(G.gx3X1ZS is null,0,G.gx3X1ZS) as gx3x1zs,
iif(gx3x1zs<>0,round(gx3x1a/gx3x1zs,4)*100,0) as gx3x1MYL,
iif(gx3x1zs<>0,round(gx3x1d/gx3x1zs,4)*100,0) as gx3x1BMYL,
iif(H.gx3x2A is null,0,H.gx3x2A) as gx3x2a, iif(H.gx3X2B is null,0,H.gx3X2B) as gx3x2b,
iif(H.gx3X2C is null,0,H.gx3X2C) as gx3x2c, iif(H.gx3X2D is null,0,H.gx3X2D) as gx3x2d,
iif(H.gx3X2ZS is null,0,H.gx3X2ZS) as gx3x2zs,
iif(gx3x2zs<>0,round(gx3x2a/gx3x2zs,4)*100,0) as gx3x2MYL,
iif(gx3x2zs<>0,round(gx3x2d/gx3x2zs,4)*100,0) as gx3x2BMYL,
iif(i.cpgx3ps is null,0,i.cpgx3ps) as cpgx3ps,
iif((gx3x1zs+gx3x2zs)<>0,round((gx3x1a+gx3x2a)/(gx3x1zs+gx3x2zs),4)*100,0) as gx3PGMYL,
iif((gx3x1zs+gx3x2zs)<>0,round((gx3x1d+gx3x2d)/(gx3x1zs+gx3x2zs),4)*100,0) as gx3PGBMYL, round((gx1x1a+gx1x2a+gx2x1a+gx2x2a+gx3x1a+gx3x2a)/(gx1x1zs+gx1x2zs+gx2x1zs+gx2x2zs+gx3x1zs+gx3x2zs),4)*100 as ZMYL,
round((gx1x1d+gx1x2d+gx2x1d+gx2x2d+gx3x1d+gx3x2d)/(gx1x1zs+gx1x2zs+gx2x1zs+gx2x2zs+gx3x1zs+gx3x2zs),4)*100 as ZBMYL //这两个字段在delphi中没有值,而用ACCESS直接打开数据正常!!!!
FROM (SELECT cjb.QH, cjb.schoolID, cjb.jsxm, cjb.jsdm, cjb.jsnameID, Sum(cjb.a1) AS gx1X1A, Sum(cjb.b1) AS gx1X1B, Sum(cjb.c1) AS gx1X1C, Sum(cjb.d1) AS gx1X1D, IIf(Sum([a1]) Is Null,0,Sum([a1]))+IIf(Sum([b1]) Is Null,0,Sum([b1]))+IIf(Sum([c1]) Is Null,0,Sum([c1]))+IIf(Sum([d1]) Is Null,0,Sum([d1])) AS gx1X1ZS
FROM cjb
where cjb.CPDX=1
GROUP BY cjb.QH, cjb.schoolID, cjb.jsxm, cjb.jsdm, cjb.jsnameID ) AS a, (SELECT cjb.jsnameID,Sum(cjb.a2) AS gx1X2A, Sum(cjb.b2) AS gx1X2B, Sum(cjb.c2) AS gx1X2C, Sum(cjb.d2) AS gx1X2D, IIf(Sum([a2]) Is Null,0,Sum([a2]))+IIf(Sum([b2]) Is Null,0,Sum([b2]))+IIf(Sum([c2]) Is Null,0,Sum([c2]))+IIf(Sum([d2]) Is Null,0,Sum([d2])) AS gx1X2ZS
FROM cjb
where cjb.CPDX=1
GROUP BY cjb.jsnameID ) AS b,A left join B ON a.jsnameid=b.jsnameid,(select jsnameID,count(*) as CPGX1PS from cjb where CPDX=1 group by jsnameID ) as C,A left join C ON a.jsnameid=C.jsnameid,(SELECT cjb.jsnameID, Sum(cjb.a1) AS gx2X1A, Sum(cjb.b1) AS gx2X1B, Sum(cjb.c1) AS gx2X1C, Sum(cjb.d1) AS gx2X1D, IIf(Sum([a1]) Is Null,0,Sum([a1]))+IIf(Sum([b1]) Is Null,0,Sum([b1]))+IIf(Sum([c1]) Is Null,0,Sum([c1]))+IIf(Sum([d1]) Is Null,0,Sum([d1])) AS gx2X1ZS
FROM cjb
where cjb.CPDX=2
GROUP BY cjb.jsnameID ) AS D, A left join D ON a.jsnameid=d.jsnameid,(SELECT cjb.jsnameID,Sum(cjb.a2) AS gx2X2A, Sum(cjb.b2) AS gx2X2B, Sum(cjb.c2) AS gx2X2C, Sum(cjb.d2) AS gx2X2D, IIf(Sum([a2]) Is Null,0,Sum([a2]))+IIf(Sum([b2]) Is Null,0,Sum([b2]))+IIf(Sum([c2]) Is Null,0,Sum([c2]))+IIf(Sum([d2]) Is Null,0,Sum([d2])) AS gx2X2ZS
FROM cjb
where cjb.CPDX=2
GROUP BY cjb.jsnameID ) AS E,A left join E on a.jsnameid=e.jsnameid,(select jsnameID,count(*) as CPGX2PS from cjb where CPDX=2 group by jsnameID) as F, A left join F on a.jsnameid=f.jsnameid,(SELECT cjb.jsnameID, Sum(cjb.a1) AS gx3X1A, Sum(cjb.b1) AS gx3X1B, Sum(cjb.c1) AS gx3X1C, Sum(cjb.d1) AS gx3X1D, IIf(Sum([a1]) Is Null,0,Sum([a1]))+IIf(Sum([b1]) Is Null,0,Sum([b1]))+IIf(Sum([c1]) Is Null,0,Sum([c1]))+IIf(Sum([d1]) Is Null,0,Sum([d1])) AS gx3X1ZS
FROM cjb
where cjb.CPDX=3
GROUP BY cjb.jsnameID ) AS G, A left join G on a.jsnameid=G.jsnameid,(SELECT cjb.jsnameID,Sum(cjb.a2) AS gx3X2A, Sum(cjb.b2) AS gx3X2B, Sum(cjb.c2) AS gx3X2C, Sum(cjb.d2) AS gx3X2D, IIf(Sum([a2]) Is Null,0,Sum([a2]))+IIf(Sum([b2]) Is Null,0,Sum([b2]))+IIf(Sum([c2]) Is Null,0,Sum([c2]))+IIf(Sum([d2]) Is Null,0,Sum([d2])) AS gx3X2ZS
FROM cjb
where cjb.CPDX=3
GROUP BY cjb.jsnameID ) AS H,A left join H on a.jsnameid=h.jsnameid,(select jsnameID,count(*) as CPGX3PS from cjb where CPDX=3 group by jsnameID) as I, A left join i on a.jsnameid=i.jsnameid,(select * from xmk) as j,A left join j on a.jsnameid=j.jsnameid
而我在delphi 中用ADOquery 直接打开这个查询时,有的字段内无值,
大侠们有没有遇见到这个问题啊,请指教!
以下是查询内的内容。SELECT a.qh, a.schoolid, a.jsdm, a.jsxm, a.jsnameid,j.areaname,j.schoolname,j.xl,j.zc,j.csny, iif(a.gx1X1A is null,0,a.gx1x1a) as gx1x1a, iif(a.gx1X1B is null,0,a.gx1x1b) as gx1x1b,
iif(a.gx1X1C is null,0,a.gx1X1C) as gx1X1C, iif(a.gx1X1D is null, 0,a.gx1x1d) as gx1x1d,
iif(a.gx1X1ZS is null,0,a.gx1x1zs) as gx1x1zs,
iif(gx1x1zs<>0,round(gx1x1a/gx1x1zs,4)*100 ,0) as gx1x1MYL,
iif(gx1x1zs<>0,round(gx1x1d/gx1x1zs,4)*100 ,0) as gx1x1BMYL, iif(b.gx1X2A is null,0,b.gx1x2A) as gx1x2a, iif(b.gx1X2B is null,0,b.gx1x2b) as gx1x2b,
iif(b.gx1X2C is null,0,b.gx1x2c) as gx1x2c, iif(b.gx1X2D is null,0,b.gx1x2D) as gx1x2d,
iif(b.gx1x2zs is null,0,b.gx1x2zs) as gx1x2zs, iif(gx1x2zs<>0,round(gx1x2a/gx1x2zs,4)*100,0) as gx1x2MYL,
iif(gx1x2zs<>0,round(gx1x2d/gx1x2zs,4)*100,0) as gx1x2BMYL,iif(c.cpgx1ps is null,0,c.cpgx1ps) as cpgx1ps,
iif((gx1x1zs+gx1x2zs)<>0,round((gx1x1a+gx1x2a)/(gx1x1zs+gx1x2zs),4)*100,0) as gx1PGMYL,
iif((gx1x1zs+gx1x2zs)<>0,round((gx1x1d+gx1x2d)/(gx1x1zs+gx1x2zs),4)*100,0) as gx1PGBMYL,
iif(D.gx2x1A is null,0,D.gx2x1A) as gx2x1a, iif(D.gx2X1B is null,0,D.gx2X1B) as gx2x1b,
iif(D.gx2X1C is null,0,d.gx2X1C) as gx2x1c, iif(D.gx2X1D is null,0,D.gx2X1D) as gx2x1d,
iif(D.gx2X1ZS is null,0,D.gx2X1ZS) as gx2x1zs,
iif(gx2x1zs<>0,round(gx2x1a/gx2x1zs,4)*100,0) as gx2x1MYL,
iif(gx2x1zs<>0,round(gx2x1d/gx2x1zs,4)*100,0) as gx2x1BMYL, iif(e.gx2x2A is null,0,e.gx2x2A) as gx2x2a, iif(e.gx2X2B is null,0,e.gx2X2B) as gx2x2b,
iif(e.gx2X2C is null,0,e.gx2X2C) as gx2x2c, iif(e.gx2X2D is null,0,e.gx2X2D) as gx2x2d,
iif(e.gx2X2ZS is null,0,e.gx2X2ZS) as gx2x2zs,
iif(gx2x2zs<>0,round(gx2x2a/gx2x2zs,4)*100,0) as gx2x2MYL,
iif(gx2x2zs<>0,round(gx2x2d/gx2x2zs,4)*100,0) as gx2x2BMYL,
iif(f.cpgx2ps is null,0,f.cpgx2ps) as cpgx2ps,
iif((gx2x1zs+gx2x2zs)<>0,round((gx2x1a+gx2x2a)/(gx2x1zs+gx2x2zs),4)*100,0) as gx2PGMYL,
iif((gx2x1zs+gx2x2zs)<>0,round((gx2x1d+gx2x2d)/(gx2x1zs+gx2x2zs),4)*100,0) as gx2PGBMYL, iif(G.gx3x1A is null,0,G.gx3x1A) as gx3x1a, iif(G.gx3X1B is null,0,G.gx3X1B) as gx3x1b,
iif(G.gx3X1C is null,0,G.gx3X1C) as gx3x1c, iif(G.gx3X1D is null,0,G.gx3X1D) as gx3x1d,
iif(G.gx3X1ZS is null,0,G.gx3X1ZS) as gx3x1zs,
iif(gx3x1zs<>0,round(gx3x1a/gx3x1zs,4)*100,0) as gx3x1MYL,
iif(gx3x1zs<>0,round(gx3x1d/gx3x1zs,4)*100,0) as gx3x1BMYL,
iif(H.gx3x2A is null,0,H.gx3x2A) as gx3x2a, iif(H.gx3X2B is null,0,H.gx3X2B) as gx3x2b,
iif(H.gx3X2C is null,0,H.gx3X2C) as gx3x2c, iif(H.gx3X2D is null,0,H.gx3X2D) as gx3x2d,
iif(H.gx3X2ZS is null,0,H.gx3X2ZS) as gx3x2zs,
iif(gx3x2zs<>0,round(gx3x2a/gx3x2zs,4)*100,0) as gx3x2MYL,
iif(gx3x2zs<>0,round(gx3x2d/gx3x2zs,4)*100,0) as gx3x2BMYL,
iif(i.cpgx3ps is null,0,i.cpgx3ps) as cpgx3ps,
iif((gx3x1zs+gx3x2zs)<>0,round((gx3x1a+gx3x2a)/(gx3x1zs+gx3x2zs),4)*100,0) as gx3PGMYL,
iif((gx3x1zs+gx3x2zs)<>0,round((gx3x1d+gx3x2d)/(gx3x1zs+gx3x2zs),4)*100,0) as gx3PGBMYL, round((gx1x1a+gx1x2a+gx2x1a+gx2x2a+gx3x1a+gx3x2a)/(gx1x1zs+gx1x2zs+gx2x1zs+gx2x2zs+gx3x1zs+gx3x2zs),4)*100 as ZMYL,
round((gx1x1d+gx1x2d+gx2x1d+gx2x2d+gx3x1d+gx3x2d)/(gx1x1zs+gx1x2zs+gx2x1zs+gx2x2zs+gx3x1zs+gx3x2zs),4)*100 as ZBMYL //这两个字段在delphi中没有值,而用ACCESS直接打开数据正常!!!!
FROM (SELECT cjb.QH, cjb.schoolID, cjb.jsxm, cjb.jsdm, cjb.jsnameID, Sum(cjb.a1) AS gx1X1A, Sum(cjb.b1) AS gx1X1B, Sum(cjb.c1) AS gx1X1C, Sum(cjb.d1) AS gx1X1D, IIf(Sum([a1]) Is Null,0,Sum([a1]))+IIf(Sum([b1]) Is Null,0,Sum([b1]))+IIf(Sum([c1]) Is Null,0,Sum([c1]))+IIf(Sum([d1]) Is Null,0,Sum([d1])) AS gx1X1ZS
FROM cjb
where cjb.CPDX=1
GROUP BY cjb.QH, cjb.schoolID, cjb.jsxm, cjb.jsdm, cjb.jsnameID ) AS a, (SELECT cjb.jsnameID,Sum(cjb.a2) AS gx1X2A, Sum(cjb.b2) AS gx1X2B, Sum(cjb.c2) AS gx1X2C, Sum(cjb.d2) AS gx1X2D, IIf(Sum([a2]) Is Null,0,Sum([a2]))+IIf(Sum([b2]) Is Null,0,Sum([b2]))+IIf(Sum([c2]) Is Null,0,Sum([c2]))+IIf(Sum([d2]) Is Null,0,Sum([d2])) AS gx1X2ZS
FROM cjb
where cjb.CPDX=1
GROUP BY cjb.jsnameID ) AS b,A left join B ON a.jsnameid=b.jsnameid,(select jsnameID,count(*) as CPGX1PS from cjb where CPDX=1 group by jsnameID ) as C,A left join C ON a.jsnameid=C.jsnameid,(SELECT cjb.jsnameID, Sum(cjb.a1) AS gx2X1A, Sum(cjb.b1) AS gx2X1B, Sum(cjb.c1) AS gx2X1C, Sum(cjb.d1) AS gx2X1D, IIf(Sum([a1]) Is Null,0,Sum([a1]))+IIf(Sum([b1]) Is Null,0,Sum([b1]))+IIf(Sum([c1]) Is Null,0,Sum([c1]))+IIf(Sum([d1]) Is Null,0,Sum([d1])) AS gx2X1ZS
FROM cjb
where cjb.CPDX=2
GROUP BY cjb.jsnameID ) AS D, A left join D ON a.jsnameid=d.jsnameid,(SELECT cjb.jsnameID,Sum(cjb.a2) AS gx2X2A, Sum(cjb.b2) AS gx2X2B, Sum(cjb.c2) AS gx2X2C, Sum(cjb.d2) AS gx2X2D, IIf(Sum([a2]) Is Null,0,Sum([a2]))+IIf(Sum([b2]) Is Null,0,Sum([b2]))+IIf(Sum([c2]) Is Null,0,Sum([c2]))+IIf(Sum([d2]) Is Null,0,Sum([d2])) AS gx2X2ZS
FROM cjb
where cjb.CPDX=2
GROUP BY cjb.jsnameID ) AS E,A left join E on a.jsnameid=e.jsnameid,(select jsnameID,count(*) as CPGX2PS from cjb where CPDX=2 group by jsnameID) as F, A left join F on a.jsnameid=f.jsnameid,(SELECT cjb.jsnameID, Sum(cjb.a1) AS gx3X1A, Sum(cjb.b1) AS gx3X1B, Sum(cjb.c1) AS gx3X1C, Sum(cjb.d1) AS gx3X1D, IIf(Sum([a1]) Is Null,0,Sum([a1]))+IIf(Sum([b1]) Is Null,0,Sum([b1]))+IIf(Sum([c1]) Is Null,0,Sum([c1]))+IIf(Sum([d1]) Is Null,0,Sum([d1])) AS gx3X1ZS
FROM cjb
where cjb.CPDX=3
GROUP BY cjb.jsnameID ) AS G, A left join G on a.jsnameid=G.jsnameid,(SELECT cjb.jsnameID,Sum(cjb.a2) AS gx3X2A, Sum(cjb.b2) AS gx3X2B, Sum(cjb.c2) AS gx3X2C, Sum(cjb.d2) AS gx3X2D, IIf(Sum([a2]) Is Null,0,Sum([a2]))+IIf(Sum([b2]) Is Null,0,Sum([b2]))+IIf(Sum([c2]) Is Null,0,Sum([c2]))+IIf(Sum([d2]) Is Null,0,Sum([d2])) AS gx3X2ZS
FROM cjb
where cjb.CPDX=3
GROUP BY cjb.jsnameID ) AS H,A left join H on a.jsnameid=h.jsnameid,(select jsnameID,count(*) as CPGX3PS from cjb where CPDX=3 group by jsnameID) as I, A left join i on a.jsnameid=i.jsnameid,(select * from xmk) as j,A left join j on a.jsnameid=j.jsnameid
解决方案 »
- 怎样获取TREEVIEW拖放后的节点
- 如何在设计期将某个控件给隐藏掉?
- 以下程序的编译错误我解决不了了!!请高手了(送分!!!)
- ADO中的记录集添加过滤条件后不能添加记录
- 用nmhttp或idhttp自动在留言板发帖的软件
- 有没有人用delphi开发mysql--你用什么访问mysql数据库?
- 求Delphi消息对话框(Message)实例
- 怎样实现用数组生成和管理控件,可根据文本自动增长的文本框?
- 如何改变TStringGrid里的文本的对齐方式(居中)!
- 我从何处下载QuickReport 3.5.1 for D5,D6
- Multiple-step OLE DB operation generated errors 什么意思 ?、
- 怎样得到vc里tab控件里任意text文本框或者命令按钮的 句柄啊 我用spy++ 得到的只要放到tab里面的任何文本框都是相同的值 #32770(对话框
比如year之类的,在access里运行正常,程序就会出错