給一個套餐ID 找出它本身和所有相關項目SQL

資料來源: 自己+AI(gemini/chatgpt)幫忙優化

條件:

T(套餐)  m_intitem_no  
TP(產品) m_intparent_item_no=m_intitem_no
TC(配料) m_intparent_item_no=m_intitem_no

自己的SQL:

SELECT * FROM order_content_data WHERE order_no='20251016-0005' AND item_no='1' AND del_flag='N'
UNION
SELECT * FROM order_content_data WHERE order_no='20251016-0005' AND parent_item_no='1' AND del_flag='N'
UNION
SELECT * FROM order_content_data WHERE order_no='20251016-0005' AND del_flag='N' AND parent_item_no IN (SELECT item_no FROM order_content_data WHERE order_no='20251016-0005' AND parent_item_no='1' AND del_flag='N')

gemini的SQL:[上面是SQLITE的SQL語法 我要簡化並提升執行效率]

方案01.

SELECT *
FROM order_content_data
WHERE order_no = '20251016-0005'
  AND del_flag = 'N'
  AND (
    item_no = '1'
    OR parent_item_no = '1'
    OR parent_item_no IN (
      SELECT item_no
      FROM order_content_data
      WHERE order_no = '20251016-0005'
        AND del_flag = 'N'
        AND parent_item_no = '1'
    )
  );

方案02.

SELECT T1.*
FROM order_content_data AS T1
LEFT JOIN order_content_data AS T2
  ON T1.parent_item_no = T2.item_no
  AND T2.order_no = '20251016-0005'
  AND T2.del_flag = 'N'
  AND T2.parent_item_no = '1'
WHERE T1.order_no = '20251016-0005'
  AND T1.del_flag = 'N'
  AND (
    T1.item_no = '1'
    OR T1.parent_item_no = '1'
    OR T2.item_no IS NOT NULL
  );

chatgpt的SQL:[上面是SQLITE的SQL語法 我要簡化並提升執行效率]

WITH RECURSIVE item_tree AS (
  SELECT *
  FROM order_content_data
  WHERE order_no = '20251016-0005'
    AND item_no = '1'
    AND del_flag = 'N'

  UNION ALL

  SELECT c.*
  FROM order_content_data c
  JOIN item_tree p
    ON c.parent_item_no = p.item_no
  WHERE c.order_no = '20251016-0005'
    AND c.del_flag = 'N'
)
SELECT *
FROM item_tree;

心得:誰說AI不好用，重點要有創意+敢嘗試

…

不是最好就拿來用，畢竟能力不足無法駕馭也不好擴充(就像研究所書報是要把不懂得越認真報告，懂得部分隨便講，這樣被授課教授問才不會倒)