I'm writing an ecommerce app and im writing a script to track the sales of items according to year/month.
我正在编写一个电子商务应用程序,我正在编写一个脚本来跟踪按年/月的项目销售情况。
I used this query here to get the distinct year-month dates from the table.
我在这里使用此查询来从表中获取不同的年月日期。
SELECT DISTINCT CONCAT(YEAR(date),'-',MONTH(date)) FROM product_purchases WHERE product_id = 1
Which would give me an output like so
这会给我一个像这样的输出
2017-11
2017-12
What im trying to accomplish next is to select data that match that year and month, for example 2017-11.
我接下来想要完成的是选择与该年和月相匹配的数据,例如2017-11。
I've tried this query which returned 0 rows
我试过这个返回0行的查询
SELECT * FROM product_purchases WHERE DATE(date) = '2017-12'
What would be the right way to go about doing this?
这样做的正确方法是什么?
7 个解决方案
#1
2
To find records of some month, update your where clause to :
要查找某个月的记录,请将where子句更新为:
where CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'
CONCAT(年(日期),' - ',月(日期))='2017-12'
#2
3
Replace your where statement with this
用这个替换你的where语句
CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'.
i.e.
SELECT * FROM product_purchases WHERE CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'
You can do this ultimately.
你最终可以做到这一点。
Select * from (SELECT DISTINCT CONCAT(YEAR(date),'-',MONTH(date)) as NewDate,Product_Id,
Product_Name FROM product_purchases WHERE product_id = 1) where NewDate='2017-12'
#3
2
DATE(date) will generate the full date format, which is not equivalent to '2017-12'. Try this instead:
DATE(日期)将生成完整日期格式,该格式不等于“2017-12”。试试这个:
WHERE CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'
Or this:
WHERE YEAR(date) = 2017 AND MONTH(date) = 12
#4
1
Of course your query returns 0 row. Problem comes with your condition. It should be:
当然,您的查询返回0行。问题伴随着你的病情。它应该是:
SELECT * FROM product_purchases WHERE YEAR(date) = '2017' AND MONTH(date) = '12'
#5
1
If you want data just to filter by year and month, use date_format on date column to match with desired year and month.
如果您希望数据仅按年和月过滤,请在日期列上使用date_format以匹配所需的年份和月份。
Example:
where date_format( date_column, '%Y-%m' ) = '2017-12'
If you want to summarize data by year and month, use group by on the format.
如果要按年和月汇总数据,请在格式上使用group by。
Example:
group by date_format( date_column, '%Y-%m' )
#6
1
SELECT * FROM oc_customer WHERE MONTH(date_added) = 1 AND YEAR(date_added) = 2016
SELECT * FROM oc_customer WHERE MONTH(date_added)= 1 AND YEAR(date_added)= 2016
#7
0
This will hepl you
这将是你的意思
WHERE YEAR(date) = 2017 AND MONTH(date) = 12
#1
2
To find records of some month, update your where clause to :
要查找某个月的记录,请将where子句更新为:
where CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'
CONCAT(年(日期),' - ',月(日期))='2017-12'
#2
3
Replace your where statement with this
用这个替换你的where语句
CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'.
i.e.
SELECT * FROM product_purchases WHERE CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'
You can do this ultimately.
你最终可以做到这一点。
Select * from (SELECT DISTINCT CONCAT(YEAR(date),'-',MONTH(date)) as NewDate,Product_Id,
Product_Name FROM product_purchases WHERE product_id = 1) where NewDate='2017-12'
#3
2
DATE(date) will generate the full date format, which is not equivalent to '2017-12'. Try this instead:
DATE(日期)将生成完整日期格式,该格式不等于“2017-12”。试试这个:
WHERE CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'
Or this:
WHERE YEAR(date) = 2017 AND MONTH(date) = 12
#4
1
Of course your query returns 0 row. Problem comes with your condition. It should be:
当然,您的查询返回0行。问题伴随着你的病情。它应该是:
SELECT * FROM product_purchases WHERE YEAR(date) = '2017' AND MONTH(date) = '12'
#5
1
If you want data just to filter by year and month, use date_format on date column to match with desired year and month.
如果您希望数据仅按年和月过滤,请在日期列上使用date_format以匹配所需的年份和月份。
Example:
where date_format( date_column, '%Y-%m' ) = '2017-12'
If you want to summarize data by year and month, use group by on the format.
如果要按年和月汇总数据,请在格式上使用group by。
Example:
group by date_format( date_column, '%Y-%m' )
#6
1
SELECT * FROM oc_customer WHERE MONTH(date_added) = 1 AND YEAR(date_added) = 2016
SELECT * FROM oc_customer WHERE MONTH(date_added)= 1 AND YEAR(date_added)= 2016
#7
0
This will hepl you
这将是你的意思
WHERE YEAR(date) = 2017 AND MONTH(date) = 12