如何显示mysql特定月份的数据？

Question

​

$query = mysql_query(
         "SELECT id, BuyerName,BuyerEmail,TransactionID,DateTime FROM `order`
         WHERE DateTime="2015 AND 08""; LIMIT $start, $per_page"
         ) 
or die(mysql_error());

​

我试图通过查询来显示特定的月份和年份。

Answer 1

您可以使用MySQL的内置YEAR和MONTH函数，如：

SELECT `id`, `BuyerName`,`BuyerEmail`,`TransactionID`,`DateTime`   
FROM `order` WHERE YEAR(DATE(`DateTime`))=2015 AND MONTH(DATE(`DateTime`)) = 8

Answer 2

如果您的列具有date的数据类型，则可以以Sargable的方式为您的条件使用日期范围。

SELECT id, BuyerName,BuyerEmail,TransactionID,DateTime
 FROM `order` 
WHERE DateTime >='01-08-2015' 
AND  DateTime <= '31-08-2015'

对于datetime，您可以将它写成

SELECT id, BuyerName,BuyerEmail,TransactionID,DateTime
 FROM `order` 
WHERE DateTime >='01-08-2015 00:00:00' 
AND  DateTime <= '31-08-2015 23:59:59'

Answer 3

如果列的类型为datetime，则尝试如下

$query = mysql_query("
    SELECT id, BuyerName, BuyerEmail, TransactionID, `DateTime` 
    FROM `order` 
    WHERE YEAR(DATE(`DateTime`)) = 2015 AND MONTH(DATE(`DateTime`)) = 8
    LIMIT $start, $per_page
") or die(mysql_error());

或者简单的

$query = mysql_query("
    SELECT id, BuyerName, BuyerEmail, TransactionID, `DateTime` 
    FROM `order` 
    WHERE `DateTime` LIKE '2015-08-%'
    LIMIT $start, $per_page
") or die(mysql_error());