SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
//但这里不允许联接一个常量,非要是一个字段名才行
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
//但这里不允许联接一个常量,非要是一个字段名才行
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
//但这里不允许联接一个常量,非要是一个字段名才行
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
//但这里不允许联接一个常量,非要是一个字段名才行
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
我试过很多遍,在ACCESS中,有试成:
SELECT G.GDSNAME, G.GDSID, G.GDSINID,
G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
AND ( D.STOCKLOCID =:LS_STOCKLOCID ) //但这里不允许联接一个常量,非要是一个字段名才行
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 ) //同上
AND ( D.CUSTOMERID = :LS_CUSTOMERID) //同上
AND ( D.DAY <= :LS_BILLDT) //同上
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;如果按楼上的办法,把 AND ( D.STOCKLOCID =:LS_STOCKLOCID ) 语句写到
WHERE子句中,那么就达不到我的要求了,因为这个字段值是允许取空值的,
放到WHERE子句中,那空值就取不到了。。
其实不只只是PB有SQL 生成器,像SQL SERVER, ACCESS都有这样的工具
问题是,并不是所有的SQL语句都可以用生成器来做的了TO jiezhi(浪子)
你上面写的语句我也执行过,可惜,它会把我需要的记录都过滤掉的
如:我想查的记录在GOODS表中有的,但在STOCKDAY表中可能是没有的
如果按你的语句查的话,因为在STOCKDAY 中并不满足以下条件:
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 ) //同上
AND ( D.CUSTOMERID = :LS_CUSTOMERID) //同上
AND ( D.DAY <= :LS_BILLDT) //同上
所以记录还是会被过滤掉
G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN
(select * from STOCKDAY where
AND ( STOCKLOCID =:LS_STOCKLOCID )
AND ( ACCOUNTID = :LS_ACCOUNTID1 )
AND ( CUSTOMERID = :LS_CUSTOMERID)
AND ( DAY <= :LS_BILLDT)
) D ON ( D.GDSINID = G.GDSINID) WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;