求一SQL，望指点啊解决方案

with tb as(    --把数据放到一张表里    select 1 as ID,1 as MID,13.51 as [Value],'2012-05-01' as [date],'A' as [SIGN] union all    select 2,1,23.16,'2012-05-01','B' union all      select 3,1,15.23,'2012-05-01','C' union all      select 4,1,24.81,'2012-05-02','A' union all      select 5,1,67.99,'2012-05-02','B' union all      select 6,1,81.91,'2012-05-02','C' union all      select 7,1,24.81,'2012-05-03','A' union all     select 8,1,67.99,'2012-05-03','B' union all     select 9,1,81.91,'2012-05-03','C'),tb2 as(    --得到最大值，最小值，平均值    select         [SIGN]        ,        AVG(Value) as v_AvgValue,        MAX(Value) as v_MaxValue,        MIN(Value) as v_MinValue    from tb     where MID='1' and [date]='2012-05-01'    --按月  where year([date])=year('时间') and month([date])=month('时间')    group by [SIGN])--下一步，根据找到的最大值、最小值关联时间，由于最大值value出现重复，我们取最大时间select     tb2.[SIGN],    tb2.v_AvgValue,    tb2.v_MaxValue,    max(a.date) as max_date,    tb2.v_MinValue,    max(b.date) as min_datefrom tb2left join tb a on tb2.v_MaxValue=a.Valueleft join tb b on tb2.v_MinValue=b.Valuegroup by tb2.[SIGN],    tb2.v_AvgValue,    tb2.v_MaxValue,    tb2.v_MinValue/*SIGN v_AvgValue                              v_MaxValue                              max_date   v_MinValue                              min_date---- --------------------------------------- --------------------------------------- ---------- --------------------------------------- ----------A    13.510000                               13.51                                   2012-05-01 13.51                                   2012-05-01B    23.160000                               23.16                                   2012-05-01 23.16                                   2012-05-01C    15.230000                               15.23                                   2012-05-01 15.23                                   2012-05-01(3 row(s) affected)*/
------解决方案--------------------
SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([ID] int,[MID] int,[Value] numeric(4,2),[Date] datetime,[SIGN] varchar(1))insert [test]select 1,1,23.51,'2012/05/01','A' union allselect 2,1,12.16,'2012/05/02','B' union allselect 3,1,23.23,'2012/05/03','C' union allselect 4,1,34.81,'2012/05/04','A' union allselect 5,1,12.99,'2012/05/05','B' union allselect 6,1,65.91,'2012/05/06','C' union allselect 7,1,32.81,'2012/05/07','A' union allselect 8,1,98.99,'2012/05/08','B' union allselect 9,1,64.91,'2012/05/09','C' union allselect 10,1,24.51,'2012/05/10','A' union allselect 11,1,23.16,'2012/05/11','B' union allselect 12,1,44.23,'2012/05/12','C' union allselect 13,1,33.81,'2012/05/13','A' union allselect 14,1,98.99,'2012/05/14','B' union allselect 15,1,81.91,'2012/05/15','C' union allselect 16,1,56.81,'2012/05/16','A' union allselect 17,1,67.99,'2012/05/17','B' union allselect 18,1,81.91,'2012/05/18','C' union allselect 19,1,24.81,'2012/06/01','A' union allselect 20,1,67.99,'2012/06/19','B' union allselect 21,1,81.91,'2012/06/20','C' union allselect 22,2,13.51,'2012/05/21','A' union allselect 23,2,23.16,'2012/05/22','B' union allselect 24,2,15.23,'2012/05/23','C';with tas(selectpx=row_number()over(partition by [SIGN] order by [Value] desc),* from testwhere MID=1 and MONTH([Date])=5),m as(selectpx=row_number()over(partition by [SIGN] order by [Value] asc),* from testwhere MID=1 and MONTH([Date])=5),nas(select [SIGN],AVG(value) as value from testwhere MID=1 and MONTH([Date])=5group by [SIGN])select     t.[SIGN],n.value,t.Value,t.[Date],m.Value,m.[Date]from tinner join m on t.px=m.px and t.[SIGN]=m.[SIGN]inner join n on t.[SIGN]=n.[SIGN]where t.px=1/*SIGN    avgvalue    maxValue    maxDate    minValue    minDate-----------------------------------------------A    34.376666    56.81    2012-05-16 00:00:00.000    23.51    2012-05-01 00:00:00.000B    52.380000    98.99    2012-05-08 00:00:00.000    12.16    2012-05-02 00:00:00.000C    60.350000    81.91    2012-05-18 00:00:00.000    23.23    2012-05-03 00:00:00.000*/