我有一个简单的查询,它的意思不是从我的表中获取所有订单日期
select (orderDate, "%m-%Y")) from customerorders
但是,它不起作用。
我还尝试交换日期和月份格式:
select (orderDate, "%%Y-%m")) from customerorders
我的表中订单日期的结构采用日期格式:
'2019-01-01'
第三个查询
select COUNT(orderid) as OrderCount,
DATE_FORMAT(orderDate, "%m-%Y")) as month
FROM customerOrders
WHERE milkOptions = 'Coconut'
group by month
order by DATE_FORMAT(orderDate, "%Y")
通过DATE_FORMAT
函数这样做:
select DATE_FORMAT('2019-01-01', "%m-%Y")
或者对你来说,这将是:
select DATE_FORMAT(orderDate, "%m-%Y") from customerorders
或不同的顺序:
select DATE_FORMAT('2019-01-01', "%y-%M")
或者对你来说,这将是:
select DATE_FORMAT(orderDate, "%y-%M") from customerorders
这是一个小演示
在这里,您可以找到有关该功能(以及其他功能(的更多信息: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
更新
select COUNT(orderid) as OrderCount,
DATE_FORMAT(orderDate, "%m-%Y") as month
FROM customerOrders
WHERE milkOptions = 'Coconut'
group by month
order by DATE_FORMAT(orderDate, "%Y");
或:
select COUNT(orderid) as OrderCount
, DATE_FORMAT(orderDate, "%m-%Y") as month
FROM customerOrders
WHERE milkOptions = 'Coconut'
group by month
order by month;