我試圖在 7:00 到 10:00 之間獲得銷售。我是 SQL (Oracle) 的新手，我對這個查詢感到很困惑。到目前為止，我有以下內容，但我得到'不一致的資料型別，預計 TIME，
SELECT s.loc_code, SUM (sales_at_net_retail_price) AS "Sales"
FROM dw_star.dw_fact_sales_line s
INNER JOIN dw_star.dw_dim_transactions tr
ON s.TRANS_SRC_ID = tr.TRANS_SRC_ID
WHERE s.calendar_date = TRUNC (SYSDATE) - 1
AND s.loc_code = 2020
AND (CAST (trans_time AS TIME) >= '07:00:00')
AND (CAST (trans_time AS TIME) <= '10:00:00')
GROUP BY s.loc_code
trans_time列的格式如下：6/2/2022 9:03:45 pm
你能幫我解決這個問題嗎？
uj5u.com熱心網友回復：
您正在將時間值與字串值進行比較。您可以使用 EXTRACT 函式從該列中獲取小時，然后比較該值。
SELECT s.loc_code, SUM (sales_at_net_retail_price) AS "Sales"
FROM dw_star.dw_fact_sales_line s
INNER JOIN dw_star.dw_dim_transactions tr
ON s.TRANS_SRC_ID = tr.TRANS_SRC_ID
WHERE s.calendar_date = TRUNC (SYSDATE) - 1
AND s.loc_code = 2020
AND TO_CHAR(trans_time, 'HH') >= '07'
AND TO_CHAR(trans_time, 'HH') <= '10'
GROUP BY s.loc_code
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/488345.html
上一篇：檢索僅具有特定列值的表的行