我有三张表:
A(user_id,user_name);
B(user_id,word_id,create_date);
C(word_id,word_name);
现在我想实现如下目标:
select * from A,B,C
where A.user_id=B.user_id
and B.word_id=C.word_id
group by a.user_id然后我想取得每个分组中create_date最新的记录.
A(user_id,user_name);
B(user_id,word_id,create_date);
C(word_id,word_name);
现在我想实现如下目标:
select * from A,B,C
where A.user_id=B.user_id
and B.word_id=C.word_id
group by a.user_id然后我想取得每个分组中create_date最新的记录.
select *,ROW_NUMBER() OVER (PARTITION BY a.user_id ORDER BY create_date desc) as rn
from A,B,C
where A.user_id=B.user_id
and B.word_id=C.word_id
and rn=1
where A.user_id=B.user_id
and B.word_id=C.word_id
group by a.user_id
这句就不行吧?
select aa.user_id user_id,max(create_date) create_date from (select a.user_id from A,B,C
where A.user_id=B.user_id
and B.word_id=C.word_id
group by a.user_id ) aa,b where aa.user_id = b.user_id group by aa.user_id
不要考虑效能 试试这个呢
SQL codeselect *,ROW_NUMBER() OVER (PARTITION BY a.user_id ORDER BY create_date desc) as rn
from A,B,C
where A.user_id=B.user_id
and B.word_id=C.word_id
and rn=1
可以的。
select d.*
from (select a.*,
b.word_id,
b.create_date,
c.word_name,
row_number() over(partition by a.user_id order by b.create_date desc) as rn
from A
inner join B ON a.user_id = b.user_id
inner join C ON b.word_id = c.word_id) d
where d.rn = 1