sql 月报表统计 在线等！

Question

数据表格式，有三个字段：日期 Varchar(10),时间 Varchar(10),价格 float
表中存储的内容是每两小时一条记录，每一天存储12条记录，参见下列数据形式：
    日期               时间             价格
2009-05-22        00:00:00        12.55
2009-05-22        02:00:00        26.55
2009-05-22        04:00:00        10.52
2009-05-22        06:00:00        23.55
2009-05-22        08:00:00        52.65
2009-05-22        10:00:00        23.21
2009-05-22        12:00:00        32.55
2009-05-22        14:00:00        15.54
2009-05-22        16:00:00        18.44
2009-05-22        18:00:00        20.10
2009-05-22        20:00:00        34.48
2009-05-22        22:00:00        18.55
2009-05-23        00:00:00        42.51
2009-05-23        02:00:00        36.64
2009-05-23        04:00:00        66.22
2009-05-23        06:00:00        54.41
……                 ……              ……最后要实现的查询结果是：
对这种的数据进行“月份报表的生成”：我想查询5月份的报表，格式如下：其中的日期要根据查询月份的天数28天还是31天？平均价格要求只对大于零的进行平均。
    日期           平均价格
2009-05-01       0
2009-05-02       0
2009-05-03       0
2009-05-04       0
2009-05-05       0
……                ……
2009-05-22       24.06
2009-05-23       49.97
2009-05-24       0
2009-05-25       0
……                ……
2009-05-30       0
2009-05-31       0
   合计            37.02

Answer 1

DECLARE @TB TABLE([日期] DATETIME, [时间] VARCHAR(8), [价格] DECIMAL(10,2))
INSERT @TB 
SELECT '2009-05-22', '00:00:00', 12.55 UNION ALL 
SELECT '2009-05-22', '02:00:00', 26.55 UNION ALL 
SELECT '2009-05-22', '04:00:00', 10.52 UNION ALL 
SELECT '2009-05-22', '06:00:00', 23.55 UNION ALL 
SELECT '2009-05-22', '08:00:00', 52.65 UNION ALL 
SELECT '2009-05-22', '10:00:00', 23.21 UNION ALL 
SELECT '2009-05-22', '12:00:00', 32.55 UNION ALL 
SELECT '2009-05-22', '14:00:00', 15.54 UNION ALL 
SELECT '2009-05-22', '16:00:00', 18.44 UNION ALL 
SELECT '2009-05-22', '18:00:00', 20.10 UNION ALL 
SELECT '2009-05-22', '20:00:00', 34.48 UNION ALL 
SELECT '2009-05-22', '22:00:00', 18.55 UNION ALL 
SELECT '2009-05-23', '00:00:00', 42.51 UNION ALL 
SELECT '2009-05-23', '02:00:00', 36.64 UNION ALL 
SELECT '2009-05-23', '04:00:00', 66.22 UNION ALL 
SELECT '2009-05-23', '06:00:00', 54.41SELECT DATEADD(DAY,ID-1,'2009-05-01') AS 日期,ISNULL([价格],0) AS 平均价格 
FROM (
SELECT ID=ROW_NUMBER() OVER (ORDER BY GETDATE()) FROM sysobjects) A
LEFT JOIN (
SELECT [日期],ROUND(SUM([价格])/SUM(CASE WHEN [价格]=0 THEN 0 ELSE 1 END),2) AS [价格]
FROM @TB 
GROUP BY [日期]) B
ON CONVERT(VARCHAR(10),DATEADD(DAY,ID-1,'2009-05-01'),120)=[日期]
WHERE DATEDIFF(MONTH,'2009-05-01',DATEADD(DAY,ID-1,'2009-05-01'))=0

Answer 2

--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
create table [ta] (日期 Varchar(10),时间 Varchar(10),价格 numeric(4,2))
insert into [ta]
select '2009-05-22','00:00:00',12.55 union all
select '2009-05-22','02:00:00',26.55 union all
select '2009-05-22','04:00:00',10.52 union all
select '2009-05-22','06:00:00',23.55 union all
select '2009-05-22','08:00:00',52.65 union all
select '2009-05-22','10:00:00',23.21 union all
select '2009-05-22','12:00:00',32.55 union all
select '2009-05-22','14:00:00',15.54 union all
select '2009-05-22','16:00:00',18.44 union all
select '2009-05-22','18:00:00',20.10 union all
select '2009-05-22','20:00:00',34.48 union all
select '2009-05-22','22:00:00',18.55 union all
select '2009-05-23','00:00:00',42.51 union all
select '2009-05-23','02:00:00',36.64 union all
select '2009-05-23','04:00:00',66.22 union all
select '2009-05-23','06:00:00',54.41
gocreate proc wsp
@year int,
@month int
as
declare @t table(dt datetime)
declare @end datetime,@i int
select @end=dateadd(dd,-1,dateadd(mm,1,ltrim(@year)+'-'+ltrim(@month)+'-'+'01'))
set @i=0
while(dateadd(dd,@i,ltrim(@year)+'-'+ltrim(@month)+'-'+'01')<=@end)
begin
insert into @t select dateadd(dd,@i,ltrim(@year)+'-'+ltrim(@month)+'-'+'01')
set @i=@i+1
end
select 日期=convert(varchar(10),a.dt,120),平均价格=isnull(avg(b.价格),0) from @t a left join ta b on datediff(dd,a.dt,b.日期)=0 group by convert(varchar(10),a.dt,120)
goexec wsp 2009,5

