【MySQL】多表查询、分组求和、并对和排序、取top n |
您所在的位置：网站首页 › 如何将两个表中的数据求和 › 【MySQL】多表查询、分组求和、并对和排序、取top n |
|
查漏补缺MySQL的相关知识
1. 有一个用户资金流水表（如上的sql代码），找出流水金额最多的前10个用户：2. 查询分组后的最大值，最小值所在的整行记录或者分组后的top n行的记录。3.TOP 14.TOP N5.LEFT JOIN后面的on和where的区别
1. 有一个用户资金流水表（如上的sql代码），找出流水金额最多的前10个用户：
create table tb_user_finance (
id bigint primary key auto_increment,
uid bigint not null default 0 comment '用户id',
money decimal(10, 2) not null default 0.00 comment '资金流水',
type tinyint not null default 0 comment '1: 转账, 10: 提现, 20: 充值',
created_at timestamp not null default current_timestamp,
updated_at timestamp not null default current_timestamp on update current_timestamp,
key ix_uid (uid)
) engine = innodb default charset=utf8 comment '用户资金流水表';
insert into tb_user_finance (uid, money, type) values(10, 20, 1);
insert into tb_user_finance (uid, money, type) values(10, 20, 1);
insert into tb_user_finance (uid, money, type) values(10, 20, 1);
insert into tb_user_finance (uid, money, type) values(10, 200, 1);
insert into tb_user_finance (uid, money, type) values(20, 10, 10);
insert into tb_user_finance (uid, money, type) values(30, 20, 20);
insert into tb_user_finance (uid, money, type) values(30, 10, 20);
insert into tb_user_finance (uid, money, type) values(31, 10, 20);
insert into tb_user_finance (uid, money, type) values(32, 20, 20);
insert into tb_user_finance (uid, money, type) values(33, 45, 20);
insert into tb_user_finance (uid, money, type) values(34, 100, 20);
insert into tb_user_finance (uid, money, type) values(35, 1000, 20);
insert into tb_user_finance (uid, money, type) values(36, 1090, 20);
答案： select uid, sum(money) as total from tb_user_finance group by uid order by total desc limit 10; 2. 查询分组后的最大值，最小值所在的整行记录或者分组后的top n行的记录。2.1 创建测试表： CREATE TABLE `test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `course` varchar(20) DEFAULT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;2.2 插入数据： insert into test1(name,course,score) values ('张三','语文',80), ('李四','语文',90), ('王五','语文',93), ('张三','数学',77), ('李四','数学',68), ('王五','数学',99), ('张三','英语',90), ('李四','英语',50), ('王五','英语',89);查看结果：select * from test1; +----+--------+--------+-------+ | id | name | course | score | +----+--------+--------+-------+ | 1 | 张三 | 语文 | 80 | | 2 | 李四 | 语文 | 90 | | 3 | 王五 | 语文 | 93 | | 4 | 张三 | 数学 | 77 | | 5 | 李四 | 数学 | 68 | | 6 | 王五 | 数学 | 99 | | 7 | 张三 | 英语 | 90 | | 8 | 李四 | 英语 | 50 | | 9 | 王五 | 英语 | 89 | +----+--------+--------+-------+ 3.TOP 1查询每门课程分数最高的学生以及成绩 1、使用自连接【推荐】 select a.name, a.course, a.score from test1 a join (select course, MAX(score) score from test1 group by course)b on a.course = b.course and a.score = b.score; +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 王五 | 数学 | 99 | | 张三 | 英语 | 90 | +--------+--------+-------+2、使用相关子查询 select name, course, score from test1 a where score=(select max(score) from test1 where a.course = test1.course); +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 王五 | 数学 | 99 | | 张三 | 英语 | 90 | +--------+--------+-------+这个有点不理解，where条件：score = （select max(score) from test1 where a.course = test1.course），我想这个应该是返回的每一科目的最大值，但是score使用等号连接表明后面应该是一个单独的值，但是直接运行这个语句就报错了，将括号内的where去掉的话 直接查出来的是整个表里的最大值了。 看到这里我试着自己写了一个，但是结果不对，不知道哪里出了问题，多了一行数据。 这条语句可以查出来每一科目的最高分，返回三个值： select max(score) score from test1 group by course; +-------+ | score | +-------+ | 93 | | 99 | | 90 | +-------+然后再查询一次，添加一个where条件限制score： select name, course, score from test1 where score in (select max(score) score from test1 group by course); +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 李四 | 语文 | 90 | //这一行是哪来的？？？？ | 王五 | 语文 | 93 | | 王五 | 数学 | 99 | | 张三 | 英语 | 90 | +--------+--------+-------+但是结果竟然多了一行，不知道这一行是从哪里多出来的？？？？？？这种写法我在项目中用过，但是好像没有发现这种会多一行不正确的数据的情况。 3.另一种相关子查询 select name, course, score from test1 a where not exists (select 1 from test1 where a.course = test1.course and a.score =1查询每门课程前两名的学生以及成绩 使用union all如果结果集比较小，可以用程序查询单个分组结果后拼凑，也可以使用union all (select name, course, score from test1 where course='语文' order by score desc limit 2) union all (select name, course, score from test1 where course='数学' order by score desc limit 2) union all (select name, course, score from test1 where course='英语' order by score desc limit 2); +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 李四 | 语文 | 90 | | 王五 | 数学 | 99 | | 张三 | 数学 | 77 | | 张三 | 英语 | 90 | | 王五 | 英语 | 89 | +--------+--------+-------+ 自身左连接 select a.name, a.course, a.score from test1 a left join test1 b on a.course = b.course and a.score a.score) order by a.course, a.score desc +----+--------+--------+-------+ | id | name | course | score | +----+--------+--------+-------+ | 6 | 王五 | 数学 | 99 | | 4 | 张三 | 数学 | 77 | | 7 | 张三 | 英语 | 90 | | 9 | 王五 | 英语 | 89 | | 3 | 王五 | 语文 | 93 | | 2 | 李四 | 语文 | 90 | +----+--------+--------+-------+ 5.LEFT JOIN后面的on和where的区别5.1创建测试表 CREATE TABLE `product` ( `id` int(10) unsigned NOT NULL auto_increment, `amount` int(10) unsigned default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; CREATE TABLE `product_details` ( `id` int(10) unsigned NOT NULL, `weight` int(10) unsigned default NULL, `exist` int(10) unsigned default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO product (id,amount) VALUES (1,100),(2,200),(3,300),(4,400); INSERT INTO product_details (id,weight,exist) VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);表数据： SELECT * FROM product; +----+--------+ | id | amount | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | 400 | +----+--------+ SELECT * FROM product_details; +----+--------+-------+ | id | weight | exist | +----+--------+-------+ | 2 | 22 | 0 | | 4 | 44 | 1 | | 5 | 55 | 0 | | 6 | 66 | 1 | +----+--------+-------+ select * from product a left join product_details b on a.id = b.id // 后面仍然可以添加where条件进行过滤 +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 2 | 200 | 2 | 22 | 0 | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | 4 | 44 | 1 | +----+--------+------+--------+-------+ON 子句和 WHERE 子句有什么不同？ 一个问题：下面两个查询的结果集有什么不同么？ select * from product a left join product_details b on a.id = b.id and b.id = 2 select * from product a left join product_details b on a.id = b.id where b.id = 2看运行结果就很明显的看出来区别了： SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) AND product_details.id=2; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 2 | 200 | 2 | 22 | 0 | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | NULL | NULL | NULL | +----+--------+------+--------+-------+ SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) WHERE product_details.id=2; +----+--------+----+--------+-------+ | id | amount | id | weight | exist | +----+--------+----+--------+-------+ | 2 | 200 | 2 | 22 | 0 | +----+--------+----+--------+-------+第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。 第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。 再来看一些示例，ON后面的AND条件是怎么匹配数据的： SELECT * FROM product LEFT JOIN product_details ON product.id = product_details.id AND product.amount=100; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 2 | 200 | NULL | NULL | NULL | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | NULL | NULL | NULL | +----+--------+------+--------+-------+所有来自product表的数据行都被检索到了，但没有在product_details表中匹配到记录（product.id = product_details.id AND product.amount=100 条件并没有匹配到任何数据） SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) AND product.amount=200; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 2 | 200 | 2 | 22 | 0 | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | NULL | NULL | NULL | +----+--------+------+--------+-------+同样，所有来自product表的数据行都被检索到了，有一条数据匹配到了。 AND没有WHERE那么干脆，WHERE直接过滤掉右边表中为null的数据。 使用 WHERE … IS NULL 子句的 LEFT JOIN 这个有点不太理解 如前所述，WHERE 条件查询发生在 匹配阶段之后，这意味着 WHERE … IS NULL 子句将从匹配阶段后的数据中过滤掉不满足匹配条件的数据行。 纸面上看起来很清楚，但是当你在 ON 子句中使用多个条件时就会感到困惑了。 我总结了一种简单的方式来理解上述情况： 将 IS NULL 作为否定匹配条件使用 !(A and B) == !A OR !B 逻辑判断看看下面的示例： SELECT a.* FROM product a LEFT JOIN product_details b ON a.id=b.id AND b.weight!=44 AND b.exist=0 WHERE b.id IS NULL; +----+--------+ | id | amount | +----+--------+ | 1 | 100 | | 3 | 300 | | 4 | 400 | +----+--------+ 这个我在运行时发现where条件加不加都是这个结果整理参考文章： https://blog.csdn.net/jiaobuchong/article/details/79617342 https://www.cnblogs.com/zjfjava/p/6041445.html |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |