SQL数据库中订单号相同，取日期最大值的记录问题
0
悬赏园豆：40
[已解决问题]
解决于 2013-12-22 20:08
以下是数据库中的记录
日期 订单号 订单序列号 订单量
odate ordcode idx qtp
2012-7-3 AD12C3-095ABCD 100 1024
2012-6-28 AD12C3-095ABCD 200 1248
2012-6-9 AD12C3-095ABCD 200 864
2012-6-28 AD12C3-095ABCD 300 512
2012-6-9 AD12C3-095ABCD 300 608
2012-6-28 AD12C3-095ABCD 400 416
2012-6-9 AD12C3-095ABCD 400 736
2012-7-3 AD12C3-095ABCD 500 632
2012-7-5 AD12C3-095E 100 800
2012-7-2 AD12C3-095F 100 200
2012-7-4 AD12C3-095G 100 400
2012-6-22 AD12C3-096ABCD 100 928
2012-6-1 AD12C3-096ABCD 100 1024
2012-6-22 AD12C3-096ABCD 200 1120
2012-6-1 AD12C3-096ABCD 200 928
2012-6-26 AD12C3-096ABCD 300 576
2012-6-1 AD12C3-096ABCD 300 608
2012-6-22 AD12C3-096ABCD 400 576
2012-6-1 AD12C3-096ABCD 400 640
---------------------------------------------------------
对于以上这些“蓝色”和“红色”的记录，我想取订单号和订单序列号相同的，日期最大的这条记录显示出来。也就是去掉，上面蓝色的记录，显示结果要如下，请问SQL语句要如何写。
日期 订单号 订单序列号 订单量
odate ordcode idx qtp
2012-7-3 AD12C3-095ABCD 100 1024
2012-6-28 AD12C3-095ABCD 200 1248
2012-6-28 AD12C3-095ABCD 300 512
2012-6-28 AD12C3-095ABCD 400 416
2012-7-3 AD12C3-095ABCD 500 632
2012-7-5 AD12C3-095E 100 800
2012-7-2 AD12C3-095F 100 200
2012-7-4 AD12C3-095G 100 400
2012-6-22 AD12C3-096ABCD 100 928
2012-6-22 AD12C3-096ABCD 200 1120
2012-6-26 AD12C3-096ABCD 300 576
2012-6-22 AD12C3-096ABCD 400 576
最佳答案
0
日期最大的这条记录显示出来：
select * from order where odate in (select max(m.odate) from order as m inner join order as n on m.ordcode=n.ordcode where m.ordcode=n.ordcode and m.idx=n.idx) order 是表名。这个我没有在sqlserver里面测试，你试试吧，思路就是这样的。
收获园豆：40
好像有错，
其他回答(3)
0
求高手帮忙哦
0
select max(日期字段),订单号,订单序列号
from t1
group by 订单号,订单序列号
我需要“订单量”这个字段也出来，你这方法我用过了，不行
1
;WITH tbl AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY ordcode,idx ORDER BY odate DESC) rn FROM #table
)SELECT * FROM tbl
WHERE rn=1
或者
SELECT * FROM #table
WHERE odate IN(
SELECT MAX(odate) FROM #table GROUP BY ordcode,idx
)
测试如下
sql2008运行，未做优化。
CREATE TABLE #table(
odate DATETIME,
ordcode VARCHAR(50),
idx INT,
qtp int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-7-3', -- odate - datetime
'AD12C3-095ABCD', -- ordcode - varchar(50)
100, -- idx - int
1024 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-6-28', -- odate - datetime
'AD12C3-095ABCD', -- ordcode - varchar(50)
200, -- idx - int
1248 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-6-9', -- odate - datetime
'AD12C3-095ABCD', -- ordcode - varchar(50)
200, -- idx - int
864 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-6-28', -- odate - datetime
'AD12C3-095ABCD', -- ordcode - varchar(50)
300, -- idx - int
512 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-6-9', -- odate - datetime
'AD12C3-095ABCD', -- ordcode - varchar(50)
300, -- idx - int
608 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-6-28', -- odate - datetime
'AD12C3-095ABCD', -- ordcode - varchar(50)
400, -- idx - int
416 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-6-9', -- odate - datetime
'AD12C3-095ABCD', -- ordcode - varchar(50)
400, -- idx - int
736 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-7-3', -- odate - datetime
'AD12C3-095ABCD', -- ordcode - varchar(50)
500, -- idx - int
632 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-7-5', -- odate - datetime
'AD12C3-095E', -- ordcode - varchar(50)
100, -- idx - int
800 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-7-2', -- odate - datetime
'AD12C3-095F', -- ordcode - varchar(50)
100, -- idx - int
200 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-7-4', -- odate - datetime
'AD12C3-095G', -- ordcode - varchar(50)
100, -- idx - int
400 -- qtp - int
)
INSERT INTO #table ( odate, ordcode, idx, qtp )
VALUES ( '2012-6-22', -- odate - datetime
'AD12C3-096ABCD', -- ordcode - varchar(50)
100, -- idx - int
928 -- qtp - int
)
--省略了一些测试数据
SELECT * FROM #table
WHERE odate IN(
SELECT MAX(odate) FROM #table GROUP BY ordcode,idx
)
;WITH tbl AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY ordcode,idx ORDER BY odate DESC) rn FROM #table
)SELECT * FROM tbl
WHERE rn=1
DROP TABLE #table
DECLARE @CustomerGUID AS uniqueidentifier='23737252-B51C-40A9-AA27-3436BFFA3926'
;
With CTE_IDMaxDT AS (
SELECT [MaterialID]
,MAX([DTPoint]) AS MaxDT
FROM [Quotation]
WHERE [QuotationTypeID]=1
AND [CustomerGUID]=@CustomerGUID
GROUP BY [MaterialID]
)
SELECT [ID]
,[CustomerGUID]
,[ProjectSiteGUID]
,[DTPoint]
,[QuotationTypeID]
,[Quotation].[MaterialID]
,[VehicleTypeID]
,[Price]
,[PriceUnit]
,[IsTaxIncluded]
,[Status]
,[DTSubmit]
,[SubmitEmpID]
,[DTApproval]
,[ApprovalResult]
,[ApprovalEmpID]
,[DTCreate]
,[DTUpdate]
FROM [Quotation]
inner JOIN [CTE_IDMaxDT]
ON [Quotation].[MaterialID]=[CTE_IDMaxDT].[MaterialID]
AND [Quotation].[DTPoint]=[CTE_IDMaxDT].[MaxDT]
WHERE [QuotationTypeID]=1
AND [CustomerGUID]=@CustomerGUID
ORDER BY [Quotation].[MaterialID]
