【MySQL】多表查询、分组求和、并对和排序、取top n

标签：test1 product 多表 top course score MySQL NULL id

查漏补缺MySQL的相关知识

1. 有一个用户资金流水表（如上的sql代码），找出流水金额最多的前10个用户：
2. 查询分组后的最大值，最小值所在的整行记录或者分组后的top n行的记录。
3.TOP 1
4.TOP N
5.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 < test1.score)

+--------+--------+-------+
| name   | course | score |
+--------+--------+-------+
| 王五   | 语文   |    93 |
| 王五   | 数学   |    99 |
| 张三   | 英语   |    90 |
+--------+--------+-------+

这种子查询，我之前还真的没有遇到过，还能这么写，真的很神奇，有空需要多补补SQL方面的知识了。

4.TOP N

N>=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<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条件加不加都是这个结果

标签：test1,product,多表,top,course,score,MySQL,NULL,id
From： https://blog.51cto.com/linmengmeng/5907334