-- TRY IT .. SELECT TF.STAFF_ID,TF.NAME, NVL(SUM(TP.UNIT_PRICE),0) "SUM_MONEY" FROM TABLE_SALE TE, TABLE_PRODUCT TP, TABLE_STAFF TF WHERE TF.STAFF_ID = TE.STAFF_ID(+) AND TE.PRODUCT_ID = TP.PRODUCT_ID AND TO_CHAR(TE.SALE_DATE,'YYYY-MM') = '2008-01' GROUP BY TF.STAFF_ID,TF.NAME ;
select a.NAME,sum(b.NUMBERS*c.UNIT_PRICE ) from TABLE_STAFF a left join table_sale b on a.STAFF_ID=b.staff_id left join TABLE_PRODUCT c on b.PRODUCT_ID =c.PRODUCT_ID ;
正在学习oracle...这个很有用..
把TP.UNIT_PRICE改成TP.UNIT_PRICE*TE.NUMBERS 即可。
SELECT TF.STAFF_ID,TF.NAME, NVL(SUM(TP.UNIT_PRICE*TE.NUMBERS),0) "SUM_MONEY" FROM TABLE_SALE TE, TABLE_PRODUCT TP, TABLE_STAFF TF WHERE TF.STAFF_ID = TE.STAFF_ID(+) AND TE.PRODUCT_ID = TP.PRODUCT_ID AND TO_CHAR(TE.SALE_DATE,'YYYY-MM') = '2008-01' GROUP BY TF.STAFF_ID,TF.NAME ;
SELECT TF.STAFF_ID,TF.NAME,
NVL(SUM(TP.UNIT_PRICE),0) "SUM_MONEY"
FROM TABLE_SALE TE,
TABLE_PRODUCT TP,
TABLE_STAFF TF
WHERE TF.STAFF_ID = TE.STAFF_ID(+)
AND TE.PRODUCT_ID = TP.PRODUCT_ID
AND TO_CHAR(TE.SALE_DATE,'YYYY-MM') = '2008-01'
GROUP BY TF.STAFF_ID,TF.NAME
;
from TABLE_STAFF a left join table_sale b
on a.STAFF_ID=b.staff_id left join TABLE_PRODUCT c
on b.PRODUCT_ID =c.PRODUCT_ID ;
NVL(SUM(TP.UNIT_PRICE*TE.NUMBERS),0) "SUM_MONEY"
FROM TABLE_SALE TE,
TABLE_PRODUCT TP,
TABLE_STAFF TF
WHERE TF.STAFF_ID = TE.STAFF_ID(+)
AND TE.PRODUCT_ID = TP.PRODUCT_ID
AND TO_CHAR(TE.SALE_DATE,'YYYY-MM') = '2008-01'
GROUP BY TF.STAFF_ID,TF.NAME
;