請幫幫我.. 我有一個如下的查詢: 這是一個表:
我想要顯示在delivery_order/item表中存在但在bst/item表中不存在的商品,根據KODE_BARANG和JUMLAH進行比較。
我有下面的查詢語句,但是如果資料已經很多的話,它仍然很慢(慢的部分是在比較KODE_BARANG的行)。 我想問一下,我的朋友們是否有類似的情況,並且該如何解決? 提前謝謝,希望這裡有人願意回答。
SELECT del.KODE_DO, deli.KODE_BARANG, deli.NAMA_BARANG, deli.JUMLAH, deli.SATUAN, @DITERIMA := COALESCE (( SELECT SUM( JUMLAH ) FROM bst_item WHERE KODE_PENERIMAAN = deli.KODE_DO AND KODE_BARANG = deli.KODE_BARANG #这行使它变慢 AND `STATUS` <> 0),0) AS DITERIMA, COALESCE ( deli.JUMLAH, 0 ) - @DITERIMA AS SISA FROM delivery_order del INNER JOIN delivery_order_item deli ON del.KODE_DO = deli.KODE_DO WHERE DATE(del.TANGGAL) >= :TGL1 AND DATE(del.TANGGAL) <= :TGL2 AND COALESCE ( deli.JUMLAH, 0 ) - COALESCE (( SELECT SUM( JUMLAH ) FROM bst_item WHERE KODE_PENERIMAAN = deli.KODE_DO AND KODE_BARANG = deli.KODE_BARANG #这行使它变慢 AND `STATUS` <> 0),0) > 0
SELECT del.KODE_DO, deli.KODE_BARANG, deli.NAMA_BARANG, deli.JUMLAH, deli.SATUAN, @DITERIMA := COALESCE (( SELECT SUM( JUMLAH ) FROM bst_item WHERE KODE_PENERIMAAN = deli.KODE_DO AND KODE_BARANG = deli.KODE_BARANG #这行使它变慢 AND `STATUS` <> 0),0) AS DITERIMA, COALESCE ( deli.JUMLAH, 0 ) - @DITERIMA AS SISA FROM delivery_order del INNER JOIN delivery_order_item deli ON del.KODE_DO = deli.KODE_DO WHERE DATE(del.TANGGAL) >= :TGL1 AND DATE(del.TANGGAL) <= :TGL2 AND COALESCE ( deli.JUMLAH, 0 ) - COALESCE (( SELECT SUM( JUMLAH ) FROM bst_item WHERE KODE_PENERIMAAN = deli.KODE_DO AND KODE_BARANG = deli.KODE_BARANG #这行使它变慢 AND `STATUS` <> 0),0) > 0
而且不要在函數呼叫中隱藏TANGGAL。 (cf "sargable")