sql 查询一行内几列的平均值,最大值,最小值,怎么写比如 name 1 2 6 7 9 0 3 求 1,2,6,7,9,0,3这几个值的最大最小平均值
来源:学生作业帮助网 编辑:作业帮 时间:2024/06/23 22:20:09
![sql 查询一行内几列的平均值,最大值,最小值,怎么写比如 name 1 2 6 7 9 0 3 求 1,2,6,7,9,0,3这几个值的最大最小平均值](/uploads/image/z/5171526-54-6.jpg?t=sql+%E6%9F%A5%E8%AF%A2%E4%B8%80%E8%A1%8C%E5%86%85%E5%87%A0%E5%88%97%E7%9A%84%E5%B9%B3%E5%9D%87%E5%80%BC%2C%E6%9C%80%E5%A4%A7%E5%80%BC%2C%E6%9C%80%E5%B0%8F%E5%80%BC%2C%E6%80%8E%E4%B9%88%E5%86%99%E6%AF%94%E5%A6%82+name+1+2+6+7+9+0+3+%E6%B1%82+1%2C2%2C6%2C7%2C9%2C0%2C3%E8%BF%99%E5%87%A0%E4%B8%AA%E5%80%BC%E7%9A%84%E6%9C%80%E5%A4%A7%E6%9C%80%E5%B0%8F%E5%B9%B3%E5%9D%87%E5%80%BC)
sql 查询一行内几列的平均值,最大值,最小值,怎么写比如 name 1 2 6 7 9 0 3 求 1,2,6,7,9,0,3这几个值的最大最小平均值
sql 查询一行内几列的平均值,最大值,最小值,怎么写
比如 name 1 2 6 7 9 0 3
求 1,2,6,7,9,0,3这几个值的最大最小平均值
sql 查询一行内几列的平均值,最大值,最小值,怎么写比如 name 1 2 6 7 9 0 3 求 1,2,6,7,9,0,3这几个值的最大最小平均值
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