用case whenselect cons, set 字段= case when jb='1' then avg(num)*0.3 else avg(num)*0.7 end from kao_r_List ...
create table #tb(jb int,cons varchar(10),num int) insert into #tb select 2,'史',10 union all select 4,'张',18 union all select 3,'宁',9 union all select 1,'林',18 union all select 2,'马',16 union all select 1,'宁',29 goselect * from ( select cons,avg(num)*0.3 as cavg from #tb where jb='1' group by cons union all select cons,avg(num)*0.7 from #tb where jb<>'1' group by cons )t order by cavg/* cons cavg -------------------------- 林 5.4 宁 6.3 史 7.0 宁 8.7 马 11.2 张 12.6*/
jb cons num
2 史 10
4 张 18
3 宁 9
1 林 18
2 马 16
1 宁 29
set 字段=
case
when jb='1' then avg(num)*0.3
else avg(num)*0.7
end
from kao_r_List ...
create table #tb(jb int,cons varchar(10),num int)
insert into #tb
select 2,'史',10
union all select 4,'张',18
union all select 3,'宁',9
union all select 1,'林',18
union all select 2,'马',16
union all select 1,'宁',29
goselect *
from
(
select cons,avg(num)*0.3 as cavg
from #tb where jb='1' group by cons
union all
select cons,avg(num)*0.7
from #tb where jb<>'1' group by cons
)t
order by cavg/*
cons cavg
--------------------------
林 5.4
宁 6.3
史 7.0
宁 8.7
马 11.2
张 12.6*/