急求一个简单的存储过程

Question

建立一个存储过程，名为sp_MonthReport_Calculate我现在有表字段如下
ID      Temperature      MaxTemperature      MaxTemperatureTime      RecorderID      nDate(日期型)
我传入一个日期型的开始时间BeginDate和结束时间EndDate，和RecorderID(编号)，要求返回从BeginDate到EndDate间每天的记录，Temperature字段返回Temperature的平均值，MaxTemperature字段返回MaxTemperature的最大值，
MaxTemperatureTime返回MaxTemperature最大值出现的最早的对应时间，nDate字段记录从BeginDate到EndDate间每天的那个日期。如果BeginDate到EndDate中无法检索到数据的话，那么仅填写nDate值，其余字段值为空。返回值为BeginDate到EndDate间计算后得出的每一天的记录

Answer 1

ID      Temperature      MaxTemperature      MaxTemperatureTime      RecorderID      nDate(日期型) 
1          1.2               1.5                  11:22                 00001      2008-10-22 11:21
2          1.4               2.1                  11:23                 00001      2008-10-22 11:22
3          1.4               2.8                  08:23                 00001      2008-10-23 08:23
4          1.4               2.1                  11:23                 00001      2008-10-23 11:22
比如这是一天的数据
起始日期为2008-10-20    结束日期为2008-11-01   传入的RecorderID=00001
那么数据计算结果应该为ID      Temperature      MaxTemperature      MaxTemperatureTime      RecorderID      nDate(日期型)
           1.3(平均)          2.1                  11:23                00001          2008-10-22  
           1.4                2.8                  08:23                00001          2008-10-23 

Answer 2

请自行将语句建为存储过程。IF OBJECT_ID('tb','u') IS NOT NULL
DROP TABLE tb
GOCREATE TABLE tb(ID INT, Temperature FLOAT, MaxTemperature FLOAT,MaxTemperatureTime VARCHAR(5),RecorderID VARCHAR(5), nDate DATETIME)
INSERT tb SELECT 1        ,  1.2,              1.5   ,               '11:22',                '00001'   ,   '2008-10-22 11:21'
UNION ALL SELECT 2       ,   1.4 ,             2.1  ,                '11:23' ,               '00001'  ,    '2008-10-22 11:22'
UNION ALL SELECT 3      ,    1.4  ,            2.8 ,                 '08:23'  ,              '00001' ,     '2008-10-23 08:23'
UNION ALL SELECT 4     ,     1.4   ,           2.1,                  '11:23'   ,             '00001',      '2008-10-23 11:22' 
GO
SELECT * FROM tb
GO
--起始日期为2008-10-20    结束日期为2008-11-01  传入的RecorderID=00001 
DECLARE @st DATETIME,@ed DATETIME,@ID VARCHAR(5)
SELECT @st='2008-10-20',@ed='2008-11-1',@id='0001'SELECT AVG(Temperature),MAX(MaxTemperature),MAX(MaxTemperatureTime),CONVERT(VARCHAR(10),nDate) FROM tb
WHERE nDate BETWEEN @st AND DATEADD(ms,-1,DATEADD(dd,1,@ed))
GROUP BY RecorderID,CONVERT(VARCHAR(10),nDate)

Answer 3

if object_id('tempdb..#')IS NOT NULL DROP TABLE #
GO
CREATE TABLE #(ID  int ,     Temperature  decimal(10,2),     MaxTemperature decimal(10,2),      MaxTemperatureTime VARCHAR(10),      RecorderID varchar(10),      nDate datetime)
INSERT # SELECT 1,          1.2    ,          1.5     ,             '11:22' ,               '00001'  ,    '2008-10-22 11:21' 
INSERT # SELECT 2,          1.4   ,           2.1     ,             '11:23' ,               '00001'  ,    '2008-10-22 11:22' 
INSERT # SELECT 3,          1.4   ,           2.8     ,             '08:23' ,               '00001'  ,    '2008-10-23 08:23' 
INSERT # SELECT 4,          1.4   ,           2.1     ,             '11:23' ,               '00001'  ,    '2008-10-23 11:22' 
--select * from 
SELECT  Temperature=AVG(Temperature),MaxTemperature=MAX(MaxTemperature),MaxTemperatureTime=MAX(MaxTemperatureTime),RecorderID ,CONVERT(VARCHAR(10),NDATE,120) Ndate
FROM # WHERE CONVERT(VARCHAR(10),NDATE,120) between '2008-10-20' and '2008-11-01'  GROUP BY RecorderID  ,CONVERT(VARCHAR(10),NDATE,120)
/*
Temperature                              MaxTemperature MaxTemperatureTime RecorderID Ndate      
---------------------------------------- -------------- ------------------ ---------- ---------- 
1.300000                                 2.10           11:23              00001      2008-10-22
1.400000                                 2.80           11:23              00001      2008-10-23*/

