Question

sql 查询一行内几列的平均值,最大值,最小值,怎么写

Answer 1

oracle的话直接用greatest函数,sql server的话照这么写：

select t1.name,max(t1.a) maxnum,min(t1.a) minnum,avg(t1.a) avgnum from

(select name,num1 a from tb1

union all

select name,num2 a from tb1

union all

select name,num3 a from tb1

union all

select name,num4 a from tb1

union all

select name,num5 a from tb1

union all

select name,num6 a from tb1

union all

select name,num7 a from tb1) t1

group by t1.name;

测试数据：

create table tb1(name varchar(50),num1 int,num2 int,num3 int,num4 int,num5 int,num6 int,num7 int)

insert into tb1 select 'Tom',1,2,6,7,9,0,3

insert into tb1 select 'Jacky',7,6,5,4,3,2,1

结果：

name maxnum minnum avgnum

Jacky 7 1 4

Tom 9 0 4