求一个select语句，得到多列中的最大值和最小值

wnba1983
等级:
发表于：2008-05-12 08:53:136楼得分:0
SQL code
为什么不能这样写呢
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'全网发电'
union

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'全网用电'
union

这样写的效率好低啊，查询起来很慢，楼上的能给个好方法吗
修改删除举报引用回复