Answer 4

楼上两位大哥，可能问题是我没有说清楚，BeginDate是一个月的第一天
EndDate是第二个月的第一天MaxTemperatureTime是检索MaxTemperature的最大值对应的第一条记录的时间，比如说ID      Temperature      MaxTemperature      MaxTemperatureTime      RecorderID      nDate(日期型) 
1          1.2              1.5                  11:22                00001      2008-10-22 11:21 
2          1.4              2.1                  11:23                00001      2008-10-22 11:22 
3          1.4              2.1                  11:24                00001      2008-10-22 11:24 MaxTemperature的最大值是2.1那么MaxTemperatureTime就应该是11:23(这个时间以nDate为准，从小到大)
第一天的数据生成一条记录，也就是说应该有30条左右的记录。

Answer 5

楼主都用上 sql2005以上的版本了，羡慕ing.
我们这的老大愣是不让用。

Answer 6

前面的帖子你咋看出来我用的哪个版本？我用的是sql server 2008

Answer 7

IF OBJECT_ID('tb','u') IS NOT NULL
  DROP TABLE tb 
GO 
CREATE TABLE tb(ID INT, Temperature FLOAT, MaxTemperature FLOAT,MaxTemperatureTime VARCHAR(5),RecorderID VARCHAR(5), nDate DATETIME) 
INSERT tb SELECT 1 , 1.2, 1.5 , '11:22', '00001' , '2008-10-22 11:21' 
UNION ALL SELECT 2 , 1.4 , 2.1 , '11:23' , '00001' , '2008-10-22 11:22' 
UNION ALL SELECT 3 , 1.4 , 2.8 , '08:23' , '00001' , '2008-10-23 08:23' 
UNION ALL SELECT 4 , 1.4 , 2.1, '11:23' , '00001', '2008-10-23 11:22' 
GO --起始日期为2008-10-20 结束日期为2008-11-01 传入的RecorderID=00001 
DECLARE @st DATETIME,@ed DATETIME,@ID VARCHAR(5) 
SELECT @st='2008-10-20',@ed='2008-11-1',@id='0001' 
SELECT b.id,avgT,maxT,b.MaxTemperatureTime, dt FROM
(
SELECT AVG(Temperature) avgT,MAX(MaxTemperature) maxT,CONVERT(VARCHAR(10),nDate,120) dt
FROM tb WHERE nDate BETWEEN @st AND DATEADD(ms,-1,DATEADD(dd,1,@ed)) 
GROUP BY RecorderID,CONVERT(VARCHAR(10),nDate,120)
) a
INNER JOIN tb b
ON DATEDIFF(dd,dt,nDate)=0 AND maxT=MaxTemperature/*
2 1.3 2.1 11:23 2008-10-22
3 1.4 2.8 08:23 2008-10-23
*/

Answer 8

CREATE TABLE tb(ID INT, Temperature FLOAT, MaxTemperature FLOAT,MaxTemperatureTime VARCHAR(5),RecorderID VARCHAR(5), nDate DATETIME)
INSERT tb SELECT 1        ,  1.2,              1.5   ,               '11:22',                '00001'   ,   '2008-10-22 11:21'
UNION ALL SELECT 2       ,   1.4 ,             2.1  ,                '11:23' ,               '00001'  ,    '2008-10-22 11:22'
UNION ALL SELECT 3      ,    1.4  ,            2.8 ,                 '08:23'  ,              '00001' ,     '2008-10-23 08:23'
UNION ALL SELECT 4     ,     1.4   ,           2.1,                  '11:23'   ,             '00001',      '2008-10-23 11:22' 
GO
create proc sp_MonthReport_Calculate 
@st DATETIME,
@ed DATETIME,
@RecorderID VARCHAR(5)
as
SELECT Temperature=AVG(Temperature)
,MaxTemperature=MAX(MaxTemperature)
,MaxTemperatureTime=(select min(MaxTemperatureTime) from tb where MaxTemperature=max(a.MaxTemperature))
,RecorderID=@RecorderID
,nDate=CONVERT(VARCHAR(10),nDate,120) 
FROM tb a
WHERE nDate BETWEEN @st AND DATEADD(ms,-1,DATEADD(dd,1,@ed))
GROUP BY RecorderID,CONVERT(VARCHAR(10),nDate,120)
goexec sp_MonthReport_Calculate '2008-10-20','2008-11-1','0001'/*
-----------------------------------------------------------
1.3 2.1 11:23 0001 2008-10-22
1.4 2.8 08:23 0001 2008-10-23
*/

