PHP專案中如何用PDO查詢臨時表？

Young_Tao發表於2020-09-03
PHP

SQL1

CREATE TEMPORARY TABLE r_js SELECT
                    b.agenname30 AS agenname,
                    fhsname30 AS fhsname,
                    b.loc_id,
                    b.locname50 AS locname,
                    a.customter,
                    SPACE( 100 ) AS js_desc,
                    SPACE( 20 ) AS js_date1,
                    SPACE( 20 ) AS js_date2,
                    SPACE( 20 ) AS js_amt,
                    SPACE( 20 ) AS js_rate 
                FROM
                    v_saleonline a,
                    v_locat b 
                WHERE
                    a.loc_id = b.loc_id 
                    AND a.inv_date BETWEEN '2020-08-01' 
                    AND '2020-08-31';

SQL2

UPDATE r_js a,
activitygen c,
activityitem d,
activityloc e 
SET a.js_desc = CONCAT( c.id, c.act_desc ),
a.js_date1 = c.act_date1,
a.js_date2 = c.act_date2,
a.js_amt = d.jsprice 
WHERE
    a.inv_date BETWEEN c.act_date1 
    AND c.act_date2 
    AND a.goodssn = d.goodssn 
    AND a.loc_id = e.loc_id 
    AND c.id = d.id 
    AND c.id = e.id;

SQL3

UPDATE r_js a,
activitysettlement c,
itemgen d,
season e 
SET a.js_desc = CONCAT( c.yea_id, e.name20 ),
a.js_date1 = c.sett_date1,
a.js_date2 = c.sett_date2,
a.js_amt = FLOOR( d.price * c.sett_rate ),
a.js_rate = CONCAT( d.price, '  X  ', c.sett_rate ) 
WHERE
    a.inv_date BETWEEN c.sett_date1 
    AND c.sett_date2 
    AND a.goodssn = d.item 
    AND c.yea_id = d.yea_id 
    AND c.sea_id = d.sea_id 
    AND d.sea_id = e.sea_id 
    AND TRIM( a.js_desc ) = '';

SQL4

UPDATE r_js a,
itemgen d 
SET a.js_desc = '銷售價 X 0.894',
a.js_amt = FLOOR( a.salcost / 0.894 ) 
WHERE
    TRIM( a.js_desc ) = '' 
    AND a.goodssn = d.item;

SQL5

SELECT
    * 
FROM
    r_js 
ORDER BY
    statusdesc,
    agenname,
    js_desc,
    inv_date,
    loc_id;

上面的幾條SQL直接用資料庫查詢是可以查出結果的，但是在PHP專案中用PDO每條SQL都執行一次遍，實際上只有第一條能執行成功，後面四條都執行失敗。然後我嘗試了只執行第一條SQL,然後執行第五條SQL，仍然是沒有結果的。這是為什麼呢？
PHP專案中，可以生成臨時表，並更新和查詢臨時表的資料嗎？

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章