表结构如下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 日期等于“某一月”又该如何计算?求教
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 日期等于“某一月”又该如何计算?求教
--> 测试数据:[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
*/
--这是个最大Value出现重复的特殊案例...
--where条件没有指定日期为哪一天
--你的另外一个帖子已经发了一个标准的..
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'
--按月 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 21.043333 24.81 2012-05-03 13.51 2012-05-01
B 53.046666 67.99 2012-05-03 23.16 2012-05-01
C 59.683333 81.91 2012-05-03 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 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
*/--这样效率高点
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
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