我有一个简单的表格(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/

10-11 05:13