mysql - 查询数据库以显示上午和下午完成的记录数

Question

我有一个包含以下字段的简单表 (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

如有任何帮助，我们将不胜感激。非常感谢。

Answer 1

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