Answer 9

大哥，还是不行，如果MaxTemperature有许多相同的值，都会出现在检索列表中的，我的目的是一个最大值只要一个id avgT maxT MaxTemperatureTime dt
145637 49.0298818624044 99 2008-01-01 12:46:00.000 2008-01-01
145638 49.0298818624044 99 2008-01-01 12:47:00.000 2008-01-01
145639 49.0298818624044 99 2008-01-01 12:48:00.000 2008-01-01
145640 49.0298818624044 99 2008-01-01 12:49:00.000 2008-01-01
145641 49.0298818624044 99 2008-01-01 12:50:00.000 2008-01-01
145642 49.0298818624044 99 2008-01-01 12:51:00.000 2008-01-01
145753 49.0298818624044 99 2008-01-01 14:42:00.000 2008-01-01
145754 49.0298818624044 99 2008-01-01 14:43:00.000 2008-01-01
145755 49.0298818624044 99 2008-01-01 14:44:00.000 2008-01-01
145756 49.0298818624044 99 2008-01-01 14:45:00.000 2008-01-01
145757 49.0298818624044 99 2008-01-01 14:46:00.000 2008-01-01
145758 49.0298818624044 99 2008-01-01 14:47:00.000 2008-01-01
145852 49.0298818624044 99 2008-01-01 16:21:00.000 2008-01-01
145853 49.0298818624044 99 2008-01-01 16:22:00.000 2008-01-01
145854 49.0298818624044 99 2008-01-01 16:23:00.000 2008-01-01
145855 49.0298818624044 99 2008-01-01 16:24:00.000 2008-01-01
145856 49.0298818624044 99 2008-01-01 16:25:00.000 2008-01-01
145857 49.0298818624044 99 2008-01-01 16:26:00.000 2008-01-01
147534 49.6118055555556 99 2008-01-02 20:23:00.000 2008-01-02
147535 49.6118055555556 99 2008-01-02 20:24:00.000 2008-01-02
147536 49.6118055555556 99 2008-01-02 20:25:00.000 2008-01-02
147537 49.6118055555556 99 2008-01-02 20:26:00.000 2008-01-02
147577 49.6118055555556 99 2008-01-02 21:06:00.000 2008-01-02
147578 49.6118055555556 99 2008-01-02 21:07:00.000 2008-01-02
147579 49.6118055555556 99 2008-01-02 21:08:00.000 2008-01-02
147580 49.6118055555556 99 2008-01-02 21:09:00.000 2008-01-02
147581 49.6118055555556 99 2008-01-02 21:10:00.000 2008-01-02
147582 49.6118055555556 99 2008-01-02 21:11:00.000 2008-01-02
147681 49.6118055555556 99 2008-01-02 22:50:00.000 2008-01-02
147682 49.6118055555556 99 2008-01-02 22:51:00.000 2008-01-02
147683 49.6118055555556 99 2008-01-02 22:52:00.000 2008-01-02
147684 49.6118055555556 99 2008-01-02 22:53:00.000 2008-01-02
147685 49.6118055555556 99 2008-01-02 22:54:00.000 2008-01-02
147686 49.6118055555556 99 2008-01-02 22:55:00.000 2008-01-02
148840 49.50625 99 2008-01-03 18:09:00.000 2008-01-03
148841 49.50625 99 2008-01-03 18:10:00.000 2008-01-03
148842 49.50625 99 2008-01-03 18:11:00.000 2008-01-03
148843 49.50625 99 2008-01-03 18:12:00.000 2008-01-03
148901 49.50625 99 2008-01-03 19:10:00.000 2008-01-03
148902 49.50625 99 2008-01-03 19:11:00.000 2008-01-03
150184 49.6083333333333 99 2008-01-04 16:33:00.000 2008-01-04
150185 49.6083333333333 99 2008-01-04 16:34:00.000 2008-01-04
150186 49.6083333333333 99 2008-01-04 16:35:00.000 2008-01-04
150187 49.6083333333333 99 2008-01-04 16:36:00.000 2008-01-04
150246 49.6083333333333 99 2008-01-04 17:35:00.000 2008-01-04
150247 49.6083333333333 99 2008-01-04 17:36:00.000 2008-01-04
150551 49.6083333333333 99 2008-01-04 22:40:00.000 2008-01-04
150552 49.6083333333333 99 2008-01-04 22:41:00.000 2008-01-04
150553 49.6083333333333 99 2008-01-04 22:42:00.000 2008-01-04
150554 49.6083333333333 99 2008-01-04 22:43:00.000 2008-01-04
150575 49.6083333333333 99 2008-01-04 23:04:00.000 2008-01-04
151791 50.5388888888889 99 2008-01-05 19:20:00.000 2008-01-05
151792 50.5388888888889 99 2008-01-05 19:21:00.000 2008-01-05
151610 50.5388888888889 99 2008-01-05 16:19:00.000 2008-01-05
151611 50.5388888888889 99 2008-01-05 16:20:00.000 2008-01-05
151612 50.5388888888889 99 2008-01-05 16:21:00.000 2008-01-05
151613 50.5388888888889 99 2008-01-05 16:22:00.000 2008-01-05
151614 50.5388888888889 99 2008-01-05 16:23:00.000 2008-01-05
151700 50.5388888888889 99 2008-01-05 17:49:00.000 2008-01-05
151701 50.5388888888889 99 2008-01-05 17:50:00.000 2008-01-05
151702 50.5388888888889 99 2008-01-05 17:51:00.000 2008-01-05
151703 50.5388888888889 99 2008-01-05 17:52:00.000 2008-01-05
这样有好多值是相同的，我只要头一条的

