有四个表,分别是CKGL_CKGL,CKGL_RKGL,CKGL_WPKC,GY_WPXX;这四个表之间没有任何外键关联。用如下语句想获得每个物品的入库总数、出库总数、库存总数、和帐面库存数,但是结果不正确,请达人指正!
SELECT sum(CKGL_WPKC.KCSL) AS SJKC,
CKGL_WPXX.WPMC,
CKGL_WPXX.WPGG,
CKGL_WPXX.WPDW,
sum(CKGL_RKGL.JHSL) AS JHSL,
sum(CKGL_CKGL.CKSL) AS CKSL,
sum(CKGL_RKGL.JHSL) - sum(CKGL_CKGL.CKSL) AS ZMKC,
CKGL_WPXX.WPBH
FROM CKGL_WPKC,
CKGL_WPXX,
CKGL_CKGL,
CKGL_RKGL
WHERE ( CKGL_WPKC.WPBH = CKGL_WPXX.WPBH ) and
(( CKGL_RKGL.WPBH = CKGL_WPXX.WPBH ) and
( CKGL_CKGL.WPBH = CKGL_WPXX.WPBH )) and
( CKGL_CKGL.DJZT = 1 ) AND
( CKGL_RKGL.DJZT = 1 )
GROUP BY CKGL_WPXX.WPMC,
CKGL_WPXX.WPGG,
CKGL_WPXX.WPDW,
CKGL_WPXX.WPBH
SELECT sum(CKGL_WPKC.KCSL) AS SJKC,
CKGL_WPXX.WPMC,
CKGL_WPXX.WPGG,
CKGL_WPXX.WPDW,
sum(CKGL_RKGL.JHSL) AS JHSL,
sum(CKGL_CKGL.CKSL) AS CKSL,
sum(CKGL_RKGL.JHSL) - sum(CKGL_CKGL.CKSL) AS ZMKC,
CKGL_WPXX.WPBH
FROM CKGL_WPKC,
CKGL_WPXX,
CKGL_CKGL,
CKGL_RKGL
WHERE ( CKGL_WPKC.WPBH = CKGL_WPXX.WPBH ) and
(( CKGL_RKGL.WPBH = CKGL_WPXX.WPBH ) and
( CKGL_CKGL.WPBH = CKGL_WPXX.WPBH )) and
( CKGL_CKGL.DJZT = 1 ) AND
( CKGL_RKGL.DJZT = 1 )
GROUP BY CKGL_WPXX.WPMC,
CKGL_WPXX.WPGG,
CKGL_WPXX.WPDW,
CKGL_WPXX.WPBH
JHSL是直接用select sum(jhsl),wpbh from ckgl_rkgl where djzt = 1
group by wpbh的3倍
cksl是直接select的4倍,sjkc是直接select的12倍
SJKC JHSL CKSL ZMKC
24 36 24 12分别用select语句直接从对应的表里取出的结果为
SJKC 2
JHSL 12
CKSL 6
ZMKC 6
SELECT sum(CKGL_WPKC.KCSL) AS SJKC,
CKGL_WPXX.WPMC,
CKGL_WPXX.WPGG,
CKGL_WPXX.WPDW,
sum(CKGL_RKGL.JHSL) AS JHSL,
sum(CKGL_CKGL.CKSL) AS CKSL,
sum(CKGL_RKGL.JHSL) - sum(CKGL_CKGL.CKSL) AS ZMKC,
CKGL_WPXX.WPBH
FROM CKGL_WPKC
INNER JOIN
CKGL_WPXX
ON
CKGL_WPKC.WPBH = CKGL_WPXX.WPBH
INNER JOIN
CKGL_CKGL
ON
CKGL_RKGL.WPBH = CKGL_WPXX.WPBH AND CKGL_CKGL.DJZT = 1
INNER JOIN
CKGL_RKGL
ON
CKGL_CKGL.WPBH = CKGL_WPXX.WPBH AND CKGL_RKGL.DJZT = 1
GROUP BY CKGL_WPXX.WPMC,
CKGL_WPXX.WPGG,
CKGL_WPXX.WPDW,
CKGL_WPXX.WPBH
“查询中的前缀‘ckgl_rkgl’与表名或别名不匹配!”