Mysql技巧

Inner Join 两个表的 公共部分 Left Join 查询两个表 A表有 B表无

 select from table Ta a left join Tb b on a.key = b.key where b.key is null

子查询 更新表

 update user1 a join (select b.`user_name` from user1 a join user2 b on a.`user_name` = b.`user_name`) b on a.`user_name` = b.`user_name`;

行转列 如多个手机号

 select user_name 
 ,Replace (substring(substring_index(
 mobile,',',a.id),
 char_length(substring_index(
 mobile,',',a.id-1))+1),',','') as mobile
 from tb_sequence a 
 cross join( select user_name,concat(mobile,',') as mobile,length(mobile)-length(replace(mobile,',',''))+1 size 
 from user1 b) on a.id <= b.size  

 SELECT a.`user_name` ,a.`over` ,b.`over` FROM user1 a INNER JOIN user2  b ON a.`user_name` = b.`user_name`;  

SELECT * FROM user1 a LEFT JOIN user2 b ON a.`user_name` = b.`user_name` WHERE b.`user_name` IS NOT NULL;  

SELECT a.`user_name` ,a.`over` ,b.`over` FROM user1 a LEFT JOIN user2 b ON a.`user_name` = b.`user_name` WHERE b.`user_name` IS NULL;  

SELECT * FROM user1 a RIGHT JOIN user2 b ON a.`user_name` = b.`user_name`  WHERE a.`user_name` IS   NULL;   

SELECT * FROM user1 a LEFT JOIN user2 b ON a.`user_name` = b.`user_name` UNION ALL SELECT * FROM user1 a 
RIGHT JOIN user2 b ON a.`user_name` =b.`user_name`;  

SELECT 
  * 
FROM
  user1 a 
  CROSS JOIN user2 b 
    UPDATE 
      user1 c 
    SET
      over = '齐天大圣' 
    WHERE c.`id` = 
      (SELECT 
        a.`id` 
      FROM
        user1 a 
        LEFT JOIN user2 b 
          ON a.`user_name` = b.`user_name` 
      WHERE b.`user_name` IS NOT NULL) ;  

UPDATE 
  user1 a 
  JOIN 
    (SELECT 
      b.`user_name` 
    FROM
      user1 a 
      JOIN user2 b 
        ON a.`user_name` = b.`user_name`) b 
    ON a.`user_name`  = b.`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`,b.`timestr`,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`) ORDER BY kills DESC;  

SELECT a.`user_name`,b.`timestr`,b.`kills` FROM user1 a  JOIN user_kills b ON a.`id` = b.`user_id` ORDER BY kills DESC;  

SELECT a.`user_name`,b.`timestr`,b.`kills` FROM user1 a  JOIN user_kills b ON a.`id` = b.`user_id`  JOIN user_kills
c ON c.`user_id` = b.`user_id` GROUP BY a.`user_name`,b.`timestr`,b.`kills` HAVING b.`kills` = MAX(c.`kills`);  

SELECT 
  d.`user_name`,
  c.timestr,
  kills ,
  cnt
FROM
  (SELECT 
    user_id,
    timestr,
    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,
    timestr,
    kills) c 
  JOIN user1 d 
    ON c.user_id = d.id 
WHERE cnt <= 2 ;