数据库牛人多多，请教下以前的几道存储过程面实题，谢谢！

Question

. 写一存储过程返回从2008-1-1号到2008-1-15号的电量信息，返回包括如下字段：ValueTime、MeterId、MeterName、BeiLv、ZValue、用电量，其中用电量为当前ValueTime的ZValue与前一点的ZValue之差，如MeterDayValue没有前一天的记录，则取上前天的，依此类推，如MeterDayValue表中不存在前一点表码记录，则将“用电量”置零。 
---
if object_id('Meter') is not null
drop table Meter
go
create table Meter (MeterId bigint identity(1,1),MeterName varchar(50),BeiLv int)
insert Meter select '电表一','1'
   union all select '电表二','2'
   union all select '电表三','3'
go
--select * from Meterif object_id('MeterDayValue') is not null
drop table MeterDayValue
go
create table MeterDayValue (ValueId bigint identity(1,1),MeterId bigint ,ZValue float,ValueTime datetime)
insert MeterDayValue select 1,50,'2008-01-01'
           union all select 1,100,'2008-01-02' 
           union all select 1,200,'2008-01-05'
           union all select 2,20,'2008-01-02' 
           union all select 2,40,'2008-01-03'
           union all select 2,100,'2008-01-05' 
           union all select 2,200,'2008-01-10' 
           union all select 3,100,'2008-01-03'
           union all select 3,200,'2008-01-04' 
           union all select 3,300,'2008-01-05'
--select * from MeterDayValue 
select ValueTime,b.MeterId,MeterName,BeiLv,ZValue,
用电量=(ZValue - isnull((select top 1 ZValue from meterdayvalue where MeterId = b.MeterId and valueid < b.valueid order by valueid desc),zvalue))
from Meter a right join MeterDayValue b 
on a.BeiLv = b.MeterId
/*
ValueTime                                              MeterId              MeterName                                          BeiLv       ZValue                                                用电量                                                   
------------------------------------------------------ -------------------- -------------------------------------------------- ----------- ----------------------------------------------------- ----------------------------------------------------- 
2008-01-01 00:00:00.000                                1                    电表一                                                1           50.0                                                  0.0
2008-01-02 00:00:00.000                                1                    电表一                                                1           100.0                                                 50.0
2008-01-05 00:00:00.000                                1                    电表一                                                1           200.0                                                 100.0
2008-01-02 00:00:00.000                                2                    电表二                                                2           20.0                                                  0.0
2008-01-03 00:00:00.000                                2                    电表二                                                2           40.0                                                  20.0
2008-01-05 00:00:00.000                                2                    电表二                                                2           100.0                                                 60.0
2008-01-10 00:00:00.000                                2                    电表二                                                2           200.0                                                 100.0
2008-01-03 00:00:00.000                                3                    电表三                                                3           100.0                                                 0.0
2008-01-04 00:00:00.000                                3                    电表三                                                3           200.0                                                 100.0
2008-01-05 00:00:00.000                                3                    电表三                                                3           300.0                                                 100.0（所影响的行数为 10 行）
*/

Answer 1

針對每一個電表計算：[code=HTML]
c. 写一存储过程返回与前一天用电量对比用电量增幅超过30%的日期与用户信息，返回如下字段：日期、MeterId、MeterName、增幅(如31.5%) 
前一天?還是前一個日期use tempdb
goif object_id('Meter') is not null
drop table Meter
go
create table Meter (MeterId bigint identity(1,1),MeterName nvarchar(50),BeiLv int)
insert Meter select N'电表一','1'
   union all select N'电表二','2'
   union all select N'电表三','3'
goif object_id('MeterDayValue') is not null
drop table MeterDayValue
go
create table MeterDayValue (ValueId bigint identity(1,1),MeterId bigint ,ZValue float,ValueTime datetime)
insert MeterDayValue select 1,50,'2008-01-01'
           union all select 1,100,'2008-01-02' 
           union all select 1,200,'2008-01-05'
           union all select 2,20,'2008-01-02' 
           union all select 2,40,'2008-01-03'
           union all select 2,100,'2008-01-05' 
           union all select 2,200,'2008-01-10' 
           union all select 3,100,'2008-01-03'
           union all select 3,200,'2008-01-04' 
           union all select 3,300,'2008-01-05'
