MySQL- GROUP 和 COUNT 按日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5407322/
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 and COUNT by date
提问by teustis
I am trying to figure out the proper syntax for getting query result I need. Here is the scenario:
我试图找出获取我需要的查询结果的正确语法。这是场景：
I am designing a SaaS application and have a table called trips, like so:
我正在设计一个 SaaS 应用程序，并有一个名为 trips 的表，如下所示：
table.trips
- trip_id (pk, ai)
- client_id (fk)
- shop_id (fk)
- trip_date (date)
I need to be able to get the number of trips in the table for each day (by shop_id) and then deliver the number of TRIPS per trip_date, like:
我需要能够获得表中每天的旅行次数（通过 shop_id），然后提供每个 trip_date 的 TRIPS 数量，例如：
DATE | TRIP SUM
--------------------------
01-01-2011 | 3
01-02-2011 | 2
01-03-2011 | 5
Any thoughts on the proper syntax?
关于正确语法的任何想法？
回答by Duniyadnd
SELECT COUNT(*), trip_date , shop_id
FROM trips
GROUP BY trip_date , shop_id
回答by Mikael ?stberg
Something like this?
像这样的东西？
select
DATE(trip_date) as [DATE],
count(1) as [TRIP SUM]
from
trips
group by
DATE(trip_date), shop_id
I'm not a mySQL guy, but you will need to use a T-SQL DatePart equivalent, where I have used the DATE()function in order to strip the time portion of the date.
我不是 mySQL 专家，但您将需要使用 T-SQL DatePart 等效项，其中我使用了该DATE()函数以去除日期的时间部分。
回答by Luis Alberto Juárez Aguirre
If the date column also stores hour and minute you will need to apply a substring function to the date, to only have the yyyy-mm-ddpart of the date column, like this:
如果日期列还存储小时和分钟，您将需要对日期应用子字符串函数，以便只有日期列的yyyy-mm-dd部分，如下所示：
SELECT COUNT(*), substring(trip_date,1,10)
FROM trips
GROUP BY substring(trip_date,1,10);
回答by manji
SELECT shop_id, trip_date, count(trip_id)
FROM trips
GROUP BY shop_id, trip_date
回答by Dumitrescu Bogdan
select trip_date,count(*)
from trips
where shop_id=[the shop id]
group by trip_date
This will work as long as the date is on the format you showed. If it has also time then you have to remove the time.
只要日期是您显示的格式，这就会起作用。如果它也有时间，那么您必须删除时间。
Also I do not know if the client_id has to come in in this query, if you have to group by it also then you have to put it along side trip_date on select or group. If you want to filter by it put it in the where.
此外，我不知道 client_id 是否必须出现在此查询中，如果您还必须按它分组，那么您必须将它放在选择或组上的 trip_date 旁边。如果你想过滤它，把它放在 where 中。
