按天从mysql数据库组获取查询 - Get query from mysql database group by day

Get query from mysql database group by day

本文关键字：获取查询数据库 mysql | 更新日期: 2023-09-27

我有一个这样的表：

id           view_time               view_cost               view_ip       ect
1           2012-05-03 00:00:00     10                      46.196.25.2   ----
2           2012-05-03 00:00:00     30                      46.196.25.2   ----
3           2012-05-03 00:00:00     20                      46.196.25.2   ----
4           2012-05-04 00:00:00     10                      46.196.25.2   ----
5           2012-05-04 00:00:00     80                      46.196.25.2   ----
6           2012-05-05 00:00:00     40                      46.196.25.2   ----
7           2012-05-05 00:00:00     50                      46.196.25.2   ----
8           2012-05-05 00:00:00     10                      46.196.25.2   ----
9           2012-05-05 00:00:00     10                      46.196.25.2   ----
10          2012-05-05 00:00:00     20                      46.196.25.2   ----
11          2012-05-05 00:00:00     10                      46.196.25.2   ----

我的结果一定是这样的：

Day          Views          Cost
2012-05-03   3              60
2012-05-04   2              90
2012-05-05   6              140

我正在寻找Mysql查询和php代码来获得这个结果

尝试这个

sql fiddle

sql篡改日期格式

select DATE(view_time) as `Day`, count(*) as `Views`, sum(view_cost) as `Cost`
from `your_table`
group by DATE(view_time)

sql应该是非常简单的

SELECT 
    DATE(`view_time`) AS `Day`,
    count(*) AS `Views`,
    SUM(`view_cost`) AS `Cost`
FROM
    `yourtable`
GROUP BY
    DATE(`view_time`)

您必须自己创建的php代码

非常直接。

SELECT view_time, count(view_Time) as Views, sum(view_cost) as view_cost
FROM TABLENAME
GROUP BY View_time