goselect 
b.ValueTime,a.MeterId,a.MeterName,a.BeiLv,b.ZValue,
[用电量]=b.ZValue-isnull((select top 1 ZValue from MeterDayValue where MeterId=a.MeterId and ValueTime<b.ValueTime order by ValueTime desc),0) 
from 
Meter a
join
MeterDayValue b on a.MeterId=b.MeterId--2000用臨時表生成日期
;with  C
as
(select cast('2008-01-01' as datetime) Date
union all
select Date+1 from C where Date<'2008-01-15') 
select 
t1.MeterId,t1.MeterName,t1.Date as [用电量为零的日期]
from 
(select  * from  c,Meter)T1
left join
MeterDayValue t2 on t1.MeterId=t2.MeterId and t1.Date=t2.ValueTime
where
t2.ValueId is null order by t1.MeterId,[用电量为零的日期][/code]

Answer 2

前兩個
select 
b.ValueTime,a.MeterId,a.MeterName,a.BeiLv,b.ZValue,
[用电量]=b.ZValue-isnull((select top 1 ZValue from MeterDayValue where MeterId=a.MeterId and ValueTime<b.ValueTime order by ValueTime desc),0) 
from 
Meter a
join
MeterDayValue b on a.MeterId=b.MeterId--2000用臨時表生成日期
;with  C
as
(select cast('2008-01-01' as datetime) Date
union all
select Date+1 from C where Date<'2008-01-15') 
select 
t1.MeterId,t1.MeterName,t1.Date as [用电量为零的日期]
from 
(select  * from  c,Meter)T1
left join
MeterDayValue t2 on t1.MeterId=t2.MeterId and t1.Date=t2.ValueTime
where
t2.ValueId is null order by t1.MeterId,[用电量为零的日期]

Answer 3

b. 写一存储过程列出在1号至15号连续三天以上“用电量”为零的用户列表，返回如下字段：MeterId, MeterName, 用电量为零的日期（如果1号,3号,5号用电量为零则此字段内容为1,3,5）。--
if object_id('Meter') is not null
drop table Meter
go
create table Meter (MeterId bigint identity(1,1),MeterName varchar(50),BeiLv int)
insert Meter select '电表一','1'
   union all select '电表二','2'
   union all select '电表三','3'
go
--select * from Meterif object_id('MeterDayValue') is not null
drop table MeterDayValue
go
create table MeterDayValue (ValueId bigint identity(1,1),MeterId bigint ,ZValue float,ValueTime datetime)
insert MeterDayValue select 1,50,'2008-01-01'
           union all select 1,100,'2008-01-02' 
           union all select 1,200,'2008-01-05'
           union all select 2,20,'2008-01-02' 
           union all select 2,40,'2008-01-03'
           union all select 2,100,'2008-01-05' 
           union all select 2,200,'2008-01-10' 
           union all select 3,100,'2008-01-03'
           union all select 3,200,'2008-01-04' 
           union all select 3,300,'2008-01-05'go
select
    distinct c.MeterName 
from
    (select * from MeterDayValue c where not exists(select 1 from MeterDayValue where MeterId=c.MeterId and datediff(d,ValueTime,c.ValueTime)= -1)) a,
    (select * from MeterDayValue d where not exists(select 1 from MeterDayValue where MeterId=d.MeterId and datediff(d,ValueTime,d.ValueTime)=  1)) b,
    Meter cwhere
    a.MeterId=b.MeterId and a.ValueTime<=b.ValueTime and a.MeterId = c.BeiLv
group by
    c.MeterName,a.ValueTime
having min(b.ValueTime) - 3 <= a.ValueTime
/*
MeterName                                          
-------------------------------------------------- 
电表二
电表一（所影响的行数为 2 行）*/

Answer 4

第三题不会当时也没问清楚，但是应该是不能和没用电的天比较吧
只会第一道，后面的都不会，自己那个时候做的第一题 select a.MeterId,a.MeterName,a.BeiLv,b.ZValue,b.ValueTime,isnull((b.Zvalue-(select * from (select top 1 c.ZValue from MeterDayValue c where c.ValueTime <b.ValueTime and c.MeterId=b.MeterId order by c.ValueTime desc )z)),0) as 用电 from Meter a inner join MeterDayValue b on a.MeterId=b.MeterId where b.ValueTime between '2008-01-01' and '2008-01-05' 

Answer 5

在語句前加上
create proc P1
as
....
------------------------------題3猜一個use tempdb
goif object_id('Meter') is not null
drop table Meter
go
create table Meter (MeterId bigint identity(1,1),MeterName nvarchar(50),BeiLv int)
insert Meter select N'电表一','1'
   union all select N'电表二','2'
   union all select N'电表三','3'
goif object_id('MeterDayValue') is not null
drop table MeterDayValue
go
create table MeterDayValue (ValueId bigint identity(1,1),MeterId bigint ,ZValue float,ValueTime datetime)
insert MeterDayValue select 1,50,'2008-01-01'
           union all select 1,100,'2008-01-02' 
           union all select 1,200,'2008-01-05'
           union all select 2,20,'2008-01-02' 
           union all select 2,40,'2008-01-03'
           union all select 2,100,'2008-01-05' 
           union all select 2,200,'2008-01-10' 
           union all select 3,100,'2008-01-03'
           union all select 3,200,'2008-01-04' 
           union all select 3,300,'2008-01-05'
