在个人首页上显示10个最近浏览过我的首页的好友,不能重复。
假设 A 浏览了 那么:A 。
B又浏览 那么:B A
A再次浏览 那么:A B
而不是A B A
数据库的记录是:
userid visitorid visitetime
1 A ...
1 B ...
1 A ...查询userid = 1 的用户首页的最近访问情况,查询10条记录,如何查?
假设 A 浏览了 那么:A 。
B又浏览 那么:B A
A再次浏览 那么:A B
而不是A B A
数据库的记录是:
userid visitorid visitetime
1 A ...
1 B ...
1 A ...查询userid = 1 的用户首页的最近访问情况,查询10条记录,如何查?
FROM (SELECT t.* row_number() over(PARTITION BY userid, visitorid ORDER BY visitetime DESC) rn
FROM table1 t
WHERE t.userid = 1
ORDER BY visitetime DESC)
WHERE rn = 1 AND
rownum <= 10;
(
select table.*,row_number() over(partition by visitorid order by rownum) rn from table
where userid = 1 and rn =1
)
where rownum <=10;
select wm_concat(visitorid) from
(
select userid, visitorid, visitetime from
(
select table.*,row_number() over(partition by visitorid order by rownum) rn from table
where userid = 1 and rn =1
)
where rownum <=10
)
group by userid;
(
select userid, visitorid, visitetime from
(
select table.*,row_number() over(partition by visitorid order by rownum) rn from table
where userid = 1 and rn =1
)
where rownum <=10
)
group by userid;
问一下标准SQL能不能写啊,我用的不是Oracle,是mySql
from
(select distinct(visitorid),max(visitetime) t
from table_name
where userid=1
group by visitorid
order by t desc
)
where rownum <11;
select visitorid
from
(select distinct(visitorid),max(visitetime) t
from table_name
where userid=1
group by visitorid
order by t desc
)
where rownum <11;
如 前十行 SELECT * FROM table LIMIT 10; 改一下楼上的
select visitorid
from
(select distinct(visitorid),max(visitetime) t
from table_name
where userid=1
group by visitorid
order by t desc
)
LIMIT 10;
select * from
(select visitorid,max(visitetime) vtime from test
where userid=1
group by visitorid
order by vtime desc) t
limit 10;