问个SQL问题
0
悬赏园豆：5
[已解决问题]
解决于 2013-10-21 10:35
表结构
ID. CreatedOn Pid
1 2013-09-26 16:24:52.250 1
1 2013-09-26 16:24:52.327 2
1 2013-09-26 16:24:52.343 3
2 2013-09-27 15:09:07.703 4
2 2013-09-27 15:09:07.720 5
2 2013-09-27 15:09:07.720 6
3 2013-09-28 15:09:07.703 7
3 2013-09-28 15:09:07.720 8
3 2013-09-28 15:09:07.720 9
我想知道 如果按照ID来分组 按照CrteateOn作为条件
我想取出最新更改的记录 该怎么SQL语句?
注意可能不止3条ID为1或者2或者3的记录。但是CreateOn日期都是一样的。
最佳答案
0
select * from TableA where id in (select id from TableA where CreatedON=(select Max(createdOn) as createdon from TableA))
收获园豆：5
其他回答(3)
0
USE tempdb GO IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='TEMP') DROP TABLE TEMP CREATE TABLE TEMP ( ID INT, CREATED DATETIME, PID INT ) GO INSERT INTO TEMP VALUES (1,'2013-01-01 11:11:11',1), (1,'2013-01-01 11:11:12',2), (1,'2013-01-01 11:11:13',3), (2,'2013-01-01 11:11:11',1), (2,'2013-01-01 11:11:12',2), (2,'2013-01-01 11:11:13',3), (2,'2013-01-01 11:11:14',4), (3,'2013-01-01 11:11:11',1), (3,'2013-01-01 11:11:12',2), (3,'2013-01-01 11:11:13',3), (3,'2013-01-01 11:11:14',4) GO SELECT * FROM TEMP AS A WHERE NOT EXISTS(SELECT 0 FROM TEMP AS B WHERE A.ID=B.ID AND A.CREATED<B.CREATED)
前辈 我想取得<<最新>>的那3条记录 比如我上面那个 取出来应该是2013-09-28的3条记录
上面的代码是按ID分组取出最新的一个记录；
--你的意思是这样吗？ SELECT TOP 3 FROM TEMP ORDER BY CREATED DESC
不太理解你的需求，不是按照ID分组吗。
还是说分组后取最新的多条记录。
0
select * from (SELECT *,rownum=ROW_NUMBER() OVER(PARTITION BY Id ORDER BY CreatedOn desc) FROM TableTest) t WHERE t.rownum=1
SQL2000。
0
select a.*
from table as a
inner join (select id, max(CreatedOn) as CreatedOn
from table
group by id) as b on a.id=b.id and a.CreatedOn=b.CreatedOn
