呵呵,当然简单是针对各位高手来讲的。假设有一个用户表,比如叫做TAB_USER,只有两个字段:
字段名 类型 含义
USER_ID number 用户ID
REG_TIME date 注册时间每天可能有几个用户注册,也可能没有用户注册。现在我要统计最近一周每天注册的用户数,我的SQL如下:select count(USER_ID) AS rscount, to_char(REG_TIME, 'yyyy-MM-dd') as rsdate
from TAB_USER
where REG_TIME > sysdate - 7
group by to_char(REG_TIME, 'yyyy-MM-dd')
order by to_char(REG_TIME, 'yyyy-MM-dd') asc得到的结果类似于:
439 2009-09-18
4309 2009-09-22其中19,20,21三天没有注册用户,但是我也想在SQL的结果中显示(因为数据需要作为输入在其它模块中使用),也就是结果期望类似于:
439 2009-09-18
0 2009-09-19
0 2009-09-20
0 2009-09-21
4309 2009-09-22望各位高手指点
字段名 类型 含义
USER_ID number 用户ID
REG_TIME date 注册时间每天可能有几个用户注册,也可能没有用户注册。现在我要统计最近一周每天注册的用户数,我的SQL如下:select count(USER_ID) AS rscount, to_char(REG_TIME, 'yyyy-MM-dd') as rsdate
from TAB_USER
where REG_TIME > sysdate - 7
group by to_char(REG_TIME, 'yyyy-MM-dd')
order by to_char(REG_TIME, 'yyyy-MM-dd') asc得到的结果类似于:
439 2009-09-18
4309 2009-09-22其中19,20,21三天没有注册用户,但是我也想在SQL的结果中显示(因为数据需要作为输入在其它模块中使用),也就是结果期望类似于:
439 2009-09-18
0 2009-09-19
0 2009-09-20
0 2009-09-21
4309 2009-09-22望各位高手指点
declare @edate datetime
set @edate = getdate()
set @sdate = dateadd(dd,-6,@edate)
select
dateadd(dd,num,@sdate)
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate)<=@edate/*
------------------------------------------------------
2009-09-17 09:09:24.750
2009-09-18 09:09:24.750
2009-09-19 09:09:24.750
2009-09-20 09:09:24.750
2009-09-21 09:09:24.750
2009-09-22 09:09:24.750
2009-09-23 09:09:24.750(所影响的行数为 7 行)
*/
from TAB_USER
where REG_TIME > CONVERT(CHAR,GETDATE()- 7 , 23)
group by CONVERT(CHAR,REG_TIME, 23)
order by CONVERT(CHAR,REG_TIME, 23)
set @date='2009-09-01';with f as
{
select count(USER_ID) AS rscount, to_char(REG_TIME, 'yyyy-MM-dd') as rsdate
from TAB_USER
where REG_TIME > sysdate - 7
group by to_char(REG_TIME, 'yyyy-MM-dd')
order by to_char(REG_TIME, 'yyyy-MM-dd') asc
)
with f1 as
(
select [day]=ltrim(year(@date))+right(100+month(@date),2)+right('0'+ltrim(number),2)
from master..spt_values
where type='p'
and number >=1
and number <= 22
)
select isnull(f.rscount,0) as rscount,f.rsdate from f left join f1 on f.rsdate =f1.day
select 日期,rscount from (
Select dateadd(day,number,DATEADD(wk, DATEDIFF(wk,0,getdate())-1,0)) as 日期
from
TAB_USER a,
master..spt_values s
where
type='p' and number<7
and datediff(wk,
dateadd(day,number,DATEADD(wk, DATEDIFF(wk,0,getdate())-1,0)),
dateadd(day,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()) ,0))
)
)t left join
(
select count(USER_ID) AS rscount, to_char(REG_TIME, 'yyyy-MM-dd') as rsdate
from TAB_USER
where REG_TIME > sysdate - 7
group by to_char(REG_TIME, 'yyyy-MM-dd')
) t1
on t.日期=rsdate
order by 日期
---我写的,多的要死
(
ID char(10),
DATE varchar(10),
NUM int
)
insert into @TT select '01','2009-01',10
union all select '01','2009-02',11
union all select '01','2009-03',12
union all select '02','2009-02',32
union all select '02','2009-04',33
select distinct T1.ID,T2.Date into #1 from
(
select Date='2009-01'
union all
select Date='2009-02'
union all
select Date='2009-03'
union all
select Date='2009-04'
union all
select Date='2009-05'
union all
select Date='2009-06'
) T2,@TT T1
select T2.ID,T2.Date,isnull(T1.Num,0) '数量' from @TT T1 right join #1 T2 on T1.ID=T2.ID and T1.Date=T2.Date
ID Date 数量
---------- ------- -----------
01 2009-01 10
01 2009-02 11
01 2009-03 12
01 2009-04 0
01 2009-05 0
01 2009-06 0
02 2009-01 0
02 2009-02 32
02 2009-03 0
02 2009-04 33
02 2009-05 0
02 2009-06 0
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/09/22/4581876.aspx
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-23 09:19:11
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:TAB_USERIF NOT OBJECT_ID('[TAB_USER]') IS NULL
DROP TABLE [TAB_USER]
GO
CREATE TABLE [TAB_USER]([USER_ID] NVARCHAR(10),[REG_TIME] DATETIME)
INSERT [TAB_USER]
SELECT '001',N'2009-09-18' UNION ALL
SELECT '002',N'2009-09-18' UNION ALL
SELECT '003',N'2009-09-18' UNION ALL
SELECT '004',N'2009-09-18' UNION ALL
SELECT '005',N'2009-09-22'
GO
--SELECT * FROM [TAB_USER]-->SQL查询如下:select count([USER_ID]) AS rscount, CONVERT(CHAR,b.DT, 23) as rsdate
from TAB_USER a
RIGHT JOIN (
SELECT CONVERT(CHAR,GETDATE()- n , 23) AS DT
FROM
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) t
) b
ON CONVERT(CHAR,a.REG_TIME, 23)=CONVERT(CHAR,b.DT, 23)
group by CONVERT(CHAR,b.DT, 23)
order by CONVERT(CHAR,b.DT, 23)
/*
rscount rsdate
----------- ------------------------------
0 2009-09-17
4 2009-09-18
0 2009-09-19
0 2009-09-20
0 2009-09-21
1 2009-09-22
0 2009-09-23 (7 行受影响)
*/
IF NOT OBJECT_ID('[TAB_USER]') IS NULL
DROP TABLE [TAB_USER]
GO
CREATE TABLE [TAB_USER]([USER_ID] NVARCHAR(10),[REG_TIME] DATETIME)
INSERT [TAB_USER]
SELECT '001',N'2009-09-18' UNION ALL
SELECT '002',N'2009-09-18' UNION ALL
SELECT '003',N'2009-09-18' UNION ALL
SELECT '004',N'2009-09-18' UNION ALL
SELECT '005',N'2009-09-22'
GO
select 日期,isnull(rscount,0) rscount from (
Select distinct dateadd(day,number,DATEADD(wk, DATEDIFF(wk,0,getdate())-1,0)) as 日期
from
TAB_USER a,
master..spt_values s
where
type='p' and number<7
and datediff(wk,
dateadd(day,number,DATEADD(wk, DATEDIFF(wk,0,getdate())-1,0)),
dateadd(day,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()) ,0))
)<=7
)t left join
(
select count(USER_ID) AS rscount, [REG_TIME]
from TAB_USER
where REG_TIME > [REG_TIME] - 7
group by [REG_TIME]
) t1
on t.日期=[REG_TIME]
order by 日期日期 rscount
----------------------- -----------
2009-09-14 00:00:00.000 0
2009-09-15 00:00:00.000 0
2009-09-16 00:00:00.000 0
2009-09-17 00:00:00.000 0
2009-09-18 00:00:00.000 4
2009-09-19 00:00:00.000 0
2009-09-20 00:00:00.000 0(7 行受影响)
CREATE TABLE [TAB_USER]([USER_ID] NVARCHAR(10),[REG_TIME] DATETIME)
INSERT [TAB_USER]
SELECT '001',N'2009-09-18' UNION ALL
SELECT '002',N'2009-09-18' UNION ALL
SELECT '003',N'2009-09-18' UNION ALL
SELECT '004',N'2009-09-18' UNION ALL
SELECT '005',N'2009-09-22'
declare @sdate datetime
declare @edate datetime
set @edate = getdate()
set @sdate = dateadd(dd,-7,@edate) select A.date,COUNT(b.USER_ID) from (
select
LEFT ( convert (char, dateadd(dd,num,@sdate) ,120 ),10) date
from
(select ROW_NUMBER ( ) over ( order by id ) as num
from sysobjects ) a
where
dateadd(dd,num,@sdate) <=@edate
) A left join [TAB_USER] b on A.date=b.REG_TIME
group by A.date
SELECT CONVERT(CHAR,GETDATE()- n , 23) AS DT
FROM
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) t学习了,这个真的很好玩
from (
select to_char(sysdate-1,'yyyy-mm-dd') as rsdate from dual
union all
select to_char(sysdate-1,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-2,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-3,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-4,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-5,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-6,'yyyy-mm-dd') from dual) t1
left join
( select count(USER_ID) AS rscount, to_char(REG_TIME, 'yyyy-MM-dd') as rsdate
from TAB_USER
where REG_TIME > sysdate - 7
group by to_char(REG_TIME, 'yyyy-MM-dd') ) t2
on t1.rsdate=t2.rsdate;
select t2.rscount, t1.rsdate
from (
select to_char(sysdate-1,'yyyy-mm-dd') as rsdate from dual
union all
select to_char(sysdate-1,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-2,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-3,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-4,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-5,'yyyy-mm-dd') from dual
union all
select to_char(sysdate-6,'yyyy-mm-dd') from dual) t1
left join
( select count(USER_ID) AS rscount, to_char(REG_TIME, 'yyyy-MM-dd') as rsdate
from TAB_USER
where REG_TIME > sysdate - 7
group by to_char(REG_TIME, 'yyyy-MM-dd') ) t2
on t1.rsdate=t2.rsdate;
if object_id('A') is not null
drop table A
go
create table A(userid int , regtime datetime)
go
insert into A
select 1 , '2009-08-08' union all
select 2 , '2009-08-08' union all
select 3 , '2009-08-08' union all
select 4 , '2009-08-09' union all
select 5 , '2009-08-11' union all
select 6 , '2009-08-11' union all
select 7 , '2009-08-13'
go
declare @mintime datetime
declare @maxtime datetime
declare @t table(userid int , regtime datetime)
select @mintime = min(regtime) , @maxtime = max(regtime) from A
while (datediff(day , @mintime , @maxtime)>=0)
begin
insert into @t
select isnull(count(userid) , 0), @mintime from A
where regtime = @mintime
set @mintime = @mintime + 1
end
select * from @t--------------------------------------------
userid regtime
3 2009-08-08 00:00:00.000
1 2009-08-09 00:00:00.000
0 2009-08-10 00:00:00.000
2 2009-08-11 00:00:00.000
0 2009-08-12 00:00:00.000
1 2009-08-13 00:00:00.000
lz是ORACLE的
呵呵我用sql server解决的
飘过