求一SQL，望指点啊

Question

表结构如下t_TempID[自增]     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 日期等于“某一月”又该如何计算？求教

Answer 1

查询结果格式如下
SIGN Value[平均值] Value[最大值] Value[最大值所在的时间点] Value[最小值] Value[最小值所在的时间点]
 A ... ... date ... date
 B ... ... date ... date
 C ... ... date ... date

Answer 2

--> 测试数据：[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,13.51,'2012/05/01','A' 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' union all
select 17,1,24.81,'2012/06/01','A' union all
select 18,1,67.99,'2012/06/01','B' union all
select 19,1,81.91,'2012/06/01','C' union all
select 20,2,13.51,'2012/05/01','A' union all
select 21,2,23.16,'2012/05/01','B' union all
select 22,2,15.23,'2012/05/01','C'
;with t 
as(
select * from test where [MID]=1 and [Date]='2012/06/01'
)
select [SIGN],AVG([Value]) as [Value平均值],
    (select MAX([Value]) from t b where a.[SIGN]=b.[SIGN]) as [Value最大值],
    (select [Date] from t c where c.[SIGN]= a.[SIGN] and c.Value=(
    select MAX(Value) from t d where d.[SIGN]=c.[SIGN])) as [Value最大值所在的时间点],
    (select min([Value]) from t b where a.[SIGN]=b.[SIGN]) as [Value最小值],
    (select [Date] from t c where c.[SIGN]= a.[SIGN] and c.Value=(
    select min(Value) from t d where d.[SIGN]=c.[SIGN])) as [Value最小值所在的时间点]
from t a
group by 
a.[SIGN]/*
SIGN Value平均值 Value最大值 Value最大值所在的时间点 Value最小值 Value最小值所在的时间点
----------------------------------------------------------------------------------------------------
A 24.810000 24.81 2012-06-01 00:00:00.000 24.81 2012-06-01 00:00:00.000
B 67.990000 67.99 2012-06-01 00:00:00.000 67.99 2012-06-01 00:00:00.000
C 81.910000 81.91 2012-06-01 00:00:00.000 81.91 2012-06-01 00:00:00.000
*/--条件是等于某一月的话就用month（[date]）=你指定的月份

Answer 3

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_date
from tb2
left join tb a on tb2.v_MaxValue=a.Value
left join tb b on tb2.v_MinValue=b.Value
group 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-01
B    23.160000                               23.16                                   2012-05-01 23.16                                   2012-05-01
C    15.230000                               15.23                                   2012-05-01 15.23                                   2012-05-01(3 row(s) affected)
*/

Answer 4

select '1' as id ,'1' as mid,'13.51' as value ,'2012/05/01' as date,'A' as sign into #tmp  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' union all 
select '17','1','24.81','2012/06/01','A'  union all 
select '18','1','67.99','2012/06/01','B'  union all 
select '19','1','81.91','2012/06/01','C'   union all 
select '20','2','13.51','2012/05/01','A'  union all 
select '21','2','23.16','2012/05/01','B' union all 
select '22','2','15.23','2012/05/01','C'  --select * from #tmp select a.sign,sum(cast(a.value as float))/count(*),max(a.value),
(select max(b.date) from #tmp b where b.sign = a.sign ) ,
min(a.value),
(select min(b.date) from #tmp b where b.sign = a.sign ) 
from #tmp a
group by a.sign 
drop table #tmp 

Answer 5

--> 测试数据：[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 all
select 2,1,12.16,'2012/05/02','B' union all
select 3,1,23.23,'2012/05/03','C' union all
select 4,1,34.81,'2012/05/04','A' union all
select 5,1,12.99,'2012/05/05','B' union all
select 6,1,65.91,'2012/05/06','C' union all
select 7,1,32.81,'2012/05/07','A' union all
select 8,1,98.99,'2012/05/08','B' union all
select 9,1,64.91,'2012/05/09','C' union all
select 10,1,24.51,'2012/05/10','A' union all
select 11,1,23.16,'2012/05/11','B' union all
select 12,1,44.23,'2012/05/12','C' union all
select 13,1,33.81,'2012/05/13','A' union all
select 14,1,98.99,'2012/05/14','B' union all
select 15,1,81.91,'2012/05/15','C' union all
select 16,1,56.81,'2012/05/16','A' union all
select 17,1,67.99,'2012/05/17','B' union all
select 18,1,81.91,'2012/05/18','C' union all
select 19,1,24.81,'2012/06/01','A' union all
select 20,1,67.99,'2012/06/19','B' union all
select 21,1,81.91,'2012/06/20','C' union all
select 22,2,13.51,'2012/05/21','A' union all
select 23,2,23.16,'2012/05/22','B' union all
select 24,2,15.23,'2012/05/23','C'
;with t
as(
select
px=row_number()over(partition by [SIGN] order by [Value] desc),
* from test
where MID=1 and MONTH([Date])=5
),
m as(
select
px=row_number()over(partition by [SIGN] order by [Value] asc),
* from test
where MID=1 and MONTH([Date])=5
),n
as(
select [SIGN],AVG(value) as value from test
where MID=1 and MONTH([Date])=5
group by [SIGN]
)
select 
    t.[SIGN],n.value,t.Value,t.[Date],m.Value,m.[Date]
from t
inner 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.000
B 52.380000 98.99 2012-05-08 00:00:00.000 12.16 2012-05-02 00:00:00.000
C 60.350000 81.91 2012-05-18 00:00:00.000 23.23 2012-05-03 00:00:00.000
*/

Answer 6

原来有id为何要row_number？直接not exists或者cross apply

Answer 7

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 all
select 2,1,23.16,'2012/05/01 10:00','B' union all
select 3,1,15.23,'2012/05/01 12:00','C' union all
select 4,1,24.81,'2012/05/02','A' union all
select 5,1,67.99,'2012/05/02 8:00','B' union all
select 6,1,81.91,'2012/05/02 9:00','C' union all
select 7,1,24.81,'2012/05/03','A' union all
select 8,1,67.99,'2012/05/03 7:00','B' union all
select 9,1,81.91,'2012/05/03 8:00','C' union all
select 17,1,24.81,'2012/06/01','A' union all
select 18,1,67.99,'2012/06/01 8:00','B' union all
select 19,1,81.91,'2012/06/01 9:00','C' union all
select 20,2,13.51,'2012/05/01 13:00','A' union all
select 21,2,23.16,'2012/05/01 8:00','B' union all
select 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_MinValue
from 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.date
from cte a
cross apply (
select top 1 [date]
from test
where [Date] >= '2012-05-01'
and [Date] < '2012-05-02'
and [SIGN] = a.[SIGN]
order by [Value] asc
) as minv
cross apply (
select top 1 [date]
from test
where [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.000
B 23.160000 23.16 2012-05-01 08:00:00.000 23.16 2012-05-01 08:00:00.000
C 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_MinValue
from 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.date
from cte a
cross apply (
select top 1 [date]
from test
where [Date] >= '2012-05-01'
and [Date] < '2012-06-01'
and [SIGN] = a.[SIGN]
order by [Value] asc
) as minv
cross apply (
select top 1 [date]
from test
where [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.000
B 45.575000 67.99 2012-05-02 08:00:00.000 23.16 2012-05-01 10:00:00.000
C 48.570000 81.91 2012-05-02 09:00:00.000 15.23 2012-05-01 12:00:00.000

Answer 8

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tbb')
BEGIN
    DROP TABLE tbb
END
GO
CREATE TABLE tbb
(
    ID INT IDENTITY(1,1),
    MID INT,
    Value DECIMAL(10,2),
    Date DATETIME,
    SIGN VARCHAR(10)
)
GO
INSERT INTO tbb
SELECT 1, 13.51, '2012/05/01', 'A' UNION 
SELECT 1, 23.16, '2012/05/01', 'B' UNION 
SELECT 1, 15.23, '2012/05/01', 'C' UNION   
SELECT 1, 24.81, '2012/05/02', 'A' UNION   
SELECT 1, 67.99, '2012/05/02', 'B' UNION   
SELECT 1, 81.91, '2012/05/02', 'C' UNION   
SELECT 1, 24.81, '2012/05/03', 'A' UNION   
SELECT 1, 67.99, '2012/05/03', 'B' UNION   
SELECT 1, 81.91, '2012/05/03', 'C' UNION 
SELECT 1, 24.81, '2012/06/01', 'A' UNION   
SELECT 1, 67.99, '2012/06/01', 'B' UNION   
SELECT 1, 81.91, '2012/06/01', 'C' UNION    
SELECT 2, 13.51, '2012/05/01', 'A' UNION  
SELECT 2, 23.16, '2012/05/01', 'B' UNION 
SELECT 2, 15.23, '2012/05/01', 'C'GO--要求根据MID查询出“MID = 1”AND 日期等于“某一天”的数据
SELECT SIGN,(MIN(value) + MAX(Value)) / 2 AS avg_Value,MIN(Value) AS min,DATE,MAX(Value) AS max,DATE
FROM tbb
WHERE MID = 1 AND Date = '2012/05/01'
GROUP BY SIGN,Date;SIGN    avg_Value    min    DATE    max    DATE
A    13.510000    13.51    2012-05-01 00:00:00.000    13.51    2012-05-01 00:00:00.000
B    23.160000    23.16    2012-05-01 00:00:00.000    23.16    2012-05-01 00:00:00.000
C    15.230000    15.23    2012-05-01 00:00:00.000    15.23    2012-05-01 00:00:00.000--另外如果上述的查询结果条件是“MID = 1”AND 日期等于“某一月”又该如何计算
WITH a AS
(SELECT SIGN,(MIN(value) + MAX(Value)) / 2 AS avg_Value,MIN(Value) AS min,MAX(Value) AS max
FROM tbb
WHERE MID = 1 AND CONVERT(VARCHAR(7),Date,120) = '2012-05'
GROUP BY SIGN,CONVERT(VARCHAR(7),Date,120))SELECT SIGN,avg_Value,MIN,(SELECT MAX(DATE) FROM tbb WHERE MID = 1 AND CONVERT(VARCHAR(7),Date,120) = '2012-05' AND Value = min AND SIGN = A.SIGN) AS mindate,MAX,(SELECT MAX(DATE) FROM tbb WHERE MID = 1 AND CONVERT(VARCHAR(7),Date,120) = '2012-05' AND Value = max and SIGN = a.SIGN) AS maxdate
FROM aSIGN    avg_Value    MIN    mindate    MAX    maxdate
A    19.160000    13.51    2012-05-01 00:00:00.000    24.81    2012-05-03 00:00:00.000
B    45.575000    23.16    2012-05-01 00:00:00.000    67.99    2012-05-03 00:00:00.000
C    48.570000    15.23    2012-05-01 00:00:00.000    81.91    2012-05-03 00:00:00.000