下面是一个独立的脚本,您可以使用它来尝试,而不会在生产中触及任何真实的数据库对象。代码的底部三分之一包含您要查找的连接的帮助。
SQL Server CE将允许您编写存储过程,而存储过程又可以用作报表的源。存储过程很好,因为它们可以接受输入参数,这是进行报告的理想选择。
-- create dummy Comments table for prototyping
create table #Comments (
ID int identity(1,1) not null,
Tutorial nvarchar(50) not null,
DateAdded datetime not null,
primary key clustered(DateAdded,ID,Tutorial)
);
-- populate dummy Comments table
declare @startDate datetime = '2000-01-01';
declare @endDate datetime = '2014-02-14';
declare @numTxns int = 5000;
set nocount on;
declare @numDays int = cast(@endDate as int) - cast(@startDate as int) + 1;
declare @i int = 1;
declare @j int = @i + @numTxns;
declare @rnd float;
while @i <= @j
begin
set @rnd = RAND();
insert into #Comments (Tutorial,DateAdded)
select
-- random tutorial titles
coalesce (
case when @rnd < .25 then 'foo' else null end,
case when @rnd between .5 and .75 then 'baz' else null end,
case when @rnd > .75 then 'qux' else null end,
'bar'
) as Tutorial,
-- random dates between @startDate and @endDate
cast(cast(rand() * @numDays + @startDate as int) as datetime) as DateAdded
set @i = @i + 1
end;
-- try deleting some months to see what happens
delete from #Comments
where DateAdded between '2013-11-01' and '2013-11-30'
or DateAdded between '2014-01-01' and '2014-01-31';
set nocount off;
go
-- ### following could easily be rewritten as a stored procedure
-- stored procedure parameters
declare @startDate datetime = '2000-01-01';
declare @endDate datetime = '2014-03-31';
-- pick only one option below
--declare @Tutorial nvarchar(50) = 'foo'; -- this only gets data for Tutorials called 'foo'
declare @Tutorial nvarchar(50) = 'all'; -- this gets data for all tutorials
-- begin stored procedure code
set nocount on;
-- this temp table is an alternative to
-- creating ***and maintaining*** a table full of dates,
-- months, etc., and cluttering up your database
-- in production, it will automatically delete itself
-- once the user has completed running the report.
create table #dates (
DateAdded datetime not null,
YearAdded int null,
MonthAdded int null,
primary key clustered (DateAdded)
);
-- now we put dates into #dates table
-- based on the parameters supplied by
-- the user running the report
declare @date datetime = @startDate;
while @date <= @endDate
begin
insert into #dates
select @date, YEAR(@date), MONTH(@date);
set @date = @date + 1;
end;
-- ## Why put every day of the month in this table?
-- ## I asked for a monthy report, not daily!
-- Yes, but looping through dates is easier, simply add 1 for the next date.
-- You can always build a monthly summary table later if you'd like.
-- This *is* kind of a brute-force solution, but easy to write.
-- More answers to this question in the code below, where they'll make more sense.
set nocount off;
-- now we return the data to the user
-- any month with no Tutorials will still show up in the report
-- but the counts will show as zero
select YearAdded, MonthAdded, SUM(Count_From_Comments) as Count_From_Comments,
SUM(foo) as Count_Foo, SUM(bar) as Count_Bar,
SUM(baz) as Count_Baz, SUM(qux) as Count_Qux
from (
-- ## you can reuse the following code for a detail report by day
-- ## another answer to 'Why not by month?' from above
-- start daily report code
select t1.DateAdded, t1.YearAdded, t1.MonthAdded, t2.Tutorial,
coalesce(Count_From_Comments,0) as Count_From_Comments,
case when t2.Tutorial = 'foo' then 1 else 0 end as foo,
case when t2.Tutorial = 'bar' then 1 else 0 end as bar,
case when t2.Tutorial = 'baz' then 1 else 0 end as baz,
case when t2.Tutorial = 'qux' then 1 else 0 end as qux
from #dates as t1 -- no where clause needed because #dates only contains the ones we want
left join ( -- left join here so that we get all dates, not just ones in #Comments
select *, 1 AS Count_From_Comments
from #Comments
where @Tutorial in (Tutorial,'all')
) as t2
on t1.DateAdded = t2.DateAdded -- ## join on one field instead of two, another answer to 'Why not by month?' from above
-- end daily report code
) as qDetail
group by YearAdded, MonthAdded
order by YearAdded, MonthAdded
-- end stored procedure code
go
-- ## Not required in production code,
-- ## but handy when testing this script.
drop table #dates;
-- #### Since this will be a real table in production
-- #### we definitely only want this for testing!
drop table #Comments;
go
编码愉快。
