a表
PID PName
1 广告牌1
2 广告牌2
3 广告牌3
4 广告牌4
5 广告牌5
b表
PID StartDate EndDate
1 2010-01-01 2010-1-20
1 2010-01-25 2010-2-25
2 2010-01-01 2010-1-20
2 2010-02-01 2010-2-25
3 2010-01-01 2010-3-30
统计条件如: 2010-01-01 至 2010-04-01日期 广告牌总数 使用数
2010-01-01 5 3
2010-01-02 5 3
...
2010-01-20 5 3
2010-01-21 5 1
...
2010-01-25 5 2
2010-01-26 5 2
...
2010-02-01 5 3
...
2010-02-25 5 3
...
2010-03-30 5 1
2010-04-01 5 0
PID PName
1 广告牌1
2 广告牌2
3 广告牌3
4 广告牌4
5 广告牌5
b表
PID StartDate EndDate
1 2010-01-01 2010-1-20
1 2010-01-25 2010-2-25
2 2010-01-01 2010-1-20
2 2010-02-01 2010-2-25
3 2010-01-01 2010-3-30
统计条件如: 2010-01-01 至 2010-04-01日期 广告牌总数 使用数
2010-01-01 5 3
2010-01-02 5 3
...
2010-01-20 5 3
2010-01-21 5 1
...
2010-01-25 5 2
2010-01-26 5 2
...
2010-02-01 5 3
...
2010-02-25 5 3
...
2010-03-30 5 1
2010-04-01 5 0
insert into b values(1 ,'2010-01-01', '2010-1-20')
insert into b values(1 ,'2010-01-25', '2010-2-25')
insert into b values(2 ,'2010-01-01', '2010-1-20')
insert into b values(2 ,'2010-02-01', '2010-2-25')
insert into b values(3 ,'2010-01-01', '2010-3-30')
goSELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b select convert(varchar(10),日期,120) 日期 , count(1) 使用数 from
(
select dateadd(dd,n.id,m.StartDate) 日期 from b m, # n where dateadd(dd,n.id,m.StartDate) <= m.EndDate
) t
group by convert(varchar(10),日期,120)
order by convert(varchar(10),日期,120)drop table b , #/*
日期 使用数
---------- -----------
2010-01-01 3
2010-01-02 3
2010-01-03 3
2010-01-04 3
2010-01-05 3
2010-01-06 3
2010-01-07 3
2010-01-08 3
2010-01-09 3
2010-01-10 3
2010-01-11 3
2010-01-12 3
2010-01-13 3
2010-01-14 3
2010-01-15 3
2010-01-16 3
2010-01-17 3
2010-01-18 3
2010-01-19 3
2010-01-20 3
2010-01-21 1
2010-01-22 1
2010-01-23 1
2010-01-24 1
2010-01-25 2
2010-01-26 2
2010-01-27 2
2010-01-28 2
2010-01-29 2
2010-01-30 2
2010-01-31 2
2010-02-01 3
2010-02-02 3
2010-02-03 3
2010-02-04 3
2010-02-05 3
2010-02-06 3
2010-02-07 3
2010-02-08 3
2010-02-09 3
2010-02-10 3
2010-02-11 3
2010-02-12 3
2010-02-13 3
2010-02-14 3
2010-02-15 3
2010-02-16 3
2010-02-17 3
2010-02-18 3
2010-02-19 3
2010-02-20 3
2010-02-21 3
2010-02-22 3
2010-02-23 3
2010-02-24 3
2010-02-25 3
2010-02-26 1
2010-02-27 1
2010-02-28 1
2010-03-01 1
2010-03-02 1
2010-03-03 1
2010-03-04 1
2010-03-05 1
2010-03-06 1
2010-03-07 1
2010-03-08 1
2010-03-09 1
2010-03-10 1
2010-03-11 1
2010-03-12 1
2010-03-13 1
2010-03-14 1
2010-03-15 1
2010-03-16 1
2010-03-17 1
2010-03-18 1
2010-03-19 1
2010-03-20 1
2010-03-21 1
2010-03-22 1
2010-03-23 1
2010-03-24 1
2010-03-25 1
2010-03-26 1
2010-03-27 1
2010-03-28 1
2010-03-29 1
2010-03-30 1(所影响的行数为 89 行)*/
insert into b values(1 ,'2010-01-01', '2010-1-20')
insert into b values(1 ,'2010-01-25', '2010-2-25')
insert into b values(2 ,'2010-01-01', '2010-1-20')
insert into b values(2 ,'2010-02-01', '2010-2-25')
insert into b values(3 ,'2010-01-01', '2010-3-30')
goSELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b select convert(varchar(10),日期,120) 日期 , (select count(1) from b) 广告牌总数 , count(1) 使用数 from
(
select dateadd(dd,n.id,m.StartDate) 日期 from b m, # n where dateadd(dd,n.id,m.StartDate) <= m.EndDate
) t
group by convert(varchar(10),日期,120)
order by convert(varchar(10),日期,120)drop table b , #/*
日日期 广告牌总数 使用数
---------- ----------- -----------
2010-01-01 5 3
2010-01-02 5 3
2010-01-03 5 3
2010-01-04 5 3
2010-01-05 5 3
2010-01-06 5 3
2010-01-07 5 3
2010-01-08 5 3
2010-01-09 5 3
2010-01-10 5 3
2010-01-11 5 3
2010-01-12 5 3
2010-01-13 5 3
2010-01-14 5 3
2010-01-15 5 3
2010-01-16 5 3
2010-01-17 5 3
2010-01-18 5 3
2010-01-19 5 3
2010-01-20 5 3
2010-01-21 5 1
2010-01-22 5 1
2010-01-23 5 1
2010-01-24 5 1
2010-01-25 5 2
2010-01-26 5 2
2010-01-27 5 2
2010-01-28 5 2
2010-01-29 5 2
2010-01-30 5 2
2010-01-31 5 2
2010-02-01 5 3
2010-02-02 5 3
2010-02-03 5 3
2010-02-04 5 3
2010-02-05 5 3
2010-02-06 5 3
2010-02-07 5 3
2010-02-08 5 3
2010-02-09 5 3
2010-02-10 5 3
2010-02-11 5 3
2010-02-12 5 3
2010-02-13 5 3
2010-02-14 5 3
2010-02-15 5 3
2010-02-16 5 3
2010-02-17 5 3
2010-02-18 5 3
2010-02-19 5 3
2010-02-20 5 3
2010-02-21 5 3
2010-02-22 5 3
2010-02-23 5 3
2010-02-24 5 3
2010-02-25 5 3
2010-02-26 5 1
2010-02-27 5 1
2010-02-28 5 1
2010-03-01 5 1
2010-03-02 5 1
2010-03-03 5 1
2010-03-04 5 1
2010-03-05 5 1
2010-03-06 5 1
2010-03-07 5 1
2010-03-08 5 1
2010-03-09 5 1
2010-03-10 5 1
2010-03-11 5 1
2010-03-12 5 1
2010-03-13 5 1
2010-03-14 5 1
2010-03-15 5 1
2010-03-16 5 1
2010-03-17 5 1
2010-03-18 5 1
2010-03-19 5 1
2010-03-20 5 1
2010-03-21 5 1
2010-03-22 5 1
2010-03-23 5 1
2010-03-24 5 1
2010-03-25 5 1
2010-03-26 5 1
2010-03-27 5 1
2010-03-28 5 1
2010-03-29 5 1
2010-03-30 5 1(所影响的行数为 89 行)
*/
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(PID INT,PNAME VARCHAR(50))
INSERT INTO A
SELECT 1 ,'广告牌1' UNION ALL
SELECT 2 ,'广告牌2' UNION ALL
SELECT 3 ,'广告牌3' UNION ALL
SELECT 4 ,'广告牌4' UNION ALL
SELECT 5 ,'广告牌5'
CREATE TABLE B(PID INT,STARTDATE DATETIME,ENDDATE DATETIME)
INSERT INTO B
SELECT 1 ,'2010-01-01','2010-1-20' UNION ALL
SELECT 1 ,'2010-01-25','2010-2-25' UNION ALL
SELECT 2 ,'2010-01-01','2010-1-20' UNION ALL
SELECT 2 ,'2010-02-01','2010-2-25' UNION ALL
SELECT 3 ,'2010-01-01','2010-3-30'
/*
SELECT * FROM A
SELECT * FROM B
*/
DECLARE @STARTDATE DATETIME,@ENDDATE DATETIME
SELECT @STARTDATE='2010-01-01',@ENDDATE='2010-04-01'
;WITH MU AS (
SELECT @STARTDATE AS 'DATE'
UNION ALL
SELECT DATEADD(DAY,1,DATE) FROM MU WHERE DATE<@ENDDATE
)
SELECT MU.DATE
,(SELECT COUNT(1) FROM B)
,COUNT(B.PID)
FROM MU
LEFT JOIN B ON MU.DATE BETWEEN B.STARTDATE AND B.ENDDATE
GROUP BY MU.DATE
/*
2010-01-01 00:00:00.000 5 3
2010-01-02 00:00:00.000 5 3
2010-01-03 00:00:00.000 5 3
2010-01-04 00:00:00.000 5 3
2010-01-05 00:00:00.000 5 3
2010-01-06 00:00:00.000 5 3
2010-01-07 00:00:00.000 5 3
2010-01-08 00:00:00.000 5 3
2010-01-09 00:00:00.000 5 3
2010-01-10 00:00:00.000 5 3
2010-01-11 00:00:00.000 5 3
2010-01-12 00:00:00.000 5 3
2010-01-13 00:00:00.000 5 3
2010-01-14 00:00:00.000 5 3
2010-01-15 00:00:00.000 5 3
2010-01-16 00:00:00.000 5 3
2010-01-17 00:00:00.000 5 3
2010-01-18 00:00:00.000 5 3
2010-01-19 00:00:00.000 5 3
2010-01-20 00:00:00.000 5 3
2010-01-21 00:00:00.000 5 1
2010-01-22 00:00:00.000 5 1
2010-01-23 00:00:00.000 5 1
2010-01-24 00:00:00.000 5 1
2010-01-25 00:00:00.000 5 2
2010-01-26 00:00:00.000 5 2
2010-01-27 00:00:00.000 5 2
2010-01-28 00:00:00.000 5 2
2010-01-29 00:00:00.000 5 2
2010-01-30 00:00:00.000 5 2
2010-01-31 00:00:00.000 5 2
2010-02-01 00:00:00.000 5 3
2010-02-02 00:00:00.000 5 3
2010-02-03 00:00:00.000 5 3
2010-02-04 00:00:00.000 5 3
2010-02-05 00:00:00.000 5 3
2010-02-06 00:00:00.000 5 3
2010-02-07 00:00:00.000 5 3
2010-02-08 00:00:00.000 5 3
2010-02-09 00:00:00.000 5 3
2010-02-10 00:00:00.000 5 3
2010-02-11 00:00:00.000 5 3
2010-02-12 00:00:00.000 5 3
2010-02-13 00:00:00.000 5 3
2010-02-14 00:00:00.000 5 3
2010-02-15 00:00:00.000 5 3
2010-02-16 00:00:00.000 5 3
2010-02-17 00:00:00.000 5 3
2010-02-18 00:00:00.000 5 3
2010-02-19 00:00:00.000 5 3
2010-02-20 00:00:00.000 5 3
2010-02-21 00:00:00.000 5 3
2010-02-22 00:00:00.000 5 3
2010-02-23 00:00:00.000 5 3
2010-02-24 00:00:00.000 5 3
2010-02-25 00:00:00.000 5 3
2010-02-26 00:00:00.000 5 1
2010-02-27 00:00:00.000 5 1
2010-02-28 00:00:00.000 5 1
2010-03-01 00:00:00.000 5 1
2010-03-02 00:00:00.000 5 1
2010-03-03 00:00:00.000 5 1
2010-03-04 00:00:00.000 5 1
2010-03-05 00:00:00.000 5 1
2010-03-06 00:00:00.000 5 1
2010-03-07 00:00:00.000 5 1
2010-03-08 00:00:00.000 5 1
2010-03-09 00:00:00.000 5 1
2010-03-10 00:00:00.000 5 1
2010-03-11 00:00:00.000 5 1
2010-03-12 00:00:00.000 5 1
2010-03-13 00:00:00.000 5 1
2010-03-14 00:00:00.000 5 1
2010-03-15 00:00:00.000 5 1
2010-03-16 00:00:00.000 5 1
2010-03-17 00:00:00.000 5 1
2010-03-18 00:00:00.000 5 1
2010-03-19 00:00:00.000 5 1
2010-03-20 00:00:00.000 5 1
2010-03-21 00:00:00.000 5 1
2010-03-22 00:00:00.000 5 1
2010-03-23 00:00:00.000 5 1
2010-03-24 00:00:00.000 5 1
2010-03-25 00:00:00.000 5 1
2010-03-26 00:00:00.000 5 1
2010-03-27 00:00:00.000 5 1
2010-03-28 00:00:00.000 5 1
2010-03-29 00:00:00.000 5 1
2010-03-30 00:00:00.000 5 1
2010-03-31 00:00:00.000 5 0
2010-04-01 00:00:00.000 5 0
*/
insert into b values(1 ,'2010-01-01', '2010-1-20')
insert into b values(1 ,'2010-01-25', '2010-2-25')
insert into b values(2 ,'2010-01-01', '2010-1-20')
insert into b values(2 ,'2010-02-01', '2010-2-25')
insert into b values(3 ,'2010-01-01', '2010-3-30')
goSELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b declare @sdate datetime
declare @edate datetime
set @sdate = '2010-01-01'
set @edate = '2010-04-01'select k1.日期 , isnull(k2.广告牌总数,(select count(1) from b)) 广告牌总数 , isnull(k2.使用数,0) 使用数 from
(
select convert(varchar(10),dateadd(dd,n.id,@sdate),120) 日期 from # n where dateadd(dd,n.id,@sdate) <= @edate
) k1 left join
(
select convert(varchar(10),日期,120) 日期 , (select count(1) from b) 广告牌总数 , count(1) 使用数 from
(
select dateadd(dd,n.id,m.StartDate) 日期 from b m, # n where dateadd(dd,n.id,m.StartDate) <= m.EndDate
) t
group by convert(varchar(10),日期,120)
) k2
on k1.日期 = k2.日期
order by k1.日期drop table b , #/*
日期 广告牌总数 使用数
---------- ----------- -----------
2010-01-01 5 3
2010-01-02 5 3
2010-01-03 5 3
2010-01-04 5 3
2010-01-05 5 3
2010-01-06 5 3
2010-01-07 5 3
2010-01-08 5 3
2010-01-09 5 3
2010-01-10 5 3
2010-01-11 5 3
2010-01-12 5 3
2010-01-13 5 3
2010-01-14 5 3
2010-01-15 5 3
2010-01-16 5 3
2010-01-17 5 3
2010-01-18 5 3
2010-01-19 5 3
2010-01-20 5 3
2010-01-21 5 1
2010-01-22 5 1
2010-01-23 5 1
2010-01-24 5 1
2010-01-25 5 2
2010-01-26 5 2
2010-01-27 5 2
2010-01-28 5 2
2010-01-29 5 2
2010-01-30 5 2
2010-01-31 5 2
2010-02-01 5 3
2010-02-02 5 3
2010-02-03 5 3
2010-02-04 5 3
2010-02-05 5 3
2010-02-06 5 3
2010-02-07 5 3
2010-02-08 5 3
2010-02-09 5 3
2010-02-10 5 3
2010-02-11 5 3
2010-02-12 5 3
2010-02-13 5 3
2010-02-14 5 3
2010-02-15 5 3
2010-02-16 5 3
2010-02-17 5 3
2010-02-18 5 3
2010-02-19 5 3
2010-02-20 5 3
2010-02-21 5 3
2010-02-22 5 3
2010-02-23 5 3
2010-02-24 5 3
2010-02-25 5 3
2010-02-26 5 1
2010-02-27 5 1
2010-02-28 5 1
2010-03-01 5 1
2010-03-02 5 1
2010-03-03 5 1
2010-03-04 5 1
2010-03-05 5 1
2010-03-06 5 1
2010-03-07 5 1
2010-03-08 5 1
2010-03-09 5 1
2010-03-10 5 1
2010-03-11 5 1
2010-03-12 5 1
2010-03-13 5 1
2010-03-14 5 1
2010-03-15 5 1
2010-03-16 5 1
2010-03-17 5 1
2010-03-18 5 1
2010-03-19 5 1
2010-03-20 5 1
2010-03-21 5 1
2010-03-22 5 1
2010-03-23 5 1
2010-03-24 5 1
2010-03-25 5 1
2010-03-26 5 1
2010-03-27 5 1
2010-03-28 5 1
2010-03-29 5 1
2010-03-30 5 1
2010-03-31 5 0
2010-04-01 5 0(所影响的行数为 91 行)
*/
(PID int, PName varchar(10))
insert a表
select 1, '广告牌1' union all
select 2 , '广告牌2' union all
select 3 , '广告牌3' union all
select 4 , '广告牌4' union all
select 5 , '广告牌5' create table b表
(PID int, StartDate datetime, EndDate datetime)
insert b表
select 1 , '2010-01-01' , '2010-1-20' union all
select 1 , '2010-01-25' , '2010-2-25' union all
select 2 , '2010-01-01' , '2010-1-20' union all
select 2 , '2010-02-01' , '2010-2-25' union all
select 3 , '2010-01-01' , '2010-3-30' declare @d1 datetime,@d2 datetime
set @d1='2010-01-01'
set @d2='2010-04-01'select convert(varchar(10),dateadd(dd,m.number,@d1),120) 日期,
(select count(1) from a表) 广告牌总数,
count(b.PID) 使用数
from master..spt_values m left join b表 b on dateadd(dd,m.number,@d1) between b.StartDate and b.EndDate
where m.type='p' and m.number<=datediff(dd,@d1,@d2)
group by dateadd(dd,m.number,@d1)
order by dateadd(dd,m.number,@d1)/*日期 广告牌总数 使用数
2010-01-01 5 3
2010-01-02 5 3
...
2010-01-20 5 3
2010-01-21 5 1
...
2010-01-25 5 2
2010-01-26 5 2
...
2010-02-01 5 3
...
2010-02-25 5 3
...
2010-03-30 5 1
2010-04-01 5 0
*/
insert into b values(1 ,'2010-01-01', '2010-1-20')
insert into b values(1 ,'2010-01-25', '2010-2-25')
insert into b values(2 ,'2010-01-01', '2010-1-20')
insert into b values(2 ,'2010-02-01', '2010-2-25')
insert into b values(3 ,'2010-01-01', '2010-3-30')
goSELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b declare @sdate datetime
declare @edate datetime
set @sdate = '2010-01-01'
set @edate = '2010-04-01'select k1.日期 , (select count(1) from b) 广告牌总数 , isnull(count(k2.PID),0) 使用数 from
(
select convert(varchar(10),dateadd(dd,n.id,@sdate),120) 日期 from # n where dateadd(dd,n.id,@sdate) <= @edate
) k1 left join b k2
on k1.日期 between k2.StartDate and k2.EndDate
group by k1.日期
order by k1.日期drop table b , #/*
日期 广告牌总数 使用数
---------- ----------- -----------
2010-01-01 5 3
2010-01-02 5 3
2010-01-03 5 3
2010-01-04 5 3
2010-01-05 5 3
2010-01-06 5 3
2010-01-07 5 3
2010-01-08 5 3
2010-01-09 5 3
2010-01-10 5 3
2010-01-11 5 3
2010-01-12 5 3
2010-01-13 5 3
2010-01-14 5 3
2010-01-15 5 3
2010-01-16 5 3
2010-01-17 5 3
2010-01-18 5 3
2010-01-19 5 3
2010-01-20 5 3
2010-01-21 5 1
2010-01-22 5 1
2010-01-23 5 1
2010-01-24 5 1
2010-01-25 5 2
2010-01-26 5 2
2010-01-27 5 2
2010-01-28 5 2
2010-01-29 5 2
2010-01-30 5 2
2010-01-31 5 2
2010-02-01 5 3
2010-02-02 5 3
2010-02-03 5 3
2010-02-04 5 3
2010-02-05 5 3
2010-02-06 5 3
2010-02-07 5 3
2010-02-08 5 3
2010-02-09 5 3
2010-02-10 5 3
2010-02-11 5 3
2010-02-12 5 3
2010-02-13 5 3
2010-02-14 5 3
2010-02-15 5 3
2010-02-16 5 3
2010-02-17 5 3
2010-02-18 5 3
2010-02-19 5 3
2010-02-20 5 3
2010-02-21 5 3
2010-02-22 5 3
2010-02-23 5 3
2010-02-24 5 3
2010-02-25 5 3
2010-02-26 5 1
2010-02-27 5 1
2010-02-28 5 1
2010-03-01 5 1
2010-03-02 5 1
2010-03-03 5 1
2010-03-04 5 1
2010-03-05 5 1
2010-03-06 5 1
2010-03-07 5 1
2010-03-08 5 1
2010-03-09 5 1
2010-03-10 5 1
2010-03-11 5 1
2010-03-12 5 1
2010-03-13 5 1
2010-03-14 5 1
2010-03-15 5 1
2010-03-16 5 1
2010-03-17 5 1
2010-03-18 5 1
2010-03-19 5 1
2010-03-20 5 1
2010-03-21 5 1
2010-03-22 5 1
2010-03-23 5 1
2010-03-24 5 1
2010-03-25 5 1
2010-03-26 5 1
2010-03-27 5 1
2010-03-28 5 1
2010-03-29 5 1
2010-03-30 5 1
2010-03-31 5 0
2010-04-01 5 0(所影响的行数为 91 行)
*/
drop table a
gocreate table a
(
pid int,
pname varchar(20)
)
goinsert into a
select 1,'广告1'
union
select 2,'广告2'
union
select 3,'广告3'
union
select 4,'广告4'
union
select 5,'广告5'
goif OBJECT_ID('b') is not null
drop table b
gocreate table b
(
pid int,
starttime datetime,
endtime datetime
)
goinsert into b
select 1,'2010-01-01','2010-01-20'
union all
select 1,'2010-01-25','2010-02-25'
union all
select 2,'2010-01-01','2010-01-20'
union all
select 2,'2010-02-01','2010-02-25'
union all
select 3,'2010-01-01','2010-03-30'
goselect *
from a
goselect *
from b
goif OBJECT_ID('fun_stat') is not null
drop function fun_stat
gocreate function fun_stat(@date datetime,@starttime datetime,@endtime datetime)
returns @t table(dates datetime,sums int,counts int)
as
begin
declare @sum int
set @sum=(select count(distinct pname) from a)
while @date>=@starttime and @date<=@endtime
begin
insert into @t
select dates=@date,sums=@sum,counts=(select count(*) from b join a on b.pid=a.pid where starttime<=@date and endtime>=@date)
set @date=dateadd(day,1,@date)
end
return
end
goselect * from dbo.fun_stat('2010-01-01','2010-01-01','2010-04-01')
go
请问这句话是什么意思呢?还有,#是一个表么?执行完后为什么库没有这个表呢?
本人新手,请解答一下,谢谢了
这句话应该怎么理解呢??从这两个表里面来的列?
还是说什么意思呢??
这个syscolumns是哪里的表呢??