join相关技巧

UPDATE user1 a JOIN 
(SELECT b.user_name FROM user1 a JOIN user2 b ON a.user_name = b.user_name) c 
ON a.user_name = c.user_name 
SET a.over='齐天大圣';

select a.user_name,a.over,(select over from user2 b where a.user_name = b.user_name) as over2 from user1 a;

select a.user_name,a.over,b.over as over2 from user1 a left join user2 b on a.user_name = b.user_name;

select a.user_name,b.killday,b.kills from user1 a join user_kills b on a.id = b.user_id
where b.kills = (select max(c.kills) from user_kills c where c.user_id = b.user_id);

select a.user_name,b.killday,b.kills from user1 a 
join user_kills b on a.id = b.user_id
join user_kills c on b.user_id = c.user_id
group by a.user_name,b.killday,b.kills having b.kills = max(c.kills);

select a.user_name,b.killday,b.kills from user1 a 
join user_kills b on a.id = b.user_id
where user_name ='孙悟空' 
order by b.kills desc limit 2;

select a.user_name,b.killday,b.kills from user1 a 
join user_kills b on a.id = b.user_id
where user_name ='猪八戒' 
order by b.kills desc limit 2;

select a.user_name,b.killday,b.kills from user1 a 
join user_kills b on a.id = b.user_id
where user_name ='沙僧' 
order by b.kills desc limit 2;

with tmp as 
(select a.user_name,b.killday,b.kills,row_number() over 
    (partition by a.user_name order by b.kills) cnt 
from user1 a join user_kills b on a.id = b.user_id)
select * from tmp where cnt <=2;

select d.user_name,c.killday,kills 
from (select user_id,killday,kills,
    (select count(*) from user_kills b where b.user_id = a.user_id and a.kills <= b.kills) as cnt
    from user_kills a group by user_id,killday,kills ) c
join user1 d on c.user_id = d.id 
where cnt <= 2;