查询指定时间范围内的订单信息

select * from order_detail limit 10;
# 输出结果为：
+----------+---------------------+---------+---------+-------+--------------+-------------+
| order_id | order_dt            | user_id | product | price | order_number | order_money |
+----------+---------------------+---------+---------+-------+--------------+-------------+
|        1 | 2011-09-01 00:00:00 |  119970 | A       |   100 |            7 |         700 |
|        2 | 2011-09-01 00:00:00 |  128236 | D       |   300 |            6 |        1800 |
|        3 | 2011-09-01 00:00:00 |  130887 | A       |   200 |           10 |        2000 |
|        4 | 2011-09-01 00:00:00 |  132368 | D       |   200 |            7 |        1400 |
|        5 | 2011-09-01 00:00:00 |  133422 | A       |   100 |           10 |        1000 |
|        6 | 2011-09-01 00:00:00 |  137401 | D       |   300 |            9 |        2700 |
|        7 | 2011-09-01 00:00:00 |  138462 | A       |   200 |            8 |        1600 |
|        8 | 2011-09-01 00:00:00 |  146853 | D       |   100 |            1 |         100 |
|        9 | 2011-09-01 00:00:00 |  149841 | A       |   200 |            9 |        1800 |
|       10 | 2011-09-01 00:00:00 |  151129 | D       |   100 |            3 |         300 |
+----------+---------------------+---------+---------+-------+--------------+-------------+

desc order_detail;
# 输出结果为：
+--------------+--------------+------+-----+---------------------+-------+
| Field        | Type         | Null | Key | Default             | Extra |
+--------------+--------------+------+-----+---------------------+-------+
| order_id     | int(11)      | NO   | PRI | NULL                |       |
| order_dt     | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| user_id      | mediumint(9) | NO   |     | 0                   |       |
| product      | varchar(20)  | NO   |     |                     |       |
| price        | mediumint(9) | NO   |     | 0                   |       |
| order_number | smallint(6)  | NO   |     | 0                   |       |
| order_money  | mediumint(9) | NO   |     | 0                   |       |
+--------------+--------------+------+-----+---------------------+-------+
# 订单明细表order_detail字段说明：
# order_id表示订单编号，order_dt表示下订单的时间，user_id表示下订单的用户，
# product表示产品种类，price表示产品单价，order_number表示订购数量，order_money表示产品的汇总金额。

假如我们要查询2011九月份内所有的订单信息，有两种解决办法：

方法一：

select * from order_detail where order_dt between '2011-09-01' and '2011-09-30';

方法二：

select * from order_detail where year(order_dt)='2009' and month(order_dt)='09';

同理，假如想要查询指定具体某年某月某日或指定某一年的订单信息也可以采用上述两种方法；假如想要查询无规律的一段时间内的订单信息，可以采用between and。

PS：本文为博主原创文章，转载请注明出处。