SELECT每月记录表数据库 [英] SELECT monthly record form database

问题描述

mysql> SELECT * FROM con_transactions;
+------+------+---------------------+--------+
| t_id | p_id | date                | amount |
+------+------+---------------------+--------+
|   10 |    1 | 2016-02-17 19:24:05 | 1800   |
|   12 |    2 | 2016-02-18 11:40:13 | 200    |
|   17 |    3 | 2016-02-18 11:42:04 | 100    |
|   19 |    4 | 2016-02-18 11:45:43 | 1      |
|   20 |    5 | 2016-02-18 11:45:54 | 999    |
|   21 |    1 | 2016-02-18 11:46:02 | 1500   |
|   41 |    2 | 2016-02-18 17:23:14 | 500    |
|   42 |    3 | 2016-02-18 17:23:14 | 500    |
|   43 |    4 | 2016-02-18 17:23:15 | 500    |
|   44 |    5 | 2016-02-18 17:23:16 | 500    |
|   45 |    1 | 2016-02-18 17:23:16 | 500    |
|   46 |    2 | 2016-02-18 17:23:16 | 500    |
|   47 |    3 | 2016-02-18 17:23:17 | 500    |
|   48 |    4 | 2016-02-18 17:23:17 | 500    |
|   49 |    5 | 2016-02-18 17:23:18 | 500    |
|   50 |    1 | 2016-02-18 17:25:54 | 1000   |
|   51 |    1 | 2016-02-18 17:26:22 | 3000   |
|   52 |    2 | 2016-02-18 17:48:59 | 10     |
|   53 |    1 | 2016-02-18 17:48:59 | 10     |
|   55 |    1 | 2016-02-19 10:20:12 | 1000   |
+------+------+---------------------+--------+

如何选择当前月或上个月所有交易记录。所有月份不是30天。那么我如何选择1月份或其他月份的SELECT查询记录。我尝试这个查询

HOW Can I Select current month or last month All Transactions record. All Month are not 30 days. So How can I Select January or other month record with SELECT query. I Try this query

SELECT * FROM con_transactions WHERE date > '2016-01-01 00:00:00' AND date < '2016-02-01 00:00:00';

这不是很聪明。

推荐答案

使用 month（）和 year（）直接查找任何给定的月份一年。

Use month() and year() to directly find any given month of a year.

SELECT * FROM con_transactions WHERE year(date)  = 2016 AND month(date) = 1;

这篇关于SELECT每月记录表数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！