参考：
1、 MySQL开发技巧
2、 MySQL开发技巧2
1、如何在子查询中测试两个值
2、如何解决多属性查询问题
3、如何计算累进税类问题
子查询使用的场景
- 使用子查询可以避免由于子查询中数据产生的重复
例子：有哪些人在取经过程中打了怪
-
mysql
>
set names
'GBK';
-
Query OK,
0
rows affected (
0.00 sec)
-
-
mysql
>
select user_name
-
-
>
from user1
-
-
>
where id
in(
select user_id
from user_kills);
-
+
-----------+
-
| user_name
|
-
+
-----------+
-
| 孙悟空
|
-
| 沙僧
|
-
| 猪八戒
|
-
+
-----------+
-
3
rows
in
set (
0.01 sec)
-
-
-- 子查询的表user_kills中有重复的数据。
-
mysql
>
select
*
from user_kills;
-
+
----+---------+---------------------+-------+
-
| id
| user_id
| timestr
| kills
|
-
+
----+---------+---------------------+-------+
-
|
1
|
2
|
2013
-01
-10
00:
00:
00
|
10
|
-
|
2
|
2
|
2013
-02
-01
00:
00:
00
|
2
|
-
|
3
|
2
|
2013
-02
-05
00:
00:
00
|
12
|
-
|
4
|
4
|
2013
-01
-10
00:
00:
00
|
3
|
-
|
5
|
4
|
2013
-02
-11
00:
00:
00
|
5
|
-
|
6
|
4
|
2013
-02
-06
00:
00:
00
|
1
|
-
|
7
|
3
|
2013
-01
-11
00:
00:
00
|
20
|
-
|
8
|
3
|
2013
-02
-12
00:
00:
00
|
10
|
-
|
9
|
3
|
2013
-02
-07
00:
00:
00
|
17
|
-
+
----+---------+---------------------+-------+
-
9
rows
in
set (
0.00 sec)
-
-
-- 如果使用连接，就会有重复数据
-
-
mysql
>
select user_name
-
-
>
from user1
-
-
>
left
join user_kills
on user1.id
= user_kills.user_id;
-
+
-----------+
-
| user_name
|
-
+
-----------+
-
| 唐僧
|
-
| 孙悟空
|
-
| 孙悟空
|
-
| 孙悟空
|
-
| 沙僧
|
-
| 沙僧
|
-
| 沙僧
|
-
| 猪八戒
|
-
| 猪八戒
|
-
| 猪八戒
|
-
+
-----------+
-
10
rows
in
set (
0.00 sec)
-
-
-- 如果使用连接，就会有重复数据--》解决方法：distinct
-
-
-
mysql
>
select
distinct user_name
-
-
>
from user1
-
-
>
left
join user_kills
on user1.id
= user_kills.user_id;
-
+
-----------+
-
| user_name
|
-
+
-----------+
-
| 唐僧
|
-
| 孙悟空
|
-
| 沙僧
|
-
| 猪八戒
|
-
+
-----------+
-
4
rows
in
set (
0.01 sec)
-
-
- 使用子查询更符合语意，更好理解
如何在子查询中匹配两个值
查询出每一个取经人打怪最多的日期，并列出取经人的姓名、打怪最多的日期和打怪的数量。
-
mysql
>
-- S1-取出user_id及最多的打怪数量
-
mysql
>
select user_id,
MAX(kills)
as max_cnt
-
-
>
from user_kills
-
-
>
GROUP
BY user_id;
-
+
---------+---------+
-
| user_id
| max_cnt
|
-
+
---------+---------+
-
|
2
|
12
|
-
|
3
|
20
|
-
|
4
|
5
|
-
+
---------+---------+
-
3
rows
in
set (
0.00 sec)
-
-
mysql
>
-- S2-取经人的姓名、打怪最多的日期和打怪的数量
-
mysql
>
select a.user_name,b.timestr,kills
-
-
>
from user1 a
-
-
>
join user_kills b
on a.id
= b.user_id
-
-
>
join (
select user_id,
max(kills)
as max_cnt
-
-
>
from user_kills
-
-
>
group
by user_id
-
-
> ) c
on b.user_id
= c.user_id
and b.kills
= c.max_cnt
-
-
> ;
-
+
-----------+---------------------+-------+
-
| user_name
| timestr
| kills
|
-
+
-----------+---------------------+-------+
-
| 猪八戒
|
2013
-02
-05
00:
00:
00
|
12
|
-
| 沙僧
|
2013
-02
-11
00:
00:
00
|
5
|
-
| 孙悟空
|
2013
-01
-11
00:
00:
00
|
20
|
-
+
-----------+---------------------+-------+
-
3
rows
in
set (
0.01 sec)
-
-
mysql
>
方法2：多列过滤。
MySQL中独有的多列过滤方式
-
mysql
>
-- 查询出每一个取经人打怪最多的日期，并列出取经人的姓名、打怪最多的日期和打怪的数量。
-
mysql
>
-- 多列过滤
-
mysql
>
SELECT a.user_name,b.timestr,kills
-
-
>
from user1 a
-
-
>
join user_kills b
on a.id
=b.user_id
-
-
>
WHERE (b.user_id,b.kills)
IN (
-
-
>
SELECT user_id,
max(kills)
-
-
>
FROM user_kills
-
-
>
GROUP
BY user_id
-
-
> );
-
+
-----------+---------------------+-------+
-
| user_name
| timestr
| kills
|
-
+
-----------+---------------------+-------+
-
| 猪八戒
|
2013
-02
-05
00:
00:
00
|
12
|
-
| 沙僧
|
2013
-02
-11
00:
00:
00
|
5
|
-
| 孙悟空
|
2013
-01
-11
00:
00:
00
|
20
|
-
+
-----------+---------------------+-------+
-
3
rows
in
set (
0.00 sec)
如何解决同一属性的多值过滤
什么是同一属性的多值过滤
添加一个技能表
-
mysql
>
--
-
mysql
>
-- Table structure for table `user1_skills`
-
mysql
>
--
-
mysql
>
CREATE
TABLE user1_skills (
-
-
> id
INT UNSIGNED
NOT
NULL AUTO_INCREMENT,
-
-
> user_id
INT UNSIGNED
NOT
NULL,
-
-
> skill
VARCHAR(
10)
NULL,
-
-
> skill_level
INT UNSIGNED
NOT
NULL
DEFAULT
0,
-
-
>
PRIMARY KEY (id)
-
-
> )ENGINE
=InnoDB
DEFAULT CHARSET
=utf8;
-
Query OK,
0
rows affected (
0.01 sec)
-
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
1,
'紧箍咒',
5);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
1,
'打坐',
4);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
1,
'念经',
5);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
1,
'变化',
0);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
2,
'变化',
4);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
2,
'腾云',
3);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
2,
'浮水',
5);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
2,
'念经',
0);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
2,
'紧箍咒',
0);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
3,
'变化',
5);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
3,
'腾云',
5);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
3,
'浮水',
3);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
3,
'念经',
2);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
3,
'请神',
5);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
3,
'紧箍咒',
0);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
3,
'紧箍咒',
0);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
4,
'变化',
2);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
4,
'腾云',
2);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
4,
'浮水',
4);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
4,
'念经',
1);
-
INSERT
INTO user1_skills (user_id,skill,skill_level)
VALUES(
4,
'紧箍咒',
0);
如何查询出同时具有变化和念经这两项技能的取经人？
-
-
-
mysql
>
select a.user_name,b.skill,b.skill_level
-
-
>
from user1 a
-
-
>
join user1_skills b
on a.id
= b.user_id
-
-
>
where skill
in(
'变化',
'念经')
and skill_level
>
0;
-
+
-----------+-------+-------------+
-
| user_name
| skill
| skill_level
|
-
+
-----------+-------+-------------+
-
| 唐僧
| 念经
|
5
|
-
| 猪八戒
| 变化
|
4
|
-
| 孙悟空
| 变化
|
5
|
-
| 孙悟空
| 念经
|
2
|
-
| 沙僧
| 变化
|
2
|
-
| 沙僧
| 念经
|
1
|
-
+
-----------+-------+-------------+
-
6
rows
in
set (
0.00 sec)
-
-
-- 使用join
-
-
mysql
>
select a.user_name,b.skill,c.skill
-
-
>
from user1 a
-
-
>
join user1_skills b
on a.id
= b.user_id
and b.skill
=
'念经'
-
-
>
join user1_skills c
on b.user_id
= c.user_id
and c.skill
=
'变化'
-
-
>
where b.skill_level
>
0
and c.skill_level
>
0;
-
+
-----------+-------+-------+
-
| user_name
| skill
| skill
|
-
+
-----------+-------+-------+
-
| 孙悟空
| 念经
| 变化
|
-
| 沙僧
| 念经
| 变化
|
-
+
-----------+-------+-------+
-
2
rows
in
set (
0.00 sec)
-
-
-
mysql
>
-- 如何查询出同时具有变化和念经、腾云，这3项技能的取经人？
-
mysql
>
-- 使用join
-
mysql
>
select a.user_name,b.skill,c.skill,d.skill
-
-
>
from user1 a
-
-
>
join user1_skills b
on a.id
= b.user_id
and b.skill
=
'念经'
-
-
>
join user1_skills c
on b.user_id
= c.user_id
and c.skill
=
'变化'
-
-
>
join user1_skills d
on c.user_id
= d.user_id
and d.skill
=
'腾云'
-
-
>
where b.skill_level
>
0
and c.skill_level
>
0
and d.skill_level
>
0;
-
+
-----------+-------+-------+-------+
-
| user_name
| skill
| skill
| skill
|
-
+
-----------+-------+-------+-------+
-
| 孙悟空
| 念经
| 变化
| 腾云
|
-
| 沙僧
| 念经
| 变化
| 腾云
|
-
+
-----------+-------+-------+-------+
-
2
rows
in
set (
0.00 sec)
-
改进，使用LEFT JOIN代替 JOIN
-
mysql
>
-- 如何查询出同时具有变化和念经、腾云，这3项技能的取经人？
-
mysql
>
-- 改进：使用left join
-
mysql
>
select a.user_name,b.skill,c.skill,d.skill,e.skill
-
-
>
from user1 a
-
-
>
left
join user1_skills b
on a.id
= b.user_id
and b.skill
=
'念经'
and b.skill_level
>
0
-
-
>
left
join user1_skills c
on a.id
= c.user_id
and c.skill
=
'变化'
and c.skill_level
>
0
-
-
>
left
join user1_skills d
on a.id
= d.user_id
and d.skill
=
'腾云'
and d.skill_level
>
0
-
-
>
left
join user1_skills e
on a.id
= e.user_id
and e.skill
=
'浮水'
and e.skill_level
>
0
-
-
> ;
-
+
-----------+-------+-------+-------+-------+
-
| user_name
| skill
| skill
| skill
| skill
|
-
+
-----------+-------+-------+-------+-------+
-
| 唐僧
| 念经
|
NULL
|
NULL
|
NULL
|
-
| 孙悟空
| 念经
| 变化
| 腾云
| 浮水
|
-
| 沙僧
| 念经
| 变化
| 腾云
| 浮水
|
-
| 猪八戒
|
NULL
| 变化
| 腾云
| 浮水
|
-
+
-----------+-------+-------+-------+-------+
-
4
rows
in
set (
0.00 sec)
是拥有两种及以上技能？
-
mysql
>
-- 是拥有两种及以上技能？
-
mysql
>
select a.user_name,b.skill,c.skill,d.skill,e.skill
-
-
>
from user1 a
-
-
>
left
join user1_skills b
on a.id
= b.user_id
and b.skill
=
'念经'
and b.skill_level
>
0
-
-
>
left
join user1_skills c
on a.id
= c.user_id
and c.skill
=
'变化'
and c.skill_level
>
0
-
-
>
left
join user1_skills d
on a.id
= d.user_id
and d.skill
=
'腾云'
and d.skill_level
>
0
-
-
>
left
join user1_skills e
on a.id
= e.user_id
and e.skill
=
'浮水'
and e.skill_level
>
0
-
-
>
where (
case
when b.skill
is
not
null
then
1
else
0
end)
-
-
>
+ (
case
when c.skill
is
not
null
then
1
else
0
end)
-
-
>
+ (
case
when d.skill
is
not
null
then
1
else
0
end)
-
-
>
+ (
case
when e.skill
is
not
null
then
1
else
0
end)
>=
2
-
-
> ;
-
+
-----------+-------+-------+-------+-------+
-
| user_name
| skill
| skill
| skill
| skill
|
-
+
-----------+-------+-------+-------+-------+
-
| 孙悟空
| 念经
| 变化
| 腾云
| 浮水
|
-
| 沙僧
| 念经
| 变化
| 腾云
| 浮水
|
-
| 猪八戒
|
NULL
| 变化
| 腾云
| 浮水
|
-
+
-----------+-------+-------+-------+-------+
-
3
rows
in
set (
0.00 sec)
使用GROUP方法解决问题
-
mysql
>
-- 使用GROUP方法解决问题
-
mysql
>
select a.user_name
-
-
>
from user1 a
-
-
>
join user1_skills b
on a.id
= b.user_id
-
-
>
where b.skill
in(
'念经',
'变化',
'腾云',
'浮水')
and b.skill_level
>
0
-
-
>
GROUP
By a.user_name
-
-
>
HAVING
count(
*)
>=
2
-
-
> ;
-
+
-----------+
-
| user_name
|
-
+
-----------+
-
| 孙悟空
|
-
| 沙僧
|
-
| 猪八戒
|
-
+
-----------+
-
3
rows
in
set (
0.00 sec)
如何计算累进税类问题
什么是累进税？——最常见：个人所得税
| 全月应纳税所得额 | 税率 | 速算扣除数（元） |
|---|---|---|
| 全月应纳税所得额不超过1500元 | 3% | 0 |
| 全月应纳税所得额超过1500元至4500元 | 10% | 105 |
| 全月应纳税所得额超过4500元至9000元 | 20% | 555 |
| 全月应纳税所得额超过9000元至35000元 | 25% | 1005 |
| 全月应纳税所得额超过35000元至55000元 | 30% | 2755 |
| 全月应纳税所得额超过55000元至80000元 | 35% | 5505 |
| 全月应纳税所得额超过80000元 | 45% | 13505 |
-
mysql
>
ALTER
TABLE user1
ADD money
float
NULL;
-
Query OK,
4
rows affected (
0.03 sec)
-
Records:
4 Duplicates:
0 Warnings:
0
-
-
mysql
>
desc user1;
-
+
-----------+------------------+------+-----+---------+----------------+
-
| Field
| Type
|
Null
| Key
|
Default
| Extra
|
-
+
-----------+------------------+------+-----+---------+----------------+
-
| id
|
int(
10) unsigned
|
NO
| PRI
|
NULL
| auto_increment
|
-
| user_name
|
varchar(
30)
|
NO
| MUL
|
NULL
|
|
-
|
over
|
varchar(
50)
| YES
|
|
NULL
|
|
-
| mobile
|
varchar(
100)
| YES
|
|
NULL
|
|
-
| money
|
float
| YES
|
|
NULL
|
|
-
+
-----------+------------------+------+-----+---------+----------------+
-
5
rows
in
set (
0.01 sec)
-
-
-- 更新 user1表的money字段
-
update user1
set money
=
35000
where id
=
1;
-
update user1
set money
=
15000
where id
=
2;
-
update user1
set money
=
28000
where id
=
3;
-
update user1
set money
=
8000
where id
=
4;
-
-
--
-
-- Table structure for table `taxRate`
-
--
-
CREATE
TABLE taxRate (
-
id
INT UNSIGNED
NOT
NULL AUTO_INCREMENT,
-
low
float
NOT
NULL,
-
high
float
NOT
NULL,
-
rate
float
NOT
NULL,
-
tax_money
float
NULL,
-
PRIMARY KEY (id)
-
)ENGINE
=InnoDB
DEFAULT CHARSET
=utf8;
-
-
-
-- 插入 taxRate
-
insert taxRate (low,high,rate)
VALUES(
0,
1500,
0.03);
-
insert taxRate (low,high,rate)
VALUES(
1500,
4500,
0.10);
-
insert taxRate (low,high,rate)
VALUES(
4500,
9000,
0.20);
-
insert taxRate (low,high,rate)
VALUES(
9000,
35000,
0.25);
-
insert taxRate (low,high,rate)
VALUES(
35000,
55000,
0.30);
-
insert taxRate (low,high,rate)
VALUES(
55000,
80000,
0.35);
-
insert taxRate (low,high,rate)
VALUES(
80000,
999999999.00,
0.45);
-
-
-
-
mysql
>
select user_name,money
from user1;
-
+
-----------+-------+
-
| user_name
| money
|
-
+
-----------+-------+
-
| 唐僧
|
35000
|
-
| 猪八戒
|
15000
|
-
| 孙悟空
|
28000
|
-
| 沙僧
|
8000
|
-
+
-----------+-------+
-
4
rows
in
set (
0.00 sec)
-
-
mysql
>
select
*
from taxRate;
-
+
----+-------+------------+------+-----------+
-
| id
| low
| high
| rate
| tax_money
|
-
+
----+-------+------------+------+-----------+
-
|
1
|
0
|
1500
|
0.03
|
NULL
|
-
|
2
|
1500
|
4500
|
0.1
|
NULL
|
-
|
3
|
4500
|
9000
|
0.2
|
NULL
|
-
|
4
|
9000
|
35000
|
0.25
|
NULL
|
-
|
5
|
35000
|
55000
|
0.3
|
NULL
|
-
|
6
|
55000
|
80000
|
0.35
|
NULL
|
-
|
7
|
80000
|
1000000000
|
0.45
|
NULL
|
-
+
----+-------+------------+------+-----------+
-
7
rows
in
set (
0.00 sec)
使用JON实现工资对不同纳税区间的匹配
-
mysql
>
-- 查询税类
-
mysql
>
select a.user_name,money,low,high,rate
-
-
>
from user1 a
-
-
>
join taxRate b
on a.money
> b.low
-
-
>
order
by a.user_name,b.id
-
-
>
-
-
> ;
-
+
-----------+-------+------+-------+------+
-
| user_name
| money
| low
| high
| rate
|
-
+
-----------+-------+------+-------+------+
-
| 唐僧
|
35000
|
0
|
1500
|
0.03
|
-
| 唐僧
|
35000
|
1500
|
4500
|
0.1
|
-
| 唐僧
|
35000
|
4500
|
9000
|
0.2
|
-
| 唐僧
|
35000
|
9000
|
35000
|
0.25
|
-
| 孙悟空
|
28000
|
0
|
1500
|
0.03
|
-
| 孙悟空
|
28000
|
1500
|
4500
|
0.1
|
-
| 孙悟空
|
28000
|
4500
|
9000
|
0.2
|
-
| 孙悟空
|
28000
|
9000
|
35000
|
0.25
|
-
| 沙僧
|
8000
|
0
|
1500
|
0.03
|
-
| 沙僧
|
8000
|
1500
|
4500
|
0.1
|
-
| 沙僧
|
8000
|
4500
|
9000
|
0.2
|
-
| 猪八戒
|
15000
|
0
|
1500
|
0.03
|
-
| 猪八戒
|
15000
|
1500
|
4500
|
0.1
|
-
| 猪八戒
|
15000
|
4500
|
9000
|
0.2
|
-
| 猪八戒
|
15000
|
9000
|
35000
|
0.25
|
-
+
-----------+-------+------+-------+------+
-
15
rows
in
set (
0.00 sec)
-
mysql
>
-- 查询税类区间
-
mysql
>
select a.user_name,money,low,high,least(money
-low,high
-low)
as curmoney,rate
-
-
>
from user1 a
-
-
>
join taxRate b
on a.money
> b.low
-
-
>
order
by a.user_name,b.id
-
-
> ;
-
+
-----------+-------+------+-------+----------+------+
-
| user_name
| money
| low
| high
| curmoney
| rate
|
-
+
-----------+-------+------+-------+----------+------+
-
| 唐僧
|
35000
|
0
|
1500
|
1500
|
0.03
|
-
| 唐僧
|
35000
|
1500
|
4500
|
3000
|
0.1
|
-
| 唐僧
|
35000
|
4500
|
9000
|
4500
|
0.2
|
-
| 唐僧
|
35000
|
9000
|
35000
|
26000
|
0.25
|
-
| 孙悟空
|
28000
|
0
|
1500
|
1500
|
0.03
|
-
| 孙悟空
|
28000
|
1500
|
4500
|
3000
|
0.1
|
-
| 孙悟空
|
28000
|
4500
|
9000
|
4500
|
0.2
|
-
| 孙悟空
|
28000
|
9000
|
35000
|
19000
|
0.25
|
-
| 沙僧
|
8000
|
0
|
1500
|
1500
|
0.03
|
-
| 沙僧
|
8000
|
1500
|
4500
|
3000
|
0.1
|
-
| 沙僧
|
8000
|
4500
|
9000
|
3500
|
0.2
|
-
| 猪八戒
|
15000
|
0
|
1500
|
1500
|
0.03
|
-
| 猪八戒
|
15000
|
1500
|
4500
|
3000
|
0.1
|
-
| 猪八戒
|
15000
|
4500
|
9000
|
4500
|
0.2
|
-
| 猪八戒
|
15000
|
9000
|
35000
|
6000
|
0.25
|
-
+
-----------+-------+------+-------+----------+------+
-
15
rows
in
set (
0.00 sec)
-
-
-
mysql
>
-- 查询每人税类总额
-
mysql
>
select user_name,
sum(curmoney
*rate)
-
-
>
from(
-
-
>
select a.user_name,money,low,high,least(money
-low,high
-low)
as curmoney,rate
-
-
>
from user1 a
-
-
>
join taxRate b
on a.money
> b.low
-
-
> ) a
-
-
>
GROUP
BY user_name
-
-
> ;
-
+
-----------+--------------------+
-
| user_name
|
sum(curmoney
*rate)
|
-
+
-----------+--------------------+
-
| 唐僧
|
7745.000016875565
|
-
| 孙悟空
|
5995.000016875565
|
-
| 沙僧
|
1045.0000138953328
|
-
| 猪八戒
|
2745.000016875565
|
-
+
-----------+--------------------+
-
4
rows
in
set (
0.00 sec)
2796
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