Answer 10

Temperature MaxTemperature MaxTemperatureTime RecorderID nDate
59.7779803646562 22.4 2008-10-11 05:07:00.000 00001 2008-10-20
59.3546511627907 22.4 2008-10-11 05:07:00.000 00001 2008-10-20
60.4509554140126 22.4 2008-10-11 05:07:00.000 00001 2008-10-20
60.7994179278231 22.5 2008-10-11 03:49:00.000 00001 2008-10-20
62.0815850815855 22.5 2008-10-11 03:49:00.000 00001 2008-10-20
61.0435582822085 22.1 2008-10-12 05:14:00.000 00001 2008-10-21
63.2135135135135 17.6 2008-10-22 13:28:00.000 00001 2008-10-22
61.183437826541 22.1 2008-10-12 05:14:00.000 00001 2008-10-20
62.1843672456574 23.3 2008-10-10 17:28:00.000 00001 2008-10-20
60.213222079589 22.1 2008-10-12 05:14:00.000 00001 2008-10-20
57.996098829649 22.5 2008-10-11 03:49:00.000 00001 2008-10-20
57.9314093959732 22.5 2008-10-11 03:49:00.000 00001 2008-10-20
64.1118086696561 26.7 2008-10-15 13:29:00.000 00001 2008-10-21
66.6823899371068 21.8 2008-10-14 13:10:00.000 00001 2008-10-21
62.8934318555009 21.8 2008-10-14 13:10:00.000 00001 2008-10-21
61.9570599613153 21.5 2008-10-14 13:22:00.000 00001 2008-10-21
63.6758316361166 25.6 2008-10-11 12:17:00.000 00001 2008-10-21
62.6348790322579 21.5 2008-10-14 13:22:00.000 00001 2008-10-21
66.4450000000002 21.8 2008-10-14 13:10:00.000 00001 2008-10-21
59.9991304347826 21.5 2008-10-14 13:22:00.000 00001 2008-10-22
56.8918918918919 21.8 2008-10-14 13:10:00.000 00001 2008-10-22
56.9142857142857 22.1 2008-10-12 05:14:00.000 00001 2008-10-22
59.7619047619048 22 2008-01-01 22:09:00.000 00001 2008-10-20
59.0850858369097 32.1 2008-10-11 08:55:00.000 00001 2008-10-20
60.5373819163293 22.2 2008-10-12 03:42:00.000 00001 2008-10-20
59.8483739837397 22.2 2008-10-12 03:42:00.000 00001 2008-10-20
62.4896761133605 21.2 2008-10-14 11:47:00.000 00001 2008-10-21
61.6886051080551 21.4 2008-10-14 12:27:00.000 00001 2008-10-21
61.2330078124999 21.3 2008-10-14 12:03:00.000 00001 2008-10-21
62.2351888667993 21.3 2008-10-14 12:03:00.000 00001 2008-10-21
59.6476099426386 22 2008-01-01 22:09:00.000 00001 2008-10-21
59.5258426966293 22 2008-01-01 22:09:00.000 00001 2008-10-21
59.0859649122807 21.1 2008-10-14 11:40:00.000 00001 2008-10-22
60.2956140350877 21.7 2008-10-14 14:07:00.000 00001 2008-10-22
58.5456896551724 21.7 2008-10-14 14:07:00.000 00001 2008-10-22
60.5684684684684 20.7 2008-10-10 16:13:00.000 00001 2008-10-22
56.5721739130435 25.5 2008-10-12 10:14:00.000 00001 2008-10-22
55.3792792792793 28.1 2008-10-15 12:42:00.000 00001 2008-10-22
62.68144 21.2 2008-10-14 11:47:00.000 00001 2008-10-22
60.3928571428571 21.3 2008-10-14 12:03:00.000 00001 2008-10-22
58.4830357142857 21.7 2008-10-14 14:07:00.000 00001 2008-10-22
59.0882882882883 21.1 2008-10-14 11:40:00.000 00001 2008-10-22
58.9303571428571 21.4 2008-10-14 12:27:00.000 00001 2008-10-22
59.20098855359 22 2008-01-01 22:09:00.000 00001 2008-10-20
59.5220379146919 23 2008-01-01 17:42:00.000 00001 2008-10-20
61.6489534883717 22 2008-01-01 22:09:00.000 00001 2008-10-20
62.4301162790699 21.6 2008-10-14 13:06:00.000 00001 2008-10-20
59.4637333333333 22.6 2008-10-11 01:56:00.000 00001 2008-10-20
62.8321649484536 21.1 2008-10-14 11:40:00.000 00001 2008-10-21
64.0666131621187 21.6 2008-10-14 13:06:00.000 00001 2008-10-21
63.0109689213893 21.3 2008-10-14 12:03:00.000 00001 2008-10-21
61.653787878788 21.7 2008-10-14 14:07:00.000 00001 2008-10-21
62.1886827458254 21.6 2008-10-14 13:06:00.000 00001 2008-10-21
58.0781818181818 21.6 2008-10-14 13:06:00.000 00001 2008-10-22
62.4530612244898 21.6 2008-10-14 13:06:00.000 00001 2008-10-22
61.2122807017544 21.6 2008-10-14 13:06:00.000 00001 2008-10-22
59.1892857142857 21.2 2008-10-14 11:47:00.000 00001 2008-10-22也有非常多的重复值，能否按nDate排下序，而且应该生成的记录应该在30条左右，也就是一个月的，
2月28,29
3月31
4月30就是月多少天就生成多少条记录，没有的仅插入日期部分？

