这是我的存储过程
但是查询出的结果没有按OrderTime 分组. 有什么错误吗?
望指教
ALTER PROCEDURE dbo.kipp_GetKeyCodeReport
(
@strKeyCode Varchar(10),
@StartDate DateTime,
@EndDate DateTime
)
ASSELECT a.OrderTime,
SUM(a.ShoppingAmount) as ShoppingAmoutTotal ,
SUM(a.ShippingCharge) as ShoppingChargeTotal ,
SUM(a.ServiceCharge) as ServiceChargeTotal ,
SUM(a.TaxAmount) as TaxAmountTotal,
SUM(a.ShoppingAmount)+SUM(a.ShippingCharge)+SUM(a.ServiceCharge)+SUM(a.TaxAmount) as TotalFROM ORDERS a WHERE (a.[OrderTime] >= @StartDate and a.[OrderTime]< @EndDate) AND (a.CouponKeyCode = @strKeyCode ) GROUP BY a.OrderTime
order by a.OrderTime
RETURN
但是查询出的结果没有按OrderTime 分组. 有什么错误吗?
望指教
ALTER PROCEDURE dbo.kipp_GetKeyCodeReport
(
@strKeyCode Varchar(10),
@StartDate DateTime,
@EndDate DateTime
)
ASSELECT a.OrderTime,
SUM(a.ShoppingAmount) as ShoppingAmoutTotal ,
SUM(a.ShippingCharge) as ShoppingChargeTotal ,
SUM(a.ServiceCharge) as ServiceChargeTotal ,
SUM(a.TaxAmount) as TaxAmountTotal,
SUM(a.ShoppingAmount)+SUM(a.ShippingCharge)+SUM(a.ServiceCharge)+SUM(a.TaxAmount) as TotalFROM ORDERS a WHERE (a.[OrderTime] >= @StartDate and a.[OrderTime]< @EndDate) AND (a.CouponKeyCode = @strKeyCode ) GROUP BY a.OrderTime
order by a.OrderTime
RETURN
分组时有秒的话就会有问题
用convert函数转成日期型再分组
2006-5-2 下午 02:41:25 36.35 25 0 0 61.35
2006-5-3 下午 02:29:58 117 13.25 0 0 130.25
2006-5-9 下午 02:30:21 117 13.25 0 0 130.25
2006-5-9 下午 03:04:12 69 11.25 0 0 80.25
2006-5-9 下午 06:33:29 20.5 10.25 4 0 34.75
2006-5-9 下午 06:38:27 30.6 10.25 0 0 40.85
2006-5-9 下午 06:49:21 28.5 10.25 4 0 42.75
2006-5-9 下午 07:04:04 17 10.25 4 0 31.25
2006-5-10 上午 11:32:47 17 10.25 4 0 31.25
2006-5-10 下午 02:38:48 51 11.25 0 0 62.25
2006-5-10 下午 02:47:37 40.2 11 0 0 51.2
2006-5-10 下午 02:49:36 40.2 11 0 0 51.2
2006-5-10 下午 02:53:17 21 10.25 4 0 35.25
2006-5-10 下午 02:59:18 144 18.25 0 0 162.25
2006-5-10 下午 03:08:53 108 13.25 0 0 121.25
2006-5-10 下午 03:15:17 100.5 29.25 0 0 129.75
2006-5-11 上午 11:03:07 4.5 0 0 0 4.5
2006-5-11 下午 02:10:04 5.8 20 0 0 25.8
2006-5-11 下午 02:12:15 40.8 25 0 0 65.8
2006-5-11 下午 06:39:24 12 0 0 0 12
2006-5-12 下午 01:45:57 30.5 23.25 0 0 53.75
2006-5-12 下午 02:37:31 58.85 26.25 0 0 85.1
2006-5-12 下午 02:49:36 34.05 10.25 0 0 44.3
2006-5-12 下午 02:53:56 3.1 0 0 0 3.1
2006-5-12 下午 06:07:06 100 29.25 0 0 129.25
2006-5-12 下午 06:14:28 19.15 10.25 4 0 33.4
2006-5-12 下午 06:17:17 41.7 11 0 0 52.7
2006-5-12 下午 06:19:52 36.4 11 0 0 47.4
2006-5-17 下午 03:54:12 52 11.25 0 0 63.25
2006-5-17 下午 04:16:16 240 0 0 0 240
2006-5-17 下午 04:22:42 426 0 0 0 426
2006-5-17 下午 04:27:02 41.05 11 0 0 52.05
2006-5-17 下午 04:29:21 106.8 13.25 0 0 120.05
2006-5-17 下午 07:04:08 6.15 0 0 0 6.15
2006-5-18 下午 05:31:45 11.3 0 0 0 11.3
2006-5-18 下午 05:36:17 2.9 20 0 0 22.9
2006-5-18 下午 05:40:48 2.15 20 0 0 22.15
2006-5-19 下午 04:09:16 4.2 0 0 0 4.2
2006-5-19 下午 04:13:54 27.6 10.25 4 0 41.85
2006-5-19 下午 05:07:13 3.5 0 0 0 3.5
2006-5-22 下午 12:13:07 86.9 0 0 0 86.9
2006-5-22 下午 12:53:04 52 26.25 0 0 78.25
2006-5-22 下午 05:29:18 65 0 0 0 65
2006-6-26 下午 02:55:42 59.5 0 0 0 59.5
2006-7-4 下午 04:24:59 99 13.25 0 0 112.25
GROUP BY a.OrderTime
order by a.OrderTime改成
GROUP BY convert(varchar(10),OrderTime,120)
order by convert(varchar(10),OrderTime,120)