--写在外面select userid,pname,adddate,(select top 1 id from product a where a.userid = product.userid order by a.adddate desc) from product group by userid
select userid,pname,adddate,(select top 1 id from product a where a.userid = product.userid order by a.adddate desc) from product group by userid,pname,adddate
以userid分组找出各组最大的ID号在与原表关联查询即可
写个取3个的. 因为id是自增的,所以id越大,时间也就越大.可以用id代时间,还无重复值.SELECT * FROM products a WHERE 3>(SELECT COUNT(1) FROM products b WHERE a.userid=b.userid AND b.id>a.id)要取几个将N改为3就行了.
晕倒,你这样 group by 就不符合要求了 ,我需要取出每个人发的最后一条信息
如果取一个还可以写成: SELECT * FROM products a WHERE NOT EXISTS>(SELECT 1 FROM products b WHERE a.userid=b.userid AND b.id>a.id)
那你取出ID,用ID和原表Join一下不就可以了?
Select A.* From product A Inner Join (Select Max(adddate) As adddate,userid From product group By userid) B On A.adddate=B.adddate And A.userid=B.userid
CREATE TABLE TEST (ID INT IDENTITY(1,1) NOT NULL, PNAME VARCHAR(20), USERID CHAR(4), ADDDATE DATETIME)INSERT TEST(PNAME,USERID,ADDDATE) SELECT 'A','A001','2006-7-11 11:12:13' UNION ALL SELECT 'A','A001','2006-7-12 11:12:13' UNION ALL SELECT 'A','A001','2006-7-13 11:12:13' UNION ALL SELECT 'B','A002','2006-7-10 11:12:13' UNION ALL SELECT 'B','A002','2006-7-16 11:12:13'SELECT * FROM TESTSELECT * FROM TEST a WHERE 1>(SELECT COUNT(*) FROM TEST b WHERE a.ADDDATE<b.ADDDATE and a.USERID=b.USERID)DROP TABLE TEST
select * from (select count(id) allnum,(select top 1 id from product a where a.userid = product.userid order by a.adddate desc,id desc) id from product group by userid) b inner join product on product.id = b.id用JOIN取是取出来了,可惜效率不咋的,才10来万的数据要2秒 ,有没有效率高的做法呢?
xjchen(星际浪子) ( ) 信誉:100 2006-07-19 15:53:00 得分: 0
可惜时间是可以刷新的,并且有可以相同 ,所以最大的ID 并非是 时间最后的 -------------------------------------------------------------------------这个早考滤过了. 因为同USERID组里的你要取一条记录,当时间又重复时,你还需借助ID来取值,比如 id userid 1 1 2005-1-1 2 1 2005-1-1 比如对于userid=1的用户只有这两条记录,时间又重复,你要选一条,如何选???? 用ID恰好避免了这个情况.不过如果记录可以更新时间的话,确实是个问题.这样,ID跟DATE字段的大小走向就不一致了.那么这时最准确的写法应该是(即使时间有重复也只取一条) 设当出现 同用户的最大时间有重复值时取id最大的SELECT a.* FROM table a INNER JOIN (Select MAX(ID) mi,Max(adddate) As md,userid From product group By userid) b ON a.UserID=b.UserID AND a.ID=b.mi AND a.adddate=b.md
回 paoluo(一天到晚游泳的鱼) 的语句,因为adddate 有可能相同 ,所以此法不行
xjchen(星际浪子) ( ) 信誉:100 2006-07-19 16:05:00 得分: 0
select * from (select count(id) allnum,(select top 1 id from product a where a.userid = product.userid order by a.adddate desc,id desc) id from product group by userid) b inner join product on product.id = b.id用JOIN取是取出来了,可惜效率不咋的,才10来万的数据要2秒 ,有没有效率高的做法呢?
---------------------------------------------------- 改為Select A.* From product A Inner Join (Select Max(adddate) As adddate,userid From product group By userid) B On A.adddate=B.adddate And A.userid=B.userid
fcuandy(边打魔兽边回贴)SELECT a.* FROM table a INNER JOIN (Select MAX(ID) mi,Max(adddate) As md,userid From product group By userid) b ON a.UserID=b.UserID AND a.ID=b.mi AND a.adddate=b.md------------------------------- 最大的ID不一定是最大的adddate,這麼寫肯定有問題的。
e,我呼略了.其实跟昨晚那个第三题一样.要多连一次表SELECT a.* FROM table a INNER (SELECT MAX(x.id) mi,md,c.userid FROM table x INNER JOIN (SELECT Max(adddate) As md,userid From product group By userid) c ON c.MD=x.md AND c.userid=x.userid GROUP BY md,c.userid) b ON b.mi=a.id
select count(id) c,(select top 1 id from product a where a.userid = product.userid order by a.adddate desc) id from product group by userid 运行显示0秒,但select * from (select count(id) allnum,(select top 1 id from product a where a.userid = product.userid order by a.adddate desc,id desc) id from product group by userid) b inner join product on product.id = b.id要2秒 索引已加 userid | userid,adddate,id 有没有办法提高效率?
格式改进一下.SELECT a.* FROM table a INNER ( SELECT MAX(x.id) mi,md,c.userid FROM table x INNER JOIN (SELECT MAX(adddate) AS md,userid FROM product GROUP BY userid) c ON c.MD=x.md AND c.userid=x.userid GROUP BY md,c.userid ) b ON b.mi=a.id
手误 INNER JOIN 把 JOIN打掉了
我也想了另外一个写法,可惜效率也不理想 select * from product a where id in(select top 1 id from product b where b.userid = a.userid order by b.adddate desc,b.id desc)
WHERE 3>(SELECT COUNT(1) FROM products b WHERE a.userid=b.userid AND b.id>a.id)要取几个将N改为3就行了.
SELECT * FROM products a
WHERE NOT EXISTS>(SELECT 1 FROM products b WHERE a.userid=b.userid AND b.id>a.id)
From product A
Inner Join (Select Max(adddate) As adddate,userid From product group By userid) B
On A.adddate=B.adddate And A.userid=B.userid
(ID INT IDENTITY(1,1) NOT NULL,
PNAME VARCHAR(20),
USERID CHAR(4),
ADDDATE DATETIME)INSERT TEST(PNAME,USERID,ADDDATE)
SELECT 'A','A001','2006-7-11 11:12:13'
UNION ALL SELECT 'A','A001','2006-7-12 11:12:13'
UNION ALL SELECT 'A','A001','2006-7-13 11:12:13'
UNION ALL SELECT 'B','A002','2006-7-10 11:12:13'
UNION ALL SELECT 'B','A002','2006-7-16 11:12:13'SELECT * FROM TESTSELECT * FROM TEST a WHERE 1>(SELECT COUNT(*) FROM TEST b WHERE a.ADDDATE<b.ADDDATE and a.USERID=b.USERID)DROP TABLE TEST
(select count(id) allnum,(select top 1 id from product a where a.userid = product.userid order by a.adddate desc,id desc) id from product group by userid) b inner join product on product.id = b.id用JOIN取是取出来了,可惜效率不咋的,才10来万的数据要2秒 ,有没有效率高的做法呢?
可惜时间是可以刷新的,并且有可以相同 ,所以最大的ID 并非是 时间最后的
-------------------------------------------------------------------------这个早考滤过了.
因为同USERID组里的你要取一条记录,当时间又重复时,你还需借助ID来取值,比如
id userid
1 1 2005-1-1
2 1 2005-1-1
比如对于userid=1的用户只有这两条记录,时间又重复,你要选一条,如何选???? 用ID恰好避免了这个情况.不过如果记录可以更新时间的话,确实是个问题.这样,ID跟DATE字段的大小走向就不一致了.那么这时最准确的写法应该是(即使时间有重复也只取一条)
设当出现 同用户的最大时间有重复值时取id最大的SELECT a.* FROM
table a
INNER JOIN
(Select MAX(ID) mi,Max(adddate) As md,userid From product group By userid) b
ON a.UserID=b.UserID AND a.ID=b.mi AND a.adddate=b.md
select * from
(select count(id) allnum,(select top 1 id from product a where a.userid = product.userid order by a.adddate desc,id desc) id from product group by userid) b inner join product on product.id = b.id用JOIN取是取出来了,可惜效率不咋的,才10来万的数据要2秒 ,有没有效率高的做法呢?
----------------------------------------------------
改為Select A.*
From product A
Inner Join (Select Max(adddate) As adddate,userid From product group By userid) B
On A.adddate=B.adddate And A.userid=B.userid
回 paoluo(一天到晚游泳的鱼) 的语句,因为adddate 有可能相同 ,所以此法不行
------------------------
同一個userid,在完全相同的時間裡有兩條紀錄?!這個,應該不可能吧。
table a
INNER JOIN
(Select MAX(ID) mi,Max(adddate) As md,userid From product group By userid) b
ON a.UserID=b.UserID AND a.ID=b.mi AND a.adddate=b.md-------------------------------
最大的ID不一定是最大的adddate,這麼寫肯定有問題的。
有可能,因为他可以批量刷新时间
INNER
(SELECT MAX(x.id) mi,md,c.userid FROM table x
INNER JOIN
(SELECT Max(adddate) As md,userid From product group By userid) c
ON c.MD=x.md AND c.userid=x.userid
GROUP BY md,c.userid) b
ON b.mi=a.id
(select count(id) allnum,(select top 1 id from product a where a.userid = product.userid order by a.adddate desc,id desc) id from product group by userid) b inner join product on product.id = b.id要2秒 索引已加 userid | userid,adddate,id
有没有办法提高效率?
INNER
(
SELECT MAX(x.id) mi,md,c.userid
FROM table x
INNER JOIN
(SELECT MAX(adddate) AS md,userid FROM product GROUP BY userid) c
ON c.MD=x.md AND c.userid=x.userid
GROUP BY md,c.userid
) b
ON b.mi=a.id
INNER JOIN
把 JOIN打掉了
select * from product a where id in(select top 1 id from product b where b.userid = a.userid order by b.adddate desc,b.id desc)
FROM production
where id in (SELECT TOP 1 id
FROM production a
WHERE a.userid = production.userid
ORDER BY a.adddate DESC)