如何每个小时从数据库获取订单 [英] How to get orders from database foreach hour

问题描述

我不知道每个小时如何获得订单，在一个固定的日期中有多少个订单.我的意思是

日期= 2012-08-07，
6 AM=20,7AM=30,8AM=40,9AM=0(如果9AM没下订单)，.......直到20PM一样.

请给我一个示例查询.

谢谢与问候，
Raghu.

Hi,
I don''t know how to get orders foreach hour how many orders are placed in a perticualar date.I mean for example

Date=2012-08-07,
6AM=20,7AM=30,8AM=40,9AM=0(if no orders are placed at 9AM),.......up to 20PM like.

please give me one sample query.

Thanks&Regards,
Raghu.

推荐答案

假设Order_tbl包含一些如下所示的数据

suppose Order_tbl have some data as given below

Name   orderdt                   ordertm   
-----------------------------------------------------
aaaa   2012-08-08 00:00:00.000   2012-08-08 10:54:15.053
bbbb   2012-08-08 00:00:00.000   2012-08-08 10:54:17.270
cccc   2012-08-08 00:00:00.000   2012-08-08 10:54:18.727
dddd   2012-08-08 00:00:00.000   2012-08-08 10:54:19.117
eeee   2012-08-08 00:00:00.000   2012-08-08 11:54:21.000
ffff   2012-08-08 00:00:00.000   2012-08-08 11:55:21.000
gggg   2012-08-08 00:00:00.000   2012-08-08 10:55:21.663
hhhh   2012-08-08 00:00:00.000   2012-08-08 10:55:21.957

现在，在下面的查询表中查询

now, check below query for this table

select case when datepart(hour,ordertm)>12 then convert(varchar,datepart(hour,ordertm)-12) +' PM' else convert(varchar,datepart(hour,ordertm)) +' AM' end  as hours,
count(*) as Total_orders from Order_tbl
where (orderdt)='2012-08-08' group by datepart(hour,ordertm)

日期"2012-08-08"的结果

result for date ''2012-08-08''

Hours   Total_Orders
-------------------------
10 AM	6
11 AM	2

祝您编码愉快！
:)

Happy Coding!
:)

这篇关于如何每个小时从数据库获取订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！