27,580
社区成员
发帖
与我相关
我的任务
分享
/****************************************************************
作者:yinchuan
CSDN提问链接:
http://topic.csdn.net/u/20120821/18/250fa91d-8f6e-4e10-9840-9ed8c67f5e33.html
PS:献丑,请指教!
****************************************************************/
--准备环境
USE test
GO
--orders中是随机生成的测试数据
IF OBJECT_ID('orders') IS NOT NULL
DROP TABLE orders
GO
CREATE TABLE orders
(
id int identity
,ordertime datetime
)
GO
--插入30条随机生成的order,日期分布是今天往后10天之内
DECLARE @count int = 0;
DECLARE @today datetime = GETDATE();
WHILE @count < 30
BEGIN
INSERT INTO orders
VALUES
(CAST ( CAST(@today as INT)-RAND()*10 AS datetime))
SET @count += 1
END
GO
--生成从今天开始完整的7天日期
DECLARE @LastSevenDay table
(
day date
)
DECLARE @StartDay date = GETDATE();
DECLARE @InsertDay date = @StartDay;
WHILE DATEDIFF(DAY,@InsertDay,@StartDay) < 7
BEGIN
INSERT INTO @LastSevenDay
VALUES ( @InsertDay)
SET @InsertDay = DATEADD(day,-1,@InsertDay)
END
--生成最后结果
SELECT DATEPART(day,lsd.day) AS '日期' ,COUNT(orders.id) AS '订单量'
FROM @LastSevenDay AS lsd
LEFT JOIN orders
ON lsd.day = CONVERT(varchar(10),orders.ordertime,120)
GROUP BY lsd.day
日期 订单量
----------- -----------
15 4
16 2
17 2
18 2
19 4
20 5
21 1
(7 行受影响)
SELECT Date,ISNULL(COUNT(1),0) FROM OrderTable GROUP BY Date