left join使用1

相关的数据表

/*

SQLyog Ultimate v13.1.1 (64 bit)

MySQL - 5.7.26 : Database - test_mysql

*********************************************************************

*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/`test_mysql` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

USE `test_mysql`;

/*Table structure for table `user_kills` */

DROP TABLE IF EXISTS `user_kills`;

CREATE TABLE `user_kills` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `kills` int(2) NOT NULL,

  `timestr` datetime NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `user_kills` */

insert  into `user_kills`(`id`,`user_id`,`kills`,`timestr`) values 

(1,2,15,'2013-01-10 00:00:00'),

(2,2,2,'2013-01-02 00:00:00'),

(3,2,12,'2013-02-05 00:00:00'),

(4,4,3,'2013-01-10 00:00:00'),

(5,4,5,'2020-11-12 02:54:05'),

(6,2,1,'2020-11-05 02:54:24'),

(7,3,20,'2020-11-11 02:54:37'),

(8,2,10,'2020-11-11 02:54:54'),

(9,3,17,'2020-11-10 02:55:06'),

(10,3,22,'2020-11-13 04:32:30');

/*Table structure for table `user1` */

DROP TABLE IF EXISTS `user1`;

CREATE TABLE `user1` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_name` char(10) COLLATE utf8_unicode_ci NOT NULL,

  `over` char(10) COLLATE utf8_unicode_ci NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `user1` */

insert  into `user1`(`id`,`user_name`,`over`) values 

(1,'唐曾','功德佛'),

(2,'猪八戒','净坛使者'),

(3,'孙悟空','齐天大圣'),

(4,'沙增','金身罗汉');

/*Table structure for table `user2` */

DROP TABLE IF EXISTS `user2`;

CREATE TABLE `user2` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_name` char(10) COLLATE utf8_unicode_ci NOT NULL,

  `over` char(10) COLLATE utf8_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `user2` */

insert  into `user2`(`id`,`user_name`,`over`) values 

(1,'孙悟空','成佛'),

(2,'牛魔王',NULL),

(3,'蛟魔王',NULL),

(4,'鹏魔王',NULL),

(5,'狮魔王',NULL);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

继续优化的sql

SELECT c.user_name, a.timestr, a.kills   FROM user1 c

JOIN user_kills a ON c.id = a.user_id

JOIN user_kills b ON a.user_id = b.user_id

WHERE a.kills <= b.kills

GROUP BY  user_name, kills DESC, timestr

HAVING COUNT(b.kills) <= 2

实现分组的join

SELECT d.user_name,c.timestr,kills 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;

子查询SELECT a.user_name,b.kills,b.timestr FROM user1 a JOIN user_kills b ON a.id=b.`user_id` WHERE (SELECT COUNT(*) FROM user_kills c WHERE b.user_id=c.user_id AND b.kills<c.kills)<2;

本连句和子查询结果一致,查询数据表中打怪最多的两个时间,但是假设猪八戒有两个不同时间相同的数目,讲师所讲解的select 为直接过滤掉,只查询一条数据,本select 为查出三条数据

SELECT d.user_name,c.timestr,kills 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<=1;

ROW_NUMBER()  SQLServer Oracle 可以这么查询

with tmp AS (select a.user_name,b.timestr,b.kills,ROW_NUMBER() over(patition 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

现实中分组查询,如果根据user表查询从表中 达到分组,逐条查询的 foreach 会出现截图问题

查询打怪最多的日期

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);

优化后:

select a.user_name,b.timestr,b.kills from user1 a join user_kills b ON a.id=b.user_id 

join user_kills b ON c.user_id=b.user_id 

group by a.user_name,b.timestr,b.kills having b.kills=max(c.kills);

join 聚合查询

插入新表

使用join 优化子查询

查询出A 表中所有记录,包含共有B

select a.user_name,a.over,(select oer 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;

join 技巧

获取两张表中的都存在的数据更新这一条记录

update user1 set over='齐天大圣' where user1.user_name in (select b.user_name from user1 a join user2 b on a.user_name=b.user_name);

error:1093 

技巧:

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.ver='齐天大圣';

cross join 交叉连接

笛卡尔连接(Cartesian join) 叉乘(product)

即为 两张表的乘积

full join 

mysql 不支持full join

技巧:

full join 

LEFT OUTER JOIN

如果查询某个数据在A表不在B表,一般会用where 中的not in B 不能使用索引

left join对not in 进行优化

SQL中的join类型

内连接INNER

全外连接 FULL OUTER

左外连接 LEFT OUTER

右外连接 RIGHT OUTER

交叉连接 CROSS