生活在別處

AVG()

mysql> SELECT * FROM tbl_1;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  2 |    1 | a    |
|  4 |    2 | a    |
|  6 |    3 | b    |
|  8 |    4 | c    |
+----+------+------+
4 rows in set (0.00 sec)

mysql> SELECT AVG(a) FROM tbl_1;
+--------+
| AVG(a) |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)

COUNT()

mysql> SELECT COUNT(*) FROM tbl_1 WHERE b = 'a';
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

MAX() 返回数据集里最大值

mysql> SELECT MAX(a) FROM tbl_1;
+--------+
| MAX(a) |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

MIN() 返回数据集里最小值

mysql> SELECT MIN(a) FROM tbl_1;
+--------+
| MIN(a) |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

SUM() 求和

mysql> SELECT SUM(a) FROM tbl_1;
+--------+
| SUM(a) |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

ABS() OR ABSVAL() 计算绝对值

mysql> SELECT ABS(a) FROM tbl_1;
+--------+
| ABS(a) |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
+--------+
4 rows in set (0.00 sec)

CEILING()

mysql> SELECT CEILING(1.1), CEILING(1.5), CEILING(-1.1), CEILING(-1.5);
+--------------+--------------+---------------+---------------+
| CEILING(1.1) | CEILING(1.5) | CEILING(-1.1) | CEILING(-1.5) |
+--------------+--------------+---------------+---------------+
|            2 |            2 |            -1 |            -1 |
+--------------+--------------+---------------+---------------+
1 row in set (0.00 sec)

ROUND() 四舍五入

mysql> SELECT ROUND(111.111, 1), ROUND(111.111, 2), ROUND(111.111, 3), ROUND(111.111, 4), ROUND(111.111, 5), ROUND(111.111, 0), ROUND(111.111, -1), ROUND(111.111, -2), ROUND(111.111, -3)\G
*************************** 1. row ***************************
 ROUND(111.111, 1): 111.1
 ROUND(111.111, 2): 111.11
 ROUND(111.111, 3): 111.111
 ROUND(111.111, 4): 111.1110
 ROUND(111.111, 5): 111.11100
 ROUND(111.111, 0): 111
 ROUND(111.111, -1): 110
 ROUND(111.111, -2): 100
 ROUND(111.111, -3): 0
1 row in set (0.01 sec)

mysql> SELECT ROUND(111.116, 1), ROUND(111.116, 2), ROUND(111.116, 3), ROUND(111.116, 4), ROUND(111.116, 5), ROUND(111.116, 6), ROUND(111.116, 0), ROUND(111.116, -1), ROUND(111.116, -2), ROUND(11.116, -3)\G
*************************** 1. row ***************************
 ROUND(111.116, 1): 111.1
 ROUND(111.116, 2): 111.12
 ROUND(111.116, 3): 111.116
 ROUND(111.116, 4): 111.1160
 ROUND(111.116, 5): 111.11600
 ROUND(111.116, 6): 111.116000
 ROUND(111.116, 0): 111
 ROUND(111.116, -1): 110
 ROUND(111.116, -2): 100
 ROUND(11.116, -3): 0
1 row in set (0.00 sec)

CURTIME() 返回系统时间

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 13:40:30  |
+-----------+
1 row in set (0.00 sec)

CURDATE() 返回系统日期

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2009-08-03 |
+------------+
1 row in set (0.00 sec)

DATE()

mysql> SELECT DATE('2009-08-03');
+--------------------+
| DATE('2009-08-03') |
+--------------------+
| 2009-08-03         |
+--------------------+
1 row in set (0.00 sec)

DAY() 返回日期的日部分

mysql> SELECT * FROM tbl_2;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2009-08-03 00:00:00 | 2009-07-03 00:00:00 |
| 2009-08-03 00:00:00 | 2009-07-01 00:00:00 |
| 2009-08-08 00:00:00 | 2009-07-01 00:00:00 |
| 2009-08-09 00:00:00 | 2009-07-10 00:00:00 |
+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT DAY(a), DAY(b) FROM tbl_2;
+--------+--------+
| DAY(a) | DAY(b) |
+--------+--------+
|      3 |      3 |
|      3 |      1 |
|      8 |      1 |
|      9 |     10 |
+--------+--------+
4 rows in set (0.00 sec)

DAYOFMONTH() 返回参数日部分

mysql> SELECT DAYOFMONTH(a) FROM tbl_2;
+---------------+
| DAYOFMONTH(a) |
+---------------+
|             3 |
|             3 |
|             8 |
|             9 |
+---------------+
4 rows in set (0.00 sec)

DAYOFWEEK() 返回参数的星期值1~7，1-星期日；7-星期六

mysql> SELECT DAYOFWEEK(a) FROM tbl_2;
+--------------+
| DAYOFWEEK(a) |
+--------------+
|            2 |
|            2 |
|            7 |
|            1 |
+--------------+
4 rows in set (0.00 sec)

DAYOFYEAR() 返回值1~366

