代码改变世界

Sqlserver 每日订单半小时数据统计

2018-07-11 17:29  skye_Jia  阅读(1791)  评论(0)  编辑  收藏  举报 
SELECT dttime, SUM(a.price) '销售额',count(1) '订单数'
FROM (SELECT CASE
                WHEN DATEPART(mi, o.create_at) < 30
                THEN
                    CONVERT(DATETIME,CONVERT(NVARCHAR(50), create_at, 23) +' '+CONVERT(VARCHAR(50),DATEPART(hh, create_at))+':00:00')
                ELSE
                    CONVERT(DATETIME,CONVERT(NVARCHAR(50), create_at, 23) +' '+CONVERT(VARCHAR(50),DATEPART(hh, create_at))+':30:00')
                END 'dttime',*                    
        FROM dbo.[order](NOLOCK) o 
        WHERE create_at >= '2018-07-11' and create_at<'2018-07-12') a
GROUP BY dttime