MySQL 使用时间戳按天分组结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2670871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Group Results by day using timestamp
提问by Webnet
I need to take the following query and pull the total order counts and sum of the orders grouped by day. I'm storing everything using timestamps.
我需要进行以下查询并提取按天分组的订单总数和订单总和。我正在使用时间戳存储所有内容。
SELECT
COUNT(id) as order_count,
SUM(price + shipping_price) as order_sum,
DAY(FROM_UNIXTIME(created)) as day
FROM `order`
WHERE '.implode(' AND ', $where).'
I need to group by DAY but when I do for this past weekend's sales it takes my order_count and makes it 1 instead of 3. How can I pull the above values grouped by day?
我需要按天分组，但是当我为上周末的销售进行分组时，它会使用我的 order_count 并将其设为 1 而不是 3。我怎样才能将上述值按天分组？
NOTE: The implode is used ONLY to define the time period (WHERE created >= TIMESTAMP AND <= TIMESTAMP)
注意：内爆仅用于定义时间段（WHERE created >= TIMESTAMP AND <= TIMESTAMP）
Update
更新
Without GROUP BY day
没有 GROUP BY day
Array (
[order_count] => 3
[order_sum] => 69.70
[day] => 17
)
With GROUP BY day
与 GROUP BY day
Array (
[order_count] => 1
[order_sum] => 24.90
[day] => 17
)
I need this query to return each day that had sales, how many orders, and the sum of those sales. I'm missing a piece of the puzzle here somewhere....
我需要这个查询来返回每天有销售额、有多少订单以及这些销售额的总和。我在某处遗漏了一块拼图......
回答by ma?ek
Are you just forgetting to add GROUP BY ...at the end?
你只是忘记GROUP BY ...在最后添加吗？
SELECT
COUNT(id) as order_count,
SUM(price + shipping_price) as order_sum,
DAY(FROM_UNIXTIME(created)) as order_day
FROM `order`
WHERE '.implode(' AND ', $where).'
GROUP BY order_day
NOTE:
笔记：
You cannot use as dayfor your day column because dayis a MySQL function. Use something like order_day.
您不能as day用于您的日期列，因为它day是一个 MySQL 函数。使用类似的东西order_day。
Of Unicorns
独角兽
Per @OMG Unicorn's comment, you can use:
根据@OMG Unicorn 的评论，您可以使用：
DAY(FROM_UNIXTIME(created)) as `day`
So long as wrap dayin `backticks.
只要day用`反引号包裹。
回答by Claus Gammelgaard
You could also simply use:
您也可以简单地使用：
SELECT
COUNT( id ) AS order_count,
SUM( price + shipping_price ) AS order_sum
FROM `order`
GROUP BY LEFT( timestamp, 10 )
or even
甚至
GROUP BY SUBSTR( timestamp, 1, 10 )
GROUP BY SUBSTR( 时间戳, 1, 10 )
回答by Hyman M.
You're probably running into a naming conflict between DAY()(the function in MySQL) and day(your variable name). Have you tried using something that wont conflict such as order_day?
您可能会遇到DAY()（MySQL 中的函数）和day（您的变量名）之间的命名冲突。您是否尝试过使用不会发生冲突的东西，例如order_day？
