首页 新闻 赞助 找找看

问个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日期都是一样的。

何小宝的主页 何小宝 | 初学一级 | 园豆:19
提问于:2013-10-18 14:57
< >
分享
最佳答案
0
select * from TableA where id in (select id from TableA where CreatedON=(select Max(createdOn)  as createdon from TableA))
收获园豆:5
happydaily | 菜鸟二级 |园豆:301 | 2013-10-19 03:35
其他回答(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)
写代码的小2B | 园豆:4371 (老鸟四级) | 2013-10-18 15:23

前辈 我想取得<<最新>>的那3条记录 比如我上面那个  取出来应该是2013-09-28的3条记录

支持(0) 反对(0) 何小宝 | 园豆:19 (初学一级) | 2013-10-18 16:16

上面的代码是按ID分组取出最新的一个记录;

--你的意思是这样吗?
SELECT TOP 3 FROM TEMP ORDER BY CREATED DESC

不太理解你的需求,不是按照ID分组吗。

还是说分组后取最新的多条记录。

支持(0) 反对(0) 写代码的小2B | 园豆:4371 (老鸟四级) | 2013-10-18 16:51
0
select * from (SELECT *,rownum=ROW_NUMBER() OVER(PARTITION BY Id ORDER BY CreatedOn desc) FROM TableTest) t
WHERE t.rownum=1
幻天芒 | 园豆:37175 (高人七级) | 2013-10-18 15:44

SQL2000。

支持(0) 反对(0) 何小宝 | 园豆:19 (初学一级) | 2013-10-18 16:16
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

诶碧司 | 园豆:1912 (小虾三级) | 2013-10-18 16:57
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册