计算mysql中每天的发生次数
I'm having some problems figuring out how to solve but I can't come with an answer at all.This is my problem.
我在解决如何解决方面遇到了一些问题,但我根本无法得到答案。这是我的问题。
I have a mySQL table like the following:
我有一个mySQL表,如下所示:
cust_id,date_removed,station_removed,date_arrived,station_arrived
6,"2010-02-02 13:57:00",56,"2010-02-02 13:58:00",77
6,"2010-02-02 15:12:00",66,"2010-02-02 15:12:00",56
30,"2010-02-05 11:36:00",32,"2010-02-05 11:37:00",14
30,"2010-02-05 11:37:00",14,"2010-02-05 11:37:00",20
30,"2010-02-05 12:41:00",85,"2010-02-05 12:43:00",85
30,"2010-02-05 12:44:00",85,"2010-02-05 12:46:00",85
30,"2010-02-06 13:15:00",8,"2010-02-06 13:17:00",20
30,"2010-02-06 13:18:00",23,"2010-02-06 13:19:00",23
30,"2010-02-06 13:20:00",32,"2010-02-06 13:21:00",39
30,"2010-02-06 13:21:00",11,"2010-02-06 13:21:00",23
30,"2010-02-06 13:21:00",76,"2010-02-06 13:22:00",32
which the corresponding datatypes in each field is the following: cust_id: varchar() date_removed: datetime station_removed: int date_arrived: datetime station_arrived: int
每个字段中相应的数据类型如下:cust_id:varchar()date_removed:datetime station_removed:int date_arrived:datetime station_arrived:int
Next, I was asked to make a query to get the count over every station used along the day, to get a table like this one:
接下来,我被要求进行查询以获取当天使用的每个站点的计数,以获得如下表格:
station 2010-02-02 2010-02-05 2010-02-06
56 2 0 0
66 1 0 0
32 0 1 2
14 0 2 0
85 0 2 0
8 0 0 1
23 0 0 2
11 0 0 1
76 0 0 1
77 1 0 0
20 0 1 1
39 0 0 1
where the columns are the days and the rows are each station. I'm not a very good mySQL user neither.
列是日期,行是每个站。我也不是一个非常好的mySQL用户。
Could somebody help me on this one?.
有人可以帮我这个吗?
Thank you in advance
先感谢您
1 个解决方案
#1
Use this query :
使用此查询:
select stations.name as station,
(select count(*) from table where date(date_arrived)='2010-02-02' and (station_removed=stations.name or station_arrived=stations.name)) as '2010-02-02'
(select count(*) from table where date(date_arrived)='2010-02-05' and (station_removed=stations.name or station_arrived=stations.name)) as '2010-02-05'
(select count(*) from table where date(date_arrived)='2010-02-06' and (station_removed=stations.name or station_arrived=stations.name)) as '2010-02-06'
from
(select station_removed as name from table
union
select station_arrived from table ) stations ;
更多相关文章
- hibernate(*.hbm.xml)中新添加的字段被标记为红色(找不到)的解决方法
- 如何使用PDO从MySQL获取正确的数据类型?
- 保持最新的一个字段值,直到它发生变化,然后保持其最新的字段值
- 替换wordpress WP_POSTS表中post_date字段的年份(4位数)
- 如何以varchar字段为编号?
- MySQL查询优化系列讲座之数据类型与效率
- 常见几种数据库中自增字段的设置方法(转)
- 如何使用jQuery将输入值插入更多填充的字段?
- JavaScript数据类型的一些注意点(2)