求一SQL，望指点啊
表结构如下t_Temp
ID[自增] MID Value Date[datetime类型] SIGN
1 1 13.51 2012/05/01 A
2 1 23.16 2012/05/01 B
3 1 15.23 2012/05/01 C
4 1 24.81 2012/05/02 A
5 1 67.99 2012/05/02 B
6 1 81.91 2012/05/02 C
7 1 24.81 2012/05/03 A
8 1 67.99 2012/05/03 B
9 1 81.91 2012/05/03 C
... ... .... .... .
17 1 24.81 2012/06/01 A
18 1 67.99 2012/06/01 B
19 1 81.91 2012/06/01 C
20 2 13.51 2012/05/01 A
21 2 23.16 2012/05/01 B
22 2 15.23 2012/05/01 C
要求根据MID查询出“MID = 1”AND 日期等于“某一天”的数据，
查询结果格式如下
SIGN Value[平均值] Value[最大值] Value[最大值所在的时间点] Value[最小值] Value[最小值所在的时间点]
A ... ... date ... date
B ... ... date ... date
C ... ... date ... date
另外如果上述的查询结果条件是“MID = 1”AND 日期等于“某一月”又该如何计算？
求教
[解决办法]
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)*/
[解决办法]
--> 测试数据：[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*/
[解决办法]
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,13.51,'2012/05/01','A' union allselect 2,1,23.16,'2012/05/01 10:00','B' union allselect 3,1,15.23,'2012/05/01 12:00','C' union allselect 4,1,24.81,'2012/05/02','A' union allselect 5,1,67.99,'2012/05/02 8:00','B' union allselect 6,1,81.91,'2012/05/02 9:00','C' union allselect 7,1,24.81,'2012/05/03','A' union allselect 8,1,67.99,'2012/05/03 7:00','B' union allselect 9,1,81.91,'2012/05/03 8:00','C' union allselect 17,1,24.81,'2012/06/01','A' union allselect 18,1,67.99,'2012/06/01 8:00','B' union allselect 19,1,81.91,'2012/06/01 9:00','C' union allselect 20,2,13.51,'2012/05/01 13:00','A' union allselect 21,2,23.16,'2012/05/01 8:00','B' union allselect 22,2,15.23,'2012/05/01 9:00','C'--一天;with cte as (select [SIGN],AVG(Value) as v_AvgValue,MAX(Value) as v_MaxValue,MIN(Value) as v_MinValuefrom test where [Date] >= '2012-05-01'and [Date] < '2012-05-02'group by [SIGN])select a.[SIGN], a.v_AvgValue, a.v_MaxValue, maxv.date, a.v_MinValue, minv.datefrom cte across apply (select top 1 [date]from testwhere [Date] >= '2012-05-01'and [Date] < '2012-05-02'and [SIGN] = a.[SIGN]order by [Value] asc) as minvcross apply (select top 1 [date]from testwhere [Date] >= '2012-05-01'and [Date] < '2012-05-02'and [SIGN] = a.[SIGN]order by [Value] desc) as maxv--结果A 13.510000 13.51 2012-05-01 13:00:00.000 13.51 2012-05-01 13:00:00.000B 23.160000 23.16 2012-05-01 08:00:00.000 23.16 2012-05-01 08:00:00.000C 15.230000 15.23 2012-05-01 09:00:00.000 15.23 2012-05-01 09:00:00.000--一月;with cte as (select [SIGN],AVG(Value) as v_AvgValue,MAX(Value) as v_MaxValue,MIN(Value) as v_MinValuefrom test where [Date] >= '2012-05-01'and [Date] < '2012-06-01'group by [SIGN])select a.[SIGN], a.v_AvgValue, a.v_MaxValue, maxv.date, a.v_MinValue, minv.datefrom cte across apply (select top 1 [date]from testwhere [Date] >= '2012-05-01'and [Date] < '2012-06-01'and [SIGN] = a.[SIGN]order by [Value] asc) as minvcross apply (select top 1 [date]from testwhere [Date] >= '2012-05-01'and [Date] < '2012-06-01'and [SIGN] = a.[SIGN]order by [Value] desc) as maxv--结果A 19.160000 24.81 2012-05-02 00:00:00.000 13.51 2012-05-01 00:00:00.000B 45.575000 67.99 2012-05-02 08:00:00.000 23.16 2012-05-01 10:00:00.000C 48.570000 81.91 2012-05-02 09:00:00.000 15.23 2012-05-01 12:00:00.000