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