数据库结构如下:
销售流水号 商品货号 出货时间
00001 k1 20131105 09:12:10
00001 k2 20131105 22:12:10
00002 j123 20131105 11:12:10
00002 j234 20131105 16:12:10
00002 j564 20131105 19:12:10
00003 l123 20131106 11:12:10
00003 l234 20131106 16:12:10
00003 l564 20131106 09:12:10
想要的查询结果如下:
出货时间 销售流水比数
20131105 2
20131106 1
如上所示想统计出每天产生的销售流水数量,请问sql怎么写?
------解决方案--------------------
select trunc(time),count(distinct 流水号) from table_name group by trunc(time)
------解决方案--------------------
round(time)不是返回天数吧,而是
ROUND returns date rounded to the unit specified by the format model fmt. This function is not sensitive to the NLS_CALENDAR session parameter. It operates according to the rules of the Gregorian calendar. The value returned is always of data type DATE, even if you specify a different datetime data type for date. If you omit fmt, then date is rounded to the nearest day. The date expression must resolve to a DATE value.
引用自:http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions154.htm#SQLRF51710
------解决方案--------------------
select substr(t.time,1,10),count(distinct no) from table_name t group by substr(t.time,1,8);