mysql> SELECT DAYOFYEAR(a), DAYOFYEAR(b) FROM tbl_2;
+--------------+--------------+
| DAYOFYEAR(a) | DAYOFYEAR(b) |
+--------------+--------------+
|          215 |          184 |
|          215 |          182 |
|          220 |          182 |
|          221 |          191 |
+--------------+--------------+
4 rows in set (0.00 sec)

HOUR() 返回参数小时部分，参数为时间或时间戳类型

mysql> SELECT * FROM tbl_2;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2009-08-03 00:00:00 | 2009-07-03 00:00:00 |
| 2009-08-03 00:00:00 | 2009-07-01 00:00:00 |
| 2009-08-08 00:00:00 | 2009-07-01 00:00:00 |
| 2009-08-09 00:00:00 | 2009-07-10 00:00:00 |
| 2009-08-03 01:11:11 | 2009-08-03 02:21:12 |
+---------------------+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT HOUR(a), HOUR(b) FROM tbl_2;
+---------+---------+
| HOUR(a) | HOUR(b) |
+---------+---------+
|       0 |       0 |
|       0 |       0 |
|       0 |       0 |
|       0 |       0 |
|       1 |       2 |
+---------+---------+
5 rows in set (0.00 sec)

FROM: http://www.winmag.com.cn/forum/itemdisplay.asp?boardid=11&id=503269
T-SQL允许你使用不同的方法解决一个问题.有的时候,尽管选择不是那么明显,但是却可以让你得到令人满意的和快乐的惊奇.下边让我们解读Dr. Tom Moreau对同一问题不同的可能性的探索.可能我们可以在那些不同的方法之中发现一些珍贵的东西.

让我们以我们的老朋友Northwind数据库为例,这里我们用到的是[order details]表,这个表是一个定单的明细表,和order表是多对一的关系.也就是一个定单对应多个订购的产品.假设你想得到每个定单订购的总价值, 但是不包括59号产品.Listing 1给了我们第一种解法:

select
OrderID,sum (Quantity * UnitPrice) value
from
[Order Details] o1
where
ProductID <> 59
group by
OrderID

上边的语句很简单,它排除掉了59号产品的定单明细条目,然后进行分组统计.但是如果我们需要忽略掉订购59号产品的定单呢？也就是说我们要统计没 有包含 59号产品的定单的价值.你想到了WHERE, NOT EXIST(S)关键词了吗？Listing 2给了我们第二种方法:

select
o1.OrderID,sum (o1.Quantity * o1.UnitPrice) value
from
[Order Details] o1
where not exists
(
select
*
from
[Order Details] o2
where
o2.OrderID = o1.OrderID
and o2.ProductID = 59
)
group by
o1.OrderID

如果你不喜欢用exist的话,你可以转化成使用not in:

select
o1.OrderID,sum (o1.Quantity * o1.UnitPrice) value
from
[Order Details] o1
where 59 not in
(
select
ProductID
from
[Order Details] o2
where
o2.OrderID = o1.OrderID
)
group by
o1.OrderID

尽管Listing 1不满足我们现在的查询条件.但是从性能发面考虑,Listing 1还是最好的,因为它只用到了一次表的扫描.而后边的两个查询都是用到了相关子查询,如果你查看查询计划就回看到,他们都涉及到了两次表的扫描.如果你曾经在 T-SQL用过交叉表查询的话,你就不会对聚集函数里边的case结构陌生.现在我们就把这个非常有趣的方法应用到我们的问题中来:

select
OrderID,sum (Quantity * UnitPrice) value
from
[Order Details] o1
group by
OrderID
having
sum (case when ProductID = 59 then 1 else 0 end) = 0

HAVING子句起到了对分组的结果进行过滤的作用.如果没有包含59号产品,就会出现0=0,显然这是满足条件的.如果包含了59号产品的订购,就会出现n=0(n<>0),这样的定单就回被过滤掉.查看执行计划你就回发现是一次表的扫描,非常棒!
再来举一个例子:我们这回用到的表是order表,假设我们要统计只通过一个雇员雇员下定单的顾客.你可以想到用子查询not exist来实现:

select distinct
o1.CustomerID
from
Orders o1
where not exists
(
select
*
from
Orders o2
where
o2.CustomerID = o1.CustomerID
and o2.EmployeeID <> o1.EmployeeID
)

同样的,这个语句可以通过带有HAVING子句的分组来实现.

select
CustomerID
from
Orders
group by
CustomerID
having
min (EmployeeID) = max (EmployeeID)

另一种方法:

select
CustomerID
from
Orders
group by
CustomerID
having
count (distinct EmployeeID) = 1

Listing 6和Listing 7查询消耗都要小于Listing 5.相比Listing 5的两次表扫描,他们只进行一次表的扫描.而Listing 6的损耗还要稍微小于Listing 7.但是,Listing 7的一个显著的特点就是它可以适应到一个顾客对应两个雇员,三个雇员……