Answer 11

DECLARE @st DATETIME,@ed DATETIME,@ID VARCHAR(5) 
SELECT @st='2008-10-20',@ed='2008-11-1',@id='0001' 
SELECT b.id,avgT,maxT,b.MaxTemperatureTime, dt FROM
(
SELECT AVG(Temperature) avgT,MAX(MaxTemperature) maxT,CONVERT(VARCHAR(10),nDate,120) dt
    FROM tb WHERE nDate BETWEEN @st AND DATEADD(ms,-1,DATEADD(dd,1,@ed)) 
    GROUP BY RecorderID,CONVERT(VARCHAR(10),nDate,120)
) a
INNER JOIN tb b
    ON DATEDIFF(dd,dt,nDate)=0 AND maxT=MaxTemperature
WHERE NOT EXISTS(
SELECT 1 FROM tb 
WHERE MaxTemperature = maxT AND DATEDIFF(dd,dt,nDate)=0
AND id<b.id)

Answer 12

try:CREATE TABLE tb(ID INT, Temperature FLOAT, MaxTemperature FLOAT,MaxTemperatureTime VARCHAR(5),RecorderID VARCHAR(5), nDate DATETIME)
INSERT tb SELECT 1        ,  1.2,              1.5   ,               '11:22',                '00001'   ,   '2008-10-22 11:21'
UNION ALL SELECT 2       ,   1.4 ,             2.1  ,                '11:23' ,               '00001'  ,    '2008-10-22 11:22'
UNION ALL SELECT 3      ,    1.4  ,            2.8 ,                 '08:23'  ,              '00001' ,     '2008-10-23 08:23'
UNION ALL SELECT 4     ,     1.4   ,           2.1,                  '11:23'   ,             '00001',      '2008-10-23 11:22' 
GO
if object_id('sp_MonthReport_Calculate','u') is not null
drop proc sp_MonthReport_Calculate
go
create proc sp_MonthReport_Calculate 
@st DATETIME,
@ed DATETIME,
@RecorderID VARCHAR(5)
as
SELECT Temperature=AVG(Temperature)
,MaxTemperature=MAX(MaxTemperature)
,MaxTemperatureTime=(select min(MaxTemperatureTime) from tb where RecorderID=@RecorderID and CONVERT(VARCHAR(10),nDate,120)=CONVERT(VARCHAR(10),a.nDate,120) and MaxTemperature=max(a.MaxTemperature))
,RecorderID=@RecorderID,
nDate=CONVERT(VARCHAR(10),nDate,120) 
FROM tb a
WHERE RecorderID=@RecorderID and nDate BETWEEN @st AND DATEADD(ms,-1,DATEADD(dd,1,@ed))
GROUP BY CONVERT(VARCHAR(10),nDate,120)
goexec sp_MonthReport_Calculate '2008-10-20','2008-11-1','00001'/*
-----------------------------------------------------------
1.3 2.1 11:23 00001 2008-10-22
1.4 2.8 08:23 00001 2008-10-23
*/drop table tb

