我有一个简单的表格(walkin2012),其中包含以下字段:
ID | date | store | timestamp | custName
1 | 2013-05-08 | Plainfield | 2013-05-08 10:38:40 | cust1
2 | 2013-05-08 | Plainfield | 2013-05-08 13:38:40 | cust2
3 | 2013-05-09 | Bayport | 2013-05-09 20:38:40 | cust3
4 | 2013-05-10 | Plainfield | 2013-05-10 17:38:40 | cust4
5 | 2013-05-10 | Bayport | 2013-05-10 11:38:40 | cust4
我想查询表格以显示上午10点至下午1点之间在每个商店的每个日期的下午和下午之后的所有记录,因此它只会给我以下结果:
date | store | morning | afternoon
2013-05-08 | Plainfield | 1 | 1
2013-05-09 | Bayport | 0 | 1
2013-05-10 | Bayport | 1 | 0
2013-05-10 | Plainfield | 0 | 1
我有日期,商店和早上上班,但是我对如何子查询下午的部分感到困惑。到目前为止,这是我的查询:
SELECT date, store, count(walkinID) AS Morning
FROM walkin2012
WHERE timestamp
BETWEEN concat_ws(' ',date,'10:00:00') AND concat_ws(' ',date,'13:00:00')
GROUP BY date,store
任何帮助,将不胜感激。非常感谢你。
最佳答案
SELECT date,
store,
SUM(timestamp BETWEEN date + interval 10 hour AND
date + interval 13 hour) Morning,
SUM(timestamp BETWEEN date + interval 13 hour AND
date + interval 24 hour) Afternoon
FROM walkin2012
GROUP BY date, store
SQLFiddle Demo
关于mysql - 查询数据库以显示在早上和下午完成的记录数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/16535569/