Answer 3

DECLARE @TB TABLE([日期] DATETIME, [时间] VARCHAR(8), [价格] DECIMAL(10,2))
INSERT @TB 
SELECT '2009-05-22', '00:00:00', 12.55 UNION ALL 
SELECT '2009-05-22', '02:00:00', 26.55 UNION ALL 
SELECT '2009-05-22', '04:00:00', 10.52 UNION ALL 
SELECT '2009-05-22', '06:00:00', 23.55 UNION ALL 
SELECT '2009-05-22', '08:00:00', 52.65 UNION ALL 
SELECT '2009-05-22', '10:00:00', 23.21 UNION ALL 
SELECT '2009-05-22', '12:00:00', 32.55 UNION ALL 
SELECT '2009-05-22', '14:00:00', 15.54 UNION ALL 
SELECT '2009-05-22', '16:00:00', 18.44 UNION ALL 
SELECT '2009-05-22', '18:00:00', 20.10 UNION ALL 
SELECT '2009-05-22', '20:00:00', 34.48 UNION ALL 
SELECT '2009-05-22', '22:00:00', 18.55 UNION ALL 
SELECT '2009-05-23', '00:00:00', 42.51 UNION ALL 
SELECT '2009-05-23', '02:00:00', 36.64 UNION ALL 
SELECT '2009-05-23', '04:00:00', 66.22 UNION ALL 
SELECT '2009-05-23', '06:00:00', 54.41SELECT CASE WHEN 日期 IS NULL AND 平均价格 IS NULL THEN N'合计' ELSE ISNULL(CONVERT(VARCHAR(10),日期,120),'') END AS 日期,
   RTRIM(ISNULL(CAST(ROUND(平均价格,2) AS FLOAT),CAST(ROUND(SUM(平均价格)/SUM(CASE WHEN 平均价格=0 THEN 0 ELSE 1 END),2) AS FLOAT))) AS 平均价格
FROM (
SELECT DATEADD(DAY,ID-1,'2009-05-01') AS 日期,ISNULL([价格],0) AS 平均价格 
FROM (
SELECT ID=ROW_NUMBER() OVER (ORDER BY GETDATE()) FROM sysobjects) A
LEFT JOIN (
SELECT [日期],SUM([价格])/SUM(CASE WHEN [价格]=0 THEN 0 ELSE 1 END) AS [价格]
FROM @TB 
GROUP BY [日期]) B
ON CONVERT(VARCHAR(10),DATEADD(DAY,ID-1,'2009-05-01'),120)=[日期]
WHERE DATEDIFF(MONTH,'2009-05-01',DATEADD(DAY,ID-1,'2009-05-01'))=0
) T
GROUP BY 日期,平均价格 WITH ROLLUP
HAVING (日期 IS NULL AND 平均价格 IS NULL) OR (日期 IS NOT NULL AND 平均价格 IS NOT NULL)
/*
日期         平均价格
---------- -----------------------
2009-05-01 0
2009-05-02 0
2009-05-03 0
2009-05-04 0
2009-05-05 0
2009-05-06 0
2009-05-07 0
2009-05-08 0
2009-05-09 0
2009-05-10 0
2009-05-11 0
2009-05-12 0
2009-05-13 0
2009-05-14 0
2009-05-15 0
2009-05-16 0
2009-05-17 0
2009-05-18 0
2009-05-19 0
2009-05-20 0
2009-05-21 0
2009-05-22 24.06
2009-05-23 49.95
2009-05-24 0
2009-05-25 0
2009-05-26 0
2009-05-27 0
2009-05-28 0
2009-05-29 0
2009-05-30 0
2009-05-31 0
合计         37
*/

Answer 4

