3-1 如何实现分组选择数据

循环执行sql查询语句缺点

此sql语句是查询出某种条件下结果，但是当条件有多种情况时，可以通过如下方法分别优化：

1）、需要循环执行此sql查询语句（缺点详见“循环执行sql查询语句缺点”）

2）、分类聚合查询

一次性分类聚合查询出结果（可使用join对子查询进一步优化）

mysql中不支持使用ROW_NUMBER()进行分组查询优化

相关的数据表

/*

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 会出现截图问题

实现分组选择：（记录分成多个分类（在分类中选取出数据）

mysql> select a.username,b.kills from(select username,kills,(select count(*) from tbuser2 c

    -> where c.username=d.username and c.kills=d.kills) as cnt

    -> from tbuser2 d

    -> group by username,kills

    -> )b join tbuser1 a on a.id=b.username

    -> where cnt<=2;

在分组选择中，一次性的选择出所有的分组，而不是分批次的完成。

分组选择代码，MySQL不支持。

实现分组选择，统计出前哪前两天打怪最多

SELECT d.name,c.`time`,c.`user_id`,c.`kill`,c.cnt FROM user1 d JOIN (SELECT user_id,a.time,a.kill,(SELECT COUNT(*) FROM user_kill b WHERE b.`user_id` = a.`user_id` AND a.`kill` <= b.`kill`

) AS cnt FROM user_kill a GROUP BY user_id,a.time,a.kill) c ON d.id=c.user_id AND c.cnt <=2

查询出杀怪最多的人和 日期

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

SQLserver/Oracle/Pgsql 中的优化方式

分组选择.

查询方法化，（使用分类聚合方法查询）方法2不适用，运行报错，方法3复杂但是运行结果最优

使查询防范使用存在的缺陷

select a.user_name,b.timestr,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

@MySQL---实现分组选择
分类聚合方式查询每一个用户某一个字段数据最大的两条数据：

SELECT d.user_name,c.ctimestr,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) AS c 
JOIN user1 d ON c.user_id=d.id 
WHERE cnt<=2;

没看懂的SQL，改天研究

SELECT d.name,c.time,kills

FROM (SELECT user_id,time,kills,(select count(*) from killsta b where 

b.user_id=a.id and a.kills < b.kills) as cnt from killsta a GROUP BY

user_id,time,kills) c JOIN users d on c.user_id = d.id

where cnt <1/*分类聚合 一次查询每个个体杀怪人数最多的前两天*/

SELECT a.id,b.time,b.kills

FROM users a JOIN killsta b

on a.id=b.user_id

where `name` = '孙悟空'

ORDER BY b.kills ASC LIMIT 2/*ASC 按照升序输出 DESC 按照降序输出*/

分组查找前两条记录

round_num方式优化mysql不适用

分类聚合

JOIN优化子查询