【MySQL】多表查询、分组求和、并对和排序、取topn 查漏补缺MySQL的相关知识
1. 有⼀个⽤户资⾦流⽔表（如上的sql代码），出流⽔⾦额最多的前10个⽤户：
create table tb_user_finance (
id bigint primary key auto_increment,
uid bigint not null default0comment'⽤户id',
money decimal(10,2)not null default0.00comment'资⾦流⽔',
type tinyint not null default0comment'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 limit10;
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=10DEFAULT 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
urse = b.course and a.score = b.score;
+--------+--------+-------+
| name | course | score |
sql中union多表合并+--------+--------+-------+
|王五|语⽂|93|
|王五|数学|99|
|张三|英语|90|
+--------+--------+-------+
2、使⽤相关⼦查询
select name, course, score from test1 a
where score=(select max(score)from test1 urse = urse);
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
|王五|语⽂|93|
|王五|数学|99|
|张三|英语|90|
+--------+--------+-------+
这个有点不理解，where条件：score = （select max(score) from test1 urse = urse），我想这个应该是返回的每⼀科⽬的最⼤值，但是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(select1from test1 urse = urse and a.score < test1.score)
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
|王五|语⽂|93|
|王五|数学|99|
|张三|英语|90|
+--------+--------+-------+
这种⼦查询，我之前还真的没有遇到过，还能这么写，真的很神奇，有空需要多补补SQL⽅⾯的知识了。
4.TOP N
N>=1
查询每门课程前两名的学⽣以及成绩
1. 使⽤union all
如果结果集⽐较⼩，可以⽤程序查询单个分组结果后拼凑，也可以使⽤union all
(select name, course, score from test1 where course='语⽂'order by score desc limit2)
union all
(select name, course, score from test1 where course='数学'order by score desc limit2)
union all
(select name, course, score from test1 where course='英语'order by score desc limit2);
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
|王五|语⽂|93|
|李四|语⽂|90|
|王五|数学|99|
|张三|数学|77|
|张三|英语|90|
|王五|英语|89|
+--------+--------+-------+
2. ⾃⾝左连接
select a.name, a.course, a.score from test1 a
left join test1 b
urse = b.course and a.score<b.score
group by a.name, a.course, a.score
having count(b.id)<2
order urse, a.score desc;
+--------+--------+-------+
| name | course | score |
+--------+--------+-------+
|王五|数学|99|
|张三|数学|77|
|张三|英语|90|
|王五|英语|89|
|王五|语⽂|93|
|李四|语⽂|90|
+--------+--------+-------+
3. 相关⼦查询
select a.name, a.course, a.score from test1 a
where2>(select count(*)from test1 where course = a.course and score > a.score) order urse, 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=5DEFAULT 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
看运⾏结果就很明显的看出来区别了：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论