第七色在线视频,2021少妇久久久久久久久久,亚洲欧洲精品成人久久av18,亚洲国产精品特色大片观看完整版,孙宇晨将参加特朗普的晚宴

left join使用1

相關的數(shù)據(jù)表

/*

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 */;

繼續(xù)優(yōu)化的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

實現(xiàn)分組的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;

本連句和子查詢結果一致,查詢數(shù)據(jù)表中打怪最多的兩個時間,但是假設豬八戒有兩個不同時間相同的數(shù)目,講師所講解的select 為直接過濾掉,只查詢一條數(shù)據(jù),本select 為查出三條數(shù)據(jù)

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

現(xiàn)實中分組查詢,如果根據(jù)user表查詢從表中 達到分組,逐條查詢的 foreach 會出現(xiàn)截圖問題

查詢打怪最多的日期

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

優(yōu)化后:

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 優(yōu)化子查詢

查詢出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;

優(yōu)化

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 技巧

獲取兩張表中的都存在的數(shù)據(jù)更新這一條記錄

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

如果查詢某個數(shù)據(jù)在A表不在B表,一般會用where 中的not in B 不能使用索引

left join對not in 進行優(yōu)化

SQL中的join類型

內(nèi)連接INNER

全外連接 FULL OUTER

左外連接 LEFT OUTER

右外連接 RIGHT OUTER

交叉連接 CROSS