select 名称,'最大值' as 项目,max(字段1) as 字段1,max(字段2) as 字段2 ...... from table1 group by 名称
union all
select 名称,'最小值' as 项目,min(字段1) as 字段1,min(字段2) as 字段2 ...... from table1 group by 名称
union all
select 名称,'平均值' as 项目,avg(字段1) as 字段1,avg(字段2) as 字段2 ...... from table1 group by 名称
order by 名称,项目
union all
select 名称,'最小值' as 项目,min(字段1) as 字段1,min(字段2) as 字段2 ...... from table1 group by 名称
union all
select 名称,'平均值' as 项目,avg(字段1) as 字段1,avg(字段2) as 字段2 ...... from table1 group by 名称
order by 名称,项目
union all
select 名称, 项目='平均直'avg(字段1) as 字段1,avg(字段2) as 字段2 ...... from table1 group by 名称
union all
union all
select 名称, 项目='最宵直',min(字段1) as 字段1,min (字段2) as 字段2 ...... from table1 group by 名称
union all
select 名称, 项目='平均直'avg(字段1) as 字段1,avg(字段2) as 字段2 ...... from table1 group by 名称)T order by T.名稱
from table1
group by 名称
union all
select 名称,'最小值' as 项目,min(字段1) as 字段1,min(字段2) as 字段2 ......
from table1
group by 名称
union all
select 名称,'平均值' as 项目,avg(字段1) as 字段1,avg(字段2) as 字段2 ......
from table1
group by 名称
order by 名称,项目
但在查询分析器内执行调试的时候会出现
警告: 聚合或其它 SET 操作消除了空值。问题
查找过相关资料说是因为
max(字段1),max(字段2)...
的字段值为null
请大家帮手想想怎样解决
from table1
group by 名称
union all
select 名称,'最小值' as 项目,min(isnull(字段1,0)) as 字段1,min(isnull(字段2,0) as 字段2 ......
from table1
group by 名称
union all
select 名称,'平均值' as 项目,avg(isnull(字段1,0) as 字段1,avg(isnull(字段2,0) as 字段2 ......
from table1
group by 名称
order by 名称,项目