Create table tb(日期 Varchar(10),时间 Varchar(10),价格 float)insert tb values('2009-05-22','00:00:00','12.55')
insert tb values('2009-05-22','02:00:00','26.55')
insert tb values('2009-05-22','04:00:00','10.52')
insert tb values('2009-05-22','06:00:00','23.55')
insert tb values('2009-05-22','08:00:00','52.65')
insert tb values('2009-05-22','10:00:00','23.21')
insert tb values('2009-05-22','12:00:00','32.55')
insert tb values('2009-05-22','14:00:00','15.54')
insert tb values('2009-05-22','16:00:00','18.44')
insert tb values('2009-05-22','18:00:00','20.10')
insert tb values('2009-05-22','20:00:00','34.48')
insert tb values('2009-05-22','22:00:00','18.55')
insert tb values('2009-05-23','00:00:00','42.51')
insert tb values('2009-05-23','02:00:00','36.64')
insert tb values('2009-05-23','04:00:00','66.22')
insert tb values('2009-05-23','06:00:00','54.41')
GO
--创建一个存储过程
Create PROC UP_Con
@Month int
as
declare @Day int,@BeginDate datetime,@EndDate datetime,@i int
set @Day=Day(Convert(datetime,cast(Year(getdate()) as Char(4))+'-'+cast(@Month+1 as VarChar(2))+'-01')-1)
set @BeginDate=cast(Year(getdate()) as Char(4))+'-'+cast(@Month as VarChar(2))+'-01' --今年
set @EndDate=Convert(datetime,cast(Year(getdate()) as Char(4))+'-'+cast(@Month+1 as VarChar(2))+'-01')-1 --今年 create table #t(Date varchar(10))
set @i=1
while @i<=@Day
begin
if @I<10
insert #t values(Convert(varchar(8),@BeginDate,120)+'0'+Cast(@I as char(1)))
else
insert #t values(Convert(varchar(8),@BeginDate,120)+Cast(@I as char(2)))
set @i=@i+1
end
;with a as(
select b.Date 日期,isnull(a.平均价格,0.000) 平均价格 from (select 日期,avg(价格) as 平均价格 from tb Group by 日期)a right join #t b on a.日期=b.date 
)
select * from a union all
select '合计',(select avg(nullif(平均价格,0)) from a)
GO
--调用
exec UP_Con 5 --参数为要查询的月份，默认是今年
/*
日期         平均价格
---------- ----------------------
2009-05-01 0
2009-05-02 0
2009-05-03 0
2009-05-04 0
2009-05-05 0
2009-05-06 0
2009-05-07 0
2009-05-08 0
2009-05-09 0
2009-05-10 0
2009-05-11 0
2009-05-12 0
2009-05-13 0
2009-05-14 0
2009-05-15 0
2009-05-16 0
2009-05-17 0
2009-05-18 0
2009-05-19 0
2009-05-20 0
2009-05-21 0
2009-05-22 24.0575
2009-05-23 49.945
2009-05-24 0
2009-05-25 0
2009-05-26 0
2009-05-27 0
2009-05-28 0
2009-05-29 0
2009-05-30 0
2009-05-31 0
合计         37.00125
*/

Answer 5

DECLARE @a TABLE(a VARCHAR(10),b VARCHAR(8),c FLOAT)
INSERT @a SELECT '2009-05-22','00:00:00',12.55 
union all select '2009-05-22','02:00:00',26.55 
union all select '2009-05-22','04:00:00',10.52 
union all select '2009-05-22','06:00:00',23.55 
union all select '2009-05-22','08:00:00',52.65 
union all select '2009-05-22','10:00:00',23.21 
union all select '2009-05-22','12:00:00',32.55 
union all select '2009-05-22','14:00:00',15.54 
union all select '2009-05-22','16:00:00',18.44 
union all select '2009-05-22','18:00:00',20.10 
union all select '2009-05-22','20:00:00',34.48 
union all select '2009-05-22','22:00:00',18.55 
union all select '2009-05-23','00:00:00',42.51 
union all select '2009-05-23','02:00:00',36.64 
union all select '2009-05-23','04:00:00',66.22 
union all select '2009-05-23','06:00:00',54.41 DECLARE @Year   INT, @month  INT SET @Year = 2009 
SET @month = 5DECLARE @b TABLE(id INT IDENTITY(1, 1), b INT)
INSERT @b SELECT TOP 31 0 FROM   syscolumns sSELECT isnull(aa.d,'合计') a,isnull(c,0) c
FROM   (SELECT LTRIM(@Year) + '-' + RIGHT(100 + @month, 2) + '-' + RIGHT(100 + ID, 2) d FROM   @b)aa
       full JOIN (select a,AVG(c) c from @a WHERE YEAR(a)=@Year AND MONTH(a)=@month AND  c>0 GROUP BY a WITH  ROLLUP) bb  ON  aa.d = bb.a
WHERE d IS NULL OR  ISDATE(d) = 1
--result
/*
a                      c                              
---------------------- ------------------------------ 
2009-05-01             0.0
2009-05-02             0.0
2009-05-03             0.0
2009-05-04             0.0
2009-05-05             0.0
2009-05-06             0.0
2009-05-07             0.0
2009-05-08             0.0
2009-05-09             0.0
2009-05-10             0.0
2009-05-11             0.0
2009-05-12             0.0
2009-05-13             0.0
2009-05-14             0.0
2009-05-15             0.0
2009-05-16             0.0
2009-05-17             0.0
2009-05-18             0.0
2009-05-19             0.0
2009-05-20             0.0
2009-05-21             0.0
2009-05-22             24.057500000000001
2009-05-23             49.945
2009-05-24             0.0
2009-05-25             0.0
2009-05-26             0.0
2009-05-27             0.0
2009-05-28             0.0
2009-05-29             0.0
2009-05-30             0.0
2009-05-31             0.0
合计                     30.529375000000002（所影响的行数为 32 行）*/