mysql中rollup和limit的用法及where子句的优化

mysql中rollup和limit的用法及where子句的优化

1. 使用ROLLUP的分组

比如说某公司某个月每个营业员的销售额 sell表，其结构如下：

create table sell

(

sale_id int auto_increment, 

sale_name varchar(10),

sale decimal(8,2),

primary key(sale_id)

);

Insert into sell

values(100000,'张三',500);

Insert into sell

values(null,'李四',300.56);

Insert into sell

values(null,'王五',350.56);

insert into sell

values(null,'李四',200.68);

Insert into sell

values(null,'王五',700.56);

insert into sell

values(null,'张三',800.56);

表记录如下：

mysql> select sale_id as 销售号,sale_name as 营业员姓名,sale as 销售额 from sell;

+--------+------------------+------------+

| 销售号 | 营业员姓名 | 销售额 |

+--------+------------------+------------+

| 100000 | 张三       | 500.00   |

| 100001 | 李四       | 300.56   |

| 100002 | 王五       | 350.56   |

| 100003 | 李四       | 200.68   |

| 100004 | 王五       | 700.56   |

| 100005 | 张三       | 800.56   |

+--------+------------------+------------+

下面的问题是：我们要查询每个营业员的总营业额，及所有营业员的总销售额？

方法1.

select sale_name as 营业员姓名,sum(sale) as 总营业额

from sell

group by sale_name

Union

select null,sum(sale)

from sell;

查询结果如下：

+-----------------+------------+

| 营业员姓名 | 总营业额 |

+-----------------+------------+

| 李四       |   501.24 |

| 王五       |  1051.12 |

| 张三       |  1300.56 |

| NULL      |  2852.92 |

+----------------+-------------+

方法2:

这里用到的便是MySQL中rollup 分组,它是通过为group by 子句添加 with rollup来简化方法1  如下：

select sale_name as 营业员姓名,sum(sale) as 总营业额

from sell

group by sale_name with rollup;

查询结果与上面的一模一样。

2. select 语句中的limit 子句

比如我们对上面的sell 表：查询出销售额最大的三笔?

方法1:

select sale_id  as 销售号, sale as 销售额 from sell as s1

Where 3 >

(

select count(*) from sell as s2 

Where s1.sale < s2.sale

)

Order by sale DESC;

查询结果如下：

+---------+--------+

| 销售号 | 销售额 |

+---------+--------+

| 100005 | 800.56 |

| 100004 | 700.56 |

| 100000 | 500.00 |

+--------+----------+

方法2:

这里利用MySQL中的limit 如下：

Select sale_id  as 销售号, sale as 销售额 from sell 

Order by sale DESC

LIMIT 3;

查询结果与方法1 一模一样。

关于limit 的一点补充：带有偏移量的limit

比如上面的sell 表中，我要从除了最大三笔销售额后，查询出最大的两笔销售额？

方法:

Select sale_id  as 销售号, sale as 销售额 from sell 

Order by sale DESC

LIMIT  2  OFFSET  3;

查询结果：

+---------+--------+

| 销售号 | 销售额 |

+---------+--------+

| 100002 | 350.56 |

| 100001 | 300.56 |

+---------+--------+

3.优化where 子句

 

此部分讨论的优化where子句适用于 select ，同样也适用于delete 、update中的where子句。

（1）去掉不必要的括号

例如下面的where子句中的条件

 ((a AND b) AND c OR (((a AND b) AND (c AND d))))

优化为：

     (a AND b AND c) OR (a AND b AND c AND d)

（2）常量重叠

例如下面的where子句中的条件

    (a<b AND b=c) AND a=5

优化为：

    b>5 AND b=c AND a=5