--> By dobear_0922(小熊) 2009-04-02 10:34:02 --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([出发城市] varchar(4),[目的城市] varchar(4),[两地距离] int) insert [tb] select '北京','沈阳',0 union all select '北京','上海',1000 union all select '沈阳','大连',5000 union all select '北京','沈阳',100000 union all select '沈阳','天津',10000 union all select '北京','沈阳',600000000 union all select '北京','沈阳',60000000 union all select '北京','沈阳',6000000 union all select '北京','沈阳',600000 union all select '北京','沈阳',60000 union all select '北京','沈阳',6000 union all select '北京','沈阳',600 select [出发城市],[目的城市] , [两地距离]=avg(case when [level]=1 or [level]=5 then null else [两地距离] end) from ( select *, [Level]= ntile(5) over (partition by [出发城市],[目的城市] order by [两地距离]) from [tb]) T group by [出发城市],[目的城市] having(count(*)>=5) union all select [出发城市],[目的城市], [两地距离]=avg([两地距离]) from tb group by [出发城市],[目的城市] having(count(*)<5) order by [出发城市],[目的城市]/* 出发城市 目的城市 两地距离 ---- ---- ----------- 北京 上海 1000 北京 沈阳 11127666 沈阳 大连 5000 沈阳 天津 10000 */drop table tb
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([出发城市] varchar(4),[目的城市] varchar(4),[两地距离] int)
insert [tb]
select '北京','沈阳',0 union all
select '北京','上海',1000 union all
select '沈阳','大连',5000 union all
select '北京','沈阳',100000 union all
select '沈阳','天津',10000 union all
select '北京','沈阳',600000000 union all
select '北京','沈阳',60000000 union all
select '北京','沈阳',6000000 union all
select '北京','沈阳',600000 union all
select '北京','沈阳',60000 union all
select '北京','沈阳',6000 union all
select '北京','沈阳',600 select [出发城市],[目的城市]
, [两地距离]=avg(case when [level]=1 or [level]=5 then null else [两地距离] end)
from (
select *, [Level]= ntile(5) over (partition by [出发城市],[目的城市] order by [两地距离])
from [tb]) T
group by [出发城市],[目的城市]
having(count(*)>=5)
union all
select [出发城市],[目的城市], [两地距离]=avg([两地距离])
from tb
group by [出发城市],[目的城市]
having(count(*)<5)
order by [出发城市],[目的城市]/*
出发城市 目的城市 两地距离
---- ---- -----------
北京 上海 1000
北京 沈阳 11127666
沈阳 大连 5000
沈阳 天津 10000
*/drop table tb