SELECT * FROM
(
SELECT DISTINCT(TRUNC(receipt_dstamp))
FROM inventory
WHERE substr(location_id,1,3) = 'GI-'
ORDER BY 1 ASC
)
WHERE ROWNUM <= 5
輸出： 
大家好，我有這個 subeqery，在這種情況下，我最早的日期在第 1 行，我只想從最后一個（在這種情況下從頂部）檢索第二個日期，這將是 01-SEP-21。我試圖玩 ROWNUM 和 OVER 但沒有任何結果，我得到空白輸出。
謝謝你。
完整查詢：
SELECT TRUNC(receipt_dstamp) as old_putaway_date, COUNT(tag_id) as tag_old_putaway
FROM inventory
WHERE substr(location_id,1,3) = 'GI-'
AND TRUNC(receipt_dstamp) IN (
SELECT * FROM
(
SELECT DISTINCT(TRUNC(receipt_dstamp))
FROM inventory
WHERE substr(location_id,1,3) = 'GI-'
ORDER BY 1 ASC
)
WHERE ROWNUM = 1
)
GROUP BY TRUNC(receipt_dstamp);
uj5u.com熱心網友回復：
您應該能夠將整個查詢簡化為：
SELECT old_putaway_date,
COUNT(tag_id) as tag_old_putaway
FROM (
SELECT TRUNC(receipt_dstamp) as old_putaway_date,
tag_id,
DENSE_RANK() OVER (ORDER BY TRUNC(receipt_dstamp)) AS rnk
FROM inventory
WHERE substr(location_id,1,3) = 'GI-'
)
WHERE rnk = 3
GROUP BY
old_putaway_date;
uj5u.com熱心網友回復：
您可以使用 density_rank() ：
SELECT * FROM (
SELECT L.*,DENSE_RANK()
OVER (PARTITION BY L.TAG_OLD_PUTAWAY ORDER BY L.OLD_PUTAWAY_DATE DESC) RNK
FROM
(
SELECT TRUNC(receipt_dstamp) as old_putaway_date, COUNT(tag_id) as tag_old_putaway
FROM inventory
WHERE substr(location_id,1,3) = 'GI-'
AND TRUNC(receipt_dstamp) IN (
SELECT * FROM
(
SELECT DISTINCT(TRUNC(receipt_dstamp))
FROM inventory
WHERE substr(location_id,1,3) = 'GI-'
ORDER BY 1 ASC
)
WHERE ROWNUM = 1
)
GROUP BY TRUNC(receipt_dstamp)
) L
) WHERE RNK = 2
uj5u.com熱心網友回復：
您使用的是不符合標準的舊 Oracle 語法，因為它依賴于子查詢結果順序。根據定義，（子）查詢結果是無序的資料集，但 Oracle 允許它通過以ROWNUM使用它。
Oracle 現在支持標準 SQLFETCH子句，您應該改用它。
SELECT DISTINCT TRUNC(receipt_dstamp) AS receipt_date
FROM inventory
WHERE SUBSTR(location_id, 1, 3) = 'GI-'
ORDER BY receipt_date
OFFSET 2 ROWS
FETCH NEXT 1 ROW ONLY;
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/359839.html
上一篇：如何查找“主要”主記錄SQL
下一篇：使用每個用戶的最后一個值填充表格