go
--2000用臨時表生成日期
;with  C
as
(select cast('2008-01-01' as datetime) Date
union all
select Date+1 from C where Date<'2008-01-15')
,C2 as ( 
select 
t1.MeterId,t1.MeterName,t1.Date,t2.ZValue,row_number()over(partition by t1.MeterId order by t1.Date ) row
from 
(select * from  c,Meter)T1
left join
MeterDayValue t2 on t1.MeterId=t2.MeterId and t1.Date=t2.ValueTime)
select 
[日期]=a.Date,a.MeterId,a.MeterName,[增幅]=case when a.row=1 then '0%' else 
ltrim(str((isnull(a.ZValue,0)-isnull(b.ZValue,0))*1.0/isnull(b.ZValue,1)*100,18,2 ))+'%'end
from 
C2 a
left join
C2 b on a.MeterId=b.MeterId and a.Date=b.Date+1日期                      MeterId              MeterName                                          增幅
----------------------- -------------------- -------------------------------------------------- -------------------
2008-01-01 00:00:00.000 1                    电表一                                                0%
2008-01-02 00:00:00.000 1                    电表一                                                100.00%
2008-01-03 00:00:00.000 1                    电表一                                                -100.00%
2008-01-04 00:00:00.000 1                    电表一                                                0.00%
2008-01-05 00:00:00.000 1                    电表一                                                20000.00%
2008-01-06 00:00:00.000 1                    电表一                                                -100.00%
2008-01-07 00:00:00.000 1                    电表一                                                0.00%
2008-01-08 00:00:00.000 1                    电表一                                                0.00%
2008-01-09 00:00:00.000 1                    电表一                                                0.00%
2008-01-10 00:00:00.000 1                    电表一                                                0.00%
2008-01-11 00:00:00.000 1                    电表一                                                0.00%
2008-01-12 00:00:00.000 1                    电表一                                                0.00%
2008-01-13 00:00:00.000 1                    电表一                                                0.00%
2008-01-14 00:00:00.000 1                    电表一                                                0.00%
2008-01-15 00:00:00.000 1                    电表一                                                0.00%
2008-01-01 00:00:00.000 2                    电表二                                                0%
2008-01-02 00:00:00.000 2                    电表二                                                2000.00%
2008-01-03 00:00:00.000 2                    电表二                                                100.00%
2008-01-04 00:00:00.000 2                    电表二                                                -100.00%
2008-01-05 00:00:00.000 2                    电表二                                                10000.00%
2008-01-06 00:00:00.000 2                    电表二                                                -100.00%
2008-01-07 00:00:00.000 2                    电表二                                                0.00%
2008-01-08 00:00:00.000 2                    电表二                                                0.00%
2008-01-09 00:00:00.000 2                    电表二                                                0.00%
2008-01-10 00:00:00.000 2                    电表二                                                20000.00%
2008-01-11 00:00:00.000 2                    电表二                                                -100.00%
2008-01-12 00:00:00.000 2                    电表二                                                0.00%
2008-01-13 00:00:00.000 2                    电表二                                                0.00%
2008-01-14 00:00:00.000 2                    电表二                                                0.00%
2008-01-15 00:00:00.000 2                    电表二                                                0.00%
2008-01-01 00:00:00.000 3                    电表三                                                0%
2008-01-02 00:00:00.000 3                    电表三                                                0.00%
2008-01-03 00:00:00.000 3                    电表三                                                10000.00%
2008-01-04 00:00:00.000 3                    电表三                                                100.00%
2008-01-05 00:00:00.000 3                    电表三                                                50.00%
2008-01-06 00:00:00.000 3                    电表三                                                -100.00%
2008-01-07 00:00:00.000 3                    电表三                                                0.00%
2008-01-08 00:00:00.000 3                    电表三                                                0.00%
2008-01-09 00:00:00.000 3                    电表三                                                0.00%
2008-01-10 00:00:00.000 3                    电表三                                                0.00%
2008-01-11 00:00:00.000 3                    电表三                                                0.00%
2008-01-12 00:00:00.000 3                    电表三                                                0.00%
2008-01-13 00:00:00.000 3                    电表三                                                0.00%
2008-01-14 00:00:00.000 3                    电表三                                                0.00%
2008-01-15 00:00:00.000 3                    电表三                                                0.00%(45 個資料列受到影響)