22,209
社区成员
发帖
与我相关
我的任务
分享
yjbiao
id yj userid date
1 5345 1 2008-12-9
2 4234 1 2008-12-5
3 334 3 2008-12-3
4 454 1 2008-10-3
5 234 2 2008-12-1
6 456 1 2008-12-12
7 5345 1 2008-11-9
8 4234 1 2008-12-5
9 334 3 2008-10-3
10 454 1 2008-12-3
11 234 2 2008-12-1
12 456 1 2008-10-12
DECLARE @START SMALLDATETIME, @END SMALLDATETIME
DECLARE @DATE TABLE(COL SMALLDATETIME)
SET @START='2008-12-01'
SET @END=@START
WHILE DATEDIFF(MM,@START,@END)=0
BEGIN
INSERT @DATE SELECT @END
SET @END=DATEADD(DAY,1,@END)
END
SELECT id,col as date, isnull(yj,0) as yj
FROM @DATE left join yjbiao on col=date
DECLARE @START SMALLDATETIME, @END SMALLDATETIME
DECLARE @DATE TABLE(COL SMALLDATETIME)
SET @START='2008-12-01'
SET @END=@START
WHILE DATEDIFF(MM,@START,@END)=0
BEGIN
INSERT @DATE SELECT @END
SET @END=DATEADD(DAY,1,@END)
END
SELECT * FROM @DATE
DECLARE @DAYS INT
DECLARE @START SMALLDATETIME
SET @DAYS=0
SET @START='2008-12-01'
SET ROWCOUNT @DAYS
SELECT DATEADD(DAY, ID, @START)
FROM (
SELECT TOP 31 ID=(SELECT COUNT(*) FROM SYSOBJECTS WHERE ID<O.ID) FROM SYSOBJECTS AS O ORDER BY ID
) T
SET ROWCOUNT 0
/*
2008-12-01 00:00:00
2008-12-02 00:00:00
2008-12-03 00:00:00
......
2008-12-29 00:00:00
2008-12-30 00:00:00
2008-12-31 00:00:00
*/
select m.riqi , isnull(n.yj , 0) yj from
(
select '2008-12-01' riqi union all
select '2008-12-02' riqi union all
...
select '2008-12-31' riqi
) m left join yjbiao n
on m.riqi = n.date
rq 当天业绩总和
1 4234
2 534
3 0
4 4234
. 6456
. 0
. 4234
31 423423