MaxValue MaxValueDateTime MinValue MinValueDateTime AverageValue
9.885 2008-9-11 1:51:58 3.210 2008-9-11 1:51:59 3.3329
10.254 2008-9-11 1:52:01 1.006 2008-9-11 1:52:33 5.666
12.225 2008-9-11 1:53:45 2.588 2008-9-11 1:53:58 6.889我想要的结果是:12.225 2008-9-11 1:53:45 1.006 2008-9-11 1:52:33 5.29 就是取最大值字段中的最大值
取最小值字段中的最小值及他们的对应时间,取平均值中的平均值
如何用一条SQL语句从表中得到想要的结果?
9.885 2008-9-11 1:51:58 3.210 2008-9-11 1:51:59 3.3329
10.254 2008-9-11 1:52:01 1.006 2008-9-11 1:52:33 5.666
12.225 2008-9-11 1:53:45 2.588 2008-9-11 1:53:58 6.889我想要的结果是:12.225 2008-9-11 1:53:45 1.006 2008-9-11 1:52:33 5.29 就是取最大值字段中的最大值
取最小值字段中的最小值及他们的对应时间,取平均值中的平均值
如何用一条SQL语句从表中得到想要的结果?
9.885 2008-9-11 1:51:58 3.210 2008-9-11 1:51:59 3.3329
10.254 2008-9-11 1:52:01 1.006 2008-9-11 1:52:33 5.666
12.225 2008-9-11 1:53:45 2.588 2008-9-11 1:53:58 6.889
数据重新显示下
(select max(MaxValue) from tb),
(select MaxValueDateTime from tb where MaxValue = (select max(MaxValue) from tb)),
(select min(MinxValue) from tb),
(select MinValueDateTime from tb where MinValue = (select min(MinValue) from tb)),
(select avg(AverageValue) from tb)
2 (select minvalue,minvaluedatetime from t where minvalue=(select min(minvalue) from t)),
3 (select trunc(avg(averagevalue),2) from t); MAXVALUE MAXVALUEDATETIME MINVALUE MINVALUEDATETIME TRUNC(AVG(AVERAGEVALUE),2)
---------- ---------------- ---------- ---------------- --------------------------
12.225 2008-9-11 1:53:4 1.006 2008-9-11 1:52:3 5.29
12.225 2008-9-11 1:53:45 1.006 2008-9-11 1:52:33 5.29
12.225 2008-9-11 1:54:12 1.006 2008-9-11 1:52:33 5.29 2楼的SQL语句在这种情况下不适用,5楼的我在SQL Server中没运行出来
同样的最小值也有可能是多个,如何把他们都显示出来?这样怎么来写SQL语句?
2 (select max(MaxValue) from t),
3 (select MaxValueDateTime from t where MaxValue = (select max(MaxValue) from t)),
4 (select min(MinValue) from t),
5 (select MinValueDateTime from t where MinValue = (select min(MinValue) from t)),
6 (select avg(AverageValue) from t);MAX(MAXVALUE) MAXVALUEDATETIME MIN(MINVALUE) MINVALUEDATETIME AVG(AVERAGEVALUE)
------------- ---------------- ------------- ---------------- -----------------
12.225 2008-9-11 1:53:4 1.006 2008-9-11 1:52:3 4.535725
12.225 2008-9-11 1:54:1 1.006 2008-9-11 1:52:3 4.535725
go
create table tb(MaxValue float, MaxValueDateTime datetime, MinValue float, MinValueDateTime datetime, AverageValue float)
insert Tb select 9.885 , '2008-9-11 1:51:58', 3.210 , '2008-9-11 1:51:59' ,3.3329
insert Tb select 10.254 , '2008-9-11 1:52:01' ,1.006 , '2008-9-11 1:52:33', 5.666
insert Tb select 12.225, '2008-9-11 1:53:45' ,2.588 , '2008-9-11 1:53:58' ,6.889
insert tb select 12.225, '2008-9-11 1:54:12', 2.002, '2008-9-11 1:54:46', 2.255
select
(select max(MaxValue) from tb),
(select top 1 MaxValueDateTime from tb where MaxValue = (select max(MaxValue) from tb)),
(select min(MinValue) from tb),
(select top 1 MinValueDateTime from tb where MinValue = (select min(MinValue) from tb)),
(select avg(AverageValue) from tb)
/*----------------------------------------------------- ------------------------------------------------------ ----------------------------------------------------- ------------------------------------------------------ -----------------------------------------------------
12.225 2008-09-11 01:53:45.000 1.006 2008-09-11 01:52:33.000 4.5357250000000002
*/
(select max(MaxValue) from tb),
(select MaxValueDateTime from tb where MaxValue = (select max(MaxValue) from tb)),
(select min(MinValue) from tb),
(select MinValueDateTime from tb where MinValue = (select min(MinValue) from tb)),
(select avg(AverageValue) from tb)
这样,对吧?但提示有错误服务器: 消息 170,级别 15,状态 1,行 2
第 2 行: ',' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 3
第 3 行: ',' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 4
第 4 行: ',' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 5
第 5 行: ',' 附近有语法错误。你看下
from (select maxvalue, maxvaluedatetime
from tb
where maxvalue = (select max(maxvalue) from tb)),
(select minvalue, minvaluedatetime
from tb
where minvalue = (select min(minvalue) from tb)),
(select avg(averagevalue) from tb);
你这条语句在Oracle数据库中运行没问题?能查询出来我想要的数据?我在SQL Server中试了下,
还是有错,是不是数据库的原因
from (select maxvalue, maxvaluedatetime
from tb
where maxvalue = (select max(maxvalue) from tb)),
(select minvalue, minvaluedatetime
from tb
where minvalue = (select min(minvalue) from tb)),
(select avg(averagevalue) from tb) table
from (select maxvalue, maxvaluedatetime
from tb
where maxvalue = (select max(maxvalue) from tb)a)b,
(select minvalue, minvaluedatetime
from tb
where minvalue = (select min(minvalue) from tb)c)d,
(select avg(averagevalue) from tb)e;
,(select top 1 maxvalueDateTime from t where maxValue = x.maxvalue) max_dt
,minvalue
,(select top 1 minValueDateTime from t where minValue = x.minvalue) min_dt
,avgValue
from (
select max(maxvalue) maxvalue
,min(minvalue) minvalue
,avg(averageValue) avgValue
from t
) x;
select *
from (select maxvalue, maxvaluedatetime
from tb
where maxvalue = (select max(maxvalue) from tb))a,
(select minvalue, minvaluedatetime
from tb
where minvalue = (select min(minvalue) from tb))b,
(select avg(averagevalue) averagevalue from tb)c