SELECT TOP 1 [GROUP],AVG([VALUE])AS [VALUE] FROM TB GROUP BY [GROUP] ORDER BY AVG([VALUE])UNION ALLSELECT TOP 1 [GROUP],AVG([VALUE])AS [VALUE] FROM TB GROUP BY [GROUP] ORDER BY AVG([VALUE]) DESC
select max(value) ma,min(value) mi FROM ( select [Group], avg(value) value from [Table] group by [Group] )aa
select Min(aa.Value) as minValue,Max(aa.Value) as maxValue from ( select [group] ,AVG(value)as Value from table2 group by [group] ) aa
select max(value),min(value) from ( select [Group], avg(value) value from tb group by [Group] )t
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([value] int,[Group] int) insert [tb] select 12,1 union all select 13,1 union all select 14,2 union all select 14,2 union all select 15,3 union all select 15,3 -- --select * from [tb]; with cte1 as ( SELECT [GROUP],AVG([VALUE])AS [VALUE] FROM TB GROUP BY [GROUP] )select * from cte1 c where not exists(select 1 from cte1 where [VALUE]>c.[VALUE] ) union all select * from cte1 c where not exists(select 1 from cte1 where [VALUE]<c.[VALUE] ) /* GROUP VALUE ----------- ----------- 3 15 1 12 */
select max(value) as [max],min(value) as [min] FROM ( select [Group], avg(value) value from [Table] group by [Group] ) t
SELECT TOP 1 [GROUP],AVG([VALUE])AS [VALUE]
FROM TB
GROUP BY [GROUP]
ORDER BY AVG([VALUE])UNION ALLSELECT TOP 1 [GROUP],AVG([VALUE])AS [VALUE]
FROM TB
GROUP BY [GROUP]
ORDER BY AVG([VALUE]) DESC
FROM
(
select [Group], avg(value) value
from [Table]
group by [Group]
)aa
select Min(aa.Value) as minValue,Max(aa.Value) as maxValue from
(
select [group]
,AVG(value)as Value from table2 group by [group]
) aa
max(value),min(value)
from
(
select
[Group], avg(value) value
from
tb
group by
[Group]
)t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([value] int,[Group] int)
insert [tb]
select 12,1 union all
select 13,1 union all
select 14,2 union all
select 14,2 union all
select 15,3 union all
select 15,3
--
--select * from [tb];
with cte1 as
(
SELECT [GROUP],AVG([VALUE])AS [VALUE]
FROM TB
GROUP BY [GROUP]
)select * from cte1 c where not exists(select 1 from cte1 where [VALUE]>c.[VALUE] )
union all
select * from cte1 c where not exists(select 1 from cte1 where [VALUE]<c.[VALUE] )
/*
GROUP VALUE
----------- -----------
3 15
1 12
*/
select max(value) as [max],min(value) as [min]
FROM
(
select [Group], avg(value) value
from [Table]
group by [Group]
) t