Answer 13

To szx1999 兄弟给出的Temperature MaxTemperature MaxTemperatureTime RecorderID nDate
50.2770833333333 99 2008-01-20 01:20:00.000 00001 2008-01-20
49.5354166666667 99 2008-01-21 08:05:00.000 00001 2008-01-21
49.7291666666667 99 2008-01-22 04:19:00.000 00001 2008-01-22
49.1576388888889 99 2008-01-23 05:48:00.000 00001 2008-01-23
49.9263888888889 99 2008-01-24 01:32:00.000 00001 2008-01-24
49.1430555555556 99 2008-01-25 02:40:00.000 00001 2008-01-25
49.325 99 2008-01-26 05:55:00.000 00001 2008-01-26
50.1548611111111 99 2008-01-27 04:42:00.000 00001 2008-01-27
49.3916666666667 99 2008-01-28 05:46:00.000 00001 2008-01-28
49.6958333333333 99 2008-01-29 03:12:00.000 00001 2008-01-29
48.5229166666667 99 2008-01-30 04:17:00.000 00001 2008-01-30
48.5486111111111 99 2008-01-31 01:54:00.000 00001 2008-01-31
49.6118055555556 99 2008-02-01 02:26:00.000 00001 2008-02-01
56 56 2008-02-02 00:00:00.000 00001 2008-02-02只能查出1-20号后的数据，而且还有2月份的，前面的数据没了
To fcuandy 
兄弟给出的也有2月份的数据，而且只运行exec似乎死了，cpu占满了，半天也没响应过了，再费心帮我看看吧

Answer 14

