【MySQL】多表查询、分组求和、并对和排序、取top n_mysql 几个表求和排序_linmengmeng_1314的博客

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
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 < test1.score)
+--------+--------+-------+
| name   | course | score |
+--------+--------+-------+
| 王五   | 语文   |    93 |
| 王五   | 数学   |    99 |
| 张三   | 英语   |    90 |
+--------+--------+-------+
这种子查询，我之前还真的没有遇到过，还能这么写，真的很神奇，有空需要多补补SQL方面的知识了。 
4.TOP N
 
查询每门课程前两名的学生以及成绩 
使用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<b.score
group by a.name, a.course, a.score
having count(b.id)<2
order by a.course, a.score desc;
+




    
--------+--------+-------+
| name   | course | score |
+--------+--------+-------+
| 王五   | 数学   |    99 |
| 张三   | 数学   |    77 |
| 张三   | 英语   |    90 |
| 王五   | 英语   |    89 |
| 王五   | 语文   |    93 |
| 李四   | 语文   |    90 |
+--------+--------+-------+
相关子查询 
select a.name, a.course, a.score from test1 a
where 2>(select count(*) from test1 where course = a.course and 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

                    查漏补缺MySQL的相关知识1. 有一个用户资金流水表（如上的sql代码），找出流水金额最多的前10个用户：2. 查询分组后的最大值，最小值所在的整行记录或者分组后的top n行的记录。3.TOP 14.TOP N5.LEFT JOIN后面的on和where的区别1. 有一个用户资金流水表（如上的sql代码），找出流水金额最多的前10个用户：create table tb_user_finan...
分为：（1）连续或不连续：dense_rank,rank 
          （2）分区或不分区：使用partition，不使用partition 
举例：student表数据（sno编号,sname姓名,course科目,score成绩） 
select * from student; 
1.  对比rank...
				一次查询抽奖活动，活动中奖数量和活动剩余库存，left join了两张它表。分别查询***中奖记录表***中对应活动的中奖人数，选择count函数，一条记录一次中奖；***活动奖品表***中对应活动的奖品剩余库存，选择sum函数，一个活动可以有多个奖品，每个奖品对应不同的库存，将库存相加。
表名打了马赛克，可以参考一下
最开始很快嗷，直接select  count中奖人数，sum 奖品库存。以为搞定，实际上数据远远超出。错误
然后单表查询了一下数据，发现对不上，只left join一个表数据不会出错，就猜
				如果你想要分组求和 MySQL 中的 1 对多 数据，可以使用 GROUP BY 和 SUM 函数。假设你有两个表，一个是订单表 orders，一个是订单明细表 order_details，订单表中的每个订单都会对应多个订单明细，那么你可以使用如下的 SQL 语句来进行分组求和：
SELECT orders.order_id, SUM(order_details.amount) AS total_amount
FROM orders
JOIN order_details ON orders.order_id = order_details.order_id
GROUP BY orders.order_id;
这个 SQL 语句会将订单表和订单明细表进行 JOIN 操作，然后根据订单 ID 进行分组，最后使用 SUM 函数对订单明细表中的金额进行求和，得到每个订单的总金额。