比如我有个表a b c d
----------------------------------------------------
10 20 30 40谁给我写一个select语句,得到如下结构x y
-------------
10 40 也就是得到上面一条记录中的最大列和最小列(where条件只查到一行记录)
----------------------------------------------------
10 20 30 40谁给我写一个select语句,得到如下结构x y
-------------
10 40 也就是得到上面一条记录中的最大列和最小列(where条件只查到一行记录)
[/align]
为什么不能这样写呢
select a,b,c,d least(a,b,c,d) x, greatest(a,b,c,d) y from t 报错说非单组分组函数,我这样写通过了
select name, dliang,
(select MAX(greatest(S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12,
S13, S14, S15, S16, S17, S18, S19, S20, S21, S22, S23, S24, S25, S26, S27,
S28, S29, S30, S31, S32, S33, S34, S35, S36, S37, S38, S39, S40, S41, S42, S43,
S44, S45, S46, S47, S48, S49, S50, S51, S52, S53, S54, S55, S56, S57, S58, S59,
S60, S61, S62, S63, S64, S65, S66, S67, S68, S69, S70, S71, S72, S73, S74, S75,
S76, S77, S78, S79, S80, S81, S82, S83, S84, S85, S86, S87, S88, S89, S90, S91,
S92, S93, S94, S95, S96)) from hdbalanceddjh t
where to_char(hddate,'yyyy-mm-dd')= to_char(sysdate-3,'yyyy-mm-dd') and name ='全网发电')
maxData,
(select MIN(least(S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12,
S13, S14, S15, S16, S17, S18, S19, S20, S21, S22, S23, S24, S25, S26, S27,
S28, S29, S30, S31, S32, S33, S34, S35, S36, S37, S38, S39, S40, S41, S42, S43,
S44, S45, S46, S47, S48, S49, S50, S51, S52, S53, S54, S55, S56, S57, S58, S59,
S60, S61, S62, S63, S64, S65, S66, S67, S68, S69, S70, S71, S72, S73, S74, S75,
S76, S77, S78, S79, S80, S81, S82, S83, S84, S85, S86, S87, S88, S89, S90, S91,
S92, S93, S94, S95, S96)) from hdbalanceddjh t
where to_char(hddate,'yyyy-mm-dd')=to_char(sysdate-3,'yyyy-mm-dd') and name in'全网发电')
minData
from hdbalanceddjh
where to_char(hddate,'yyyy-mm-dd')=to_char(sysdate-3,'yyyy-mm-dd') and name in'全网发电'
unionselect name, dliang,
(select MAX(greatest(S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12,
S13, S14, S15, S16, S17, S18, S19, S20, S21, S22, S23, S24, S25, S26, S27,
S28, S29, S30, S31, S32, S33, S34, S35, S36, S37, S38, S39, S40, S41, S42, S43,
S44, S45, S46, S47, S48, S49, S50, S51, S52, S53, S54, S55, S56, S57, S58, S59,
S60, S61, S62, S63, S64, S65, S66, S67, S68, S69, S70, S71, S72, S73, S74, S75,
S76, S77, S78, S79, S80, S81, S82, S83, S84, S85, S86, S87, S88, S89, S90, S91,
S92, S93, S94, S95, S96)) from hdbalanceddjh t
where to_char(hddate,'yyyy-mm-dd')= to_char(sysdate-3,'yyyy-mm-dd') and name ='全网用电')
maxData,(select MIN(least(S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12,
S13, S14, S15, S16, S17, S18, S19, S20, S21, S22, S23, S24, S25, S26, S27,
S28, S29, S30, S31, S32, S33, S34, S35, S36, S37, S38, S39, S40, S41, S42, S43,
S44, S45, S46, S47, S48, S49, S50, S51, S52, S53, S54, S55, S56, S57, S58, S59,
S60, S61, S62, S63, S64, S65, S66, S67, S68, S69, S70, S71, S72, S73, S74, S75,
S76, S77, S78, S79, S80, S81, S82, S83, S84, S85, S86, S87, S88, S89, S90, S91,
S92, S93, S94, S95, S96)) from hdbalanceddjh t
where to_char(hddate,'yyyy-mm-dd')=to_char(sysdate-3,'yyyy-mm-dd') and name in'全网用电')
minData
from hdbalanceddjh
where to_char(hddate,'yyyy-mm-dd')=to_char(sysdate-3,'yyyy-mm-dd') and name in'全网用电'
union这样写的效率好低啊,查询起来很慢,楼上的能给个好方法吗
另做个统计表,存放每天的最大最小数据;
做个JOB,每天凌晨时统计一下数据放到一个临时表里.