这个是我根据fcuandy 大哥给的代码改的我实际的代码
create proc sp_MonthReport_Calculate 
@st DATETIME,
@ed DATETIME,
@RecorderID VARCHAR(5)
asSELECT b.id,avgT,maxT,b.MaxTemperatureTime, dt FROM
(
SELECT AVG(EnvirTemp) avgT,MAX(MaxTemperature) maxT,CONVERT(VARCHAR(10),nDate,120) dt
    FROM MeteTable WHERE nDate BETWEEN @st AND DATEADD(ms,-1,DATEADD(dd,1,@ed)) 
    GROUP BY RecorderID,CONVERT(VARCHAR(10),nDate,120)
) a
INNER JOIN MeteTable b
    ON DATEDIFF(dd,dt,nDate)=0 AND maxT=MaxTemperature
    WHERE NOT EXISTS(
        SELECT 1 FROM MeteTable 
        WHERE MaxTemperature = maxT AND DATEDIFF(dd,dt,nDate)=0
                        AND id<b.id) order by nDate
                        
go
exec sp_MonthReport_Calculate '2008-10-01','2008-11-01','08316'

Answer 15

这种语句效率是很差，相关子查询。 可以改聚合再内连接试试。
IF OBJECT_ID('tb','u') IS NOT NULL
  DROP TABLE tb 
GO 
CREATE TABLE tb(ID INT, Temperature FLOAT, MaxTemperature FLOAT,MaxTemperatureTime VARCHAR(5),RecorderID VARCHAR(5), nDate DATETIME) 
INSERT tb SELECT 1 , 1.2, 1.5 , '11:22', '00001' , '2008-10-22 11:21' 
UNION ALL SELECT 2 , 1.4 , 2.1 , '11:23' , '00001' , '2008-10-22 11:22' 
UNION ALL SELECT 3 , 1.4 , 2.8 , '08:23' , '00001' , '2008-10-23 08:23' 
UNION ALL SELECT 4 , 1.4 , 2.8 , '09:23' , '00001' , '2008-10-23 08:23' 
UNION ALL SELECT 5 , 1.4 , 2.1, '11:23' , '00001', '2008-10-23 11:22' 
GO --起始日期为2008-10-20 结束日期为2008-11-01 传入的RecorderID=00001 
DECLARE @st DATETIME,@ed DATETIME,@ID VARCHAR(5) 
SELECT @st='2008-10-20',@ed='2008-11-1',@id='0001' SELECT mb,avgT,maxT,a.MaxTemperatureTime,dt FROM tb a
INNER JOIN
(
SELECT MIN(b.id) mb,avgT,maxT,dt FROM
(
SELECT AVG(Temperature) avgT,MAX(MaxTemperature) maxT,CONVERT(VARCHAR(10),nDate,120) dt
    FROM tb WHERE nDate BETWEEN @st AND DATEADD(ms,-1,DATEADD(dd,1,@ed)) 
    GROUP BY RecorderID,CONVERT(VARCHAR(10),nDate,120)
) a
INNER JOIN tb b
    ON DATEDIFF(dd,dt,nDate)=0 AND maxT=MaxTemperature
GROUP BY avgT,maxT, dt
) b
ON mb=id这样也好不了多少，你看看吧。

Answer 16

if object_id('sp_MonthReport_Calculate','u') is not null
    drop proc sp_MonthReport_Calculate
gocreate proc sp_MonthReport_Calculate 
@st DATETIME,
@ed DATETIME,
@RecorderID VARCHAR(5)
as
SELECT Temperature=AVG(EnvirTemp)
    ,MaxTemperature=MAX(MaxTemperature)
    
    ,MaxTemperatureTime=
    (select min(MaxTemperatureTime) from MeteTable 
    where RecorderID=@RecorderID and CONVERT(VARCHAR(10),nDate,120)=CONVERT(VARCHAR(10),a.nDate,120) 
    and MaxTemperature=max(a.MaxTemperature))
    
    ,RecorderID=@RecorderID,
    nDate=CONVERT(VARCHAR(10),nDate,120) 
FROM MeteTable a
WHERE RecorderID=@RecorderID and nDate BETWEEN @st AND DATEADD(ms,-1,DATEADD(dd,1,@ed))
GROUP BY CONVERT(VARCHAR(10),nDate,120) order by nDate
goexec sp_MonthReport_Calculate '2008-10-01 20:00','2008-11-1 20:00','08267'
这个答案已经很接近了，但还有一个问题，如果求的月报的日期是从前一天的20点到后一天的20点才算一天，这个要怎么改呢

Answer 17

