oracle 如何从表中仅选择第二个最大日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40210820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select only the second max date from a table
提问by Tony Roczz
I have a table where the delivery date and order number are stored.
我有一张表，其中存储了交货日期和订单号。
Here i was able to get the order with the max delivery date.
在这里，我能够获得最大交货日期的订单。
SELECT DISTINCT D.ORDER_NO
FROM DELIVERY D
WHERE D.CUSTOMER_NO =112 AND D.DELIVERY_DATE = (SELECT MAX(D1.DELIVERY_DATE) FROM DELIVERY D1
WHERE D1.CUSTOMER_NO = 112 );
Here a single customer may have multiple orders.
在这里，一个客户可能有多个订单。
Now what i want is to get only the second max date.
现在我想要的是只获得第二个最大日期。
By using the above query I was able to get the list of data other than the max delivery date by changing the =to <and adding ORDER BYin the subquery.
通过使用上述查询，我能够通过更改=to<并添加ORDER BY到子查询中来获取最大交货日期以外的数据列表。
But its an entire list but i want only the second max date.
但它是一个完整的列表，但我只想要第二个最大日期。
Someone pls tell me how I can get only the second max date.
有人请告诉我如何才能获得第二个最大日期。
Note: I have tried using ROWNUM<=1but i am getting wrong date
注意：我试过使用，ROWNUM<=1但我得到了错误的日期
采纳答案by Tony Roczz
SELECT D.ORDER_NO
FROM
(
SELECT DISTINCT D.ORDER_NO,D.DELIVERY_DATE,ROW_NUMBER()OVER(order by D.DELIVERY_DATE desc) RowNo
FROM DELIVERY D
WHERE D.CUSTOMER_NO =1128158
ORDER BY D.DELIVERY_DATE DESC)
t WHERE t.RowNo = 2;
回答by Tim Biegeleisen
Assuming you are using Oracle:
假设您使用的是 Oracle：
SELECT *
FROM
(
SELECT t.*, rownum rnum
FROM
(
SELECT DISTINCT D.ORDER_NO
FROM DELIVERY D
WHERE D.CUSTOMER_NO = 112
ORDER BY D.DELIVERY_DATE DESC
) t
WHERE rownum <= 2
)
WHERE rnum >= 2
回答by Kiran
You can try this query:
你可以试试这个查询：
SELECT DISTINCT ORDER_NO FROM DELIVERY WHERE CUSTOMER_NO=112 AND DELIVERY_DATE =
(SELECT MAX(DELIVERY_DATE) FROM DELIVERY WHERE CUSTOMER_NO=112 AND
DELIVERY_DATE<(SELECT MAX(DELIVERY_DATE) FROM DELIVERY AND CUSTOMER_NO=112));
The sub query will return second max delivery date which will give order_no of second max date.
子查询将返回第二个最大交货日期，这将给出第二个最大日期的 order_no。
回答by Munna Extreme
SELECT * FROM(
SELECT DISTINCT D.ORDER_NO, ROW_NUMBER()OVER(order by D.DELIVERY_DATE desc) RowNo
FROM DELIVERY D
WHERE D.CUSTOMER_NO =112
) t where t.RowNo = 2
回答by JIYAUL MUSTAPHA
Select Second max date From table
从表中选择第二个最大日期
SELECT MAX(date) FROM tbl_date WHERE date NOT IN (SELECT MAX(date) FROM tbl_date )
OR
SELECT DISTINCT date FROM tbl_date ORDER BY date DESC LIMIT 1,1;
OR
SELECT MAX(date) FROM ( SELECT date FROM tbl_date MINUS SELECT MAX(date) FROM tbl_date)
回答by Rajesh-Systematix
SELECT TOP 1 D.ORDER_NO
FROM DELIVERY D WHERE D.CUSTOMER_NO =112
AND D.DELIVERY_DATE = (SELECT MAX(D1.DELIVERY_DATE) FROM DELIVERY D1 WHERE D1.CUSTOMER_NO = 112 );
ORDER BY DELIVERY_DATE DESC
回答by Suman Bhandari
change D.DELIVERY_DATE =
改变 D.DELIVERY_DATE =
to D.DELIVERY_DATE <
到 D.DELIVERY_DATE <
SELECT DISTINCT D.ORDER_NO
FROM DELIVERY D
WHERE D.CUSTOMER_NO =112 AND D.DELIVERY_DATE = (SELECT MAX(D1.DELIVERY_DATE) FROM DELIVERY D1
WHERE D1.CUSTOMER_NO = 112 );
OR SOMETHING LIKE BELOW
或类似下面的东西
SELECT MAX(DELIVERY_DATE)
FROM TABLE
WHERE DELIVERY_DATE< ( SELECT MAX(DELIVERY_DATE)
FROM TABLE)