终极版：CREATE TABLE tb(ID INT, Temperature FLOAT, MaxTemperature FLOAT,MaxTemperatureTime VARCHAR(5),RecorderID VARCHAR(5), nDate DATETIME)
INSERT tb SELECT 1        ,  1.2,              1.5   ,               '11:22',                '00001'   ,   '2008-10-22 11:21'
UNION ALL SELECT 2       ,   1.4 ,             2.1  ,                '11:23' ,               '00001'  ,    '2008-10-22 11:22'
UNION ALL SELECT 3      ,    1.4  ,            2.8 ,                 '08:23'  ,              '00001' ,     '2008-10-23 08:23'
UNION ALL SELECT 4     ,     1.4   ,           2.1,                  '11:23'   ,             '00001',      '2008-10-23 11:22' 
GO
if object_id('sp_MonthReport_Calculate','u') is not null
drop proc sp_MonthReport_Calculate
go
create proc sp_MonthReport_Calculate 
@st DATETIME,
@ed DATETIME,
@RecorderID VARCHAR(5)
as
select a.Temperature,a.MaxTemperature,MaxTemperatureTime,RecorderID,b.nDate from
(
SELECT Temperature=AVG(Temperature)
,MaxTemperature=MAX(MaxTemperature)
,MaxTemperatureTime=(select min(MaxTemperatureTime) from tb where RecorderID=@RecorderID and CONVERT(VARCHAR(10),nDate,120)=CONVERT(VARCHAR(10),t.nDate,120) and MaxTemperature=max(t.MaxTemperature))
,RecorderID=@RecorderID
,nDate=CONVERT(VARCHAR(10),nDate,120) 
FROM tb t
WHERE RecorderID=@RecorderID and nDate BETWEEN @st AND @ed
GROUP BY CONVERT(VARCHAR(10),nDate,120)
) a
right join
(select top(day(dateadd(d,-1,convert(varchar(8),dateadd(m,1,@st),120)+'1'))) nDate=convert(varchar(10),dateadd(d,row_number() over(order by id),dateadd(d,-1,convert(varchar(8),@st,120)+'1')),120) from sysobjects) b
on a.nDate=b.nDate
order by b.nDate
goexec sp_MonthReport_Calculate '2008-10-20','2008-11-1','00001'/*
Temperature            MaxTemperature         MaxTemperatureTime RecorderID nDate
---------------------- ---------------------- ------------------ ---------- ----------
NULL                   NULL                   NULL               NULL       2008-10-01
NULL                   NULL                   NULL               NULL       2008-10-02
NULL                   NULL                   NULL               NULL       2008-10-03
NULL                   NULL                   NULL               NULL       2008-10-04
NULL                   NULL                   NULL               NULL       2008-10-05
NULL                   NULL                   NULL               NULL       2008-10-06
NULL                   NULL                   NULL               NULL       2008-10-07
NULL                   NULL                   NULL               NULL       2008-10-08
NULL                   NULL                   NULL               NULL       2008-10-09
NULL                   NULL                   NULL               NULL       2008-10-10
NULL                   NULL                   NULL               NULL       2008-10-11
NULL                   NULL                   NULL               NULL       2008-10-12
NULL                   NULL                   NULL               NULL       2008-10-13
NULL                   NULL                   NULL               NULL       2008-10-14
NULL                   NULL                   NULL               NULL       2008-10-15
NULL                   NULL                   NULL               NULL       2008-10-16
NULL                   NULL                   NULL               NULL       2008-10-17
NULL                   NULL                   NULL               NULL       2008-10-18
NULL                   NULL                   NULL               NULL       2008-10-19
NULL                   NULL                   NULL               NULL       2008-10-20
NULL                   NULL                   NULL               NULL       2008-10-21
1.3                    2.1                    11:23              00001      2008-10-22
1.4                    2.8                    08:23              00001      2008-10-23
NULL                   NULL                   NULL               NULL       2008-10-24
NULL                   NULL                   NULL               NULL       2008-10-25
NULL                   NULL                   NULL               NULL       2008-10-26
NULL                   NULL                   NULL               NULL       2008-10-27
NULL                   NULL                   NULL               NULL       2008-10-28
NULL                   NULL                   NULL               NULL       2008-10-29
NULL                   NULL                   NULL               NULL       2008-10-30
NULL                   NULL                   NULL               NULL       2008-10-31(31 row(s) affected)
*/