declare @tab table(id varchar(20),F varchar(20))
insert @tab values('01','iv')
insert @tab values('02','po')
insert @tab values('03','iv')
insert @tab values('04','h')
insert @tab values('05','v')
insert @tab values('06','v')
insert @tab values('07','po')
--select * from @tab
select F,F=case when F='iv' then count(F)*1
when f='po' then count(F)*3
when f='h' then count(F)*5
when f='v' then count(F)*8 end
from @tab group by F
F F
-------------------- -----------
h 5
iv 2
po 6
v 16
F,
count(F)*(case F when 'iv' then 1 when 'po' then 3 when 'h' then 5 when 'v' then 8 end)
from
A
group by
F
when f='po' then count(f)*3
when f='h' then count(f)*5
when f='v' then count(f)*8
end ) as f from A group by f
表A中有一字段F,F值为
字段F
iv
PO
H
V
如值为iv那加1,为po加3,为h加5,为v加8
如
id F F1
01 iv A
02 po A
03 iv B
04 h C
05 v A
06 v B
07 po C那得到结果为 A 12
b 9
c 8
(select f=case when f='iv' then 1
when f='po' then 3
when f='h' then 5
when f='v' then 8
end,f1
from A) a
group by f1
when 'po' then 3
when 'h' then 5
when 'v' then 8
end)
from a
group by F1只需要扫描一次Table.
表A中有一字段F,F1,F,F1值为
字段F F1
iv 1
PO 3
H 5
V 8
.
.
.
如值为iv那加1,为po加3,为h加5,为v加8 等等.....
如
表B为
id F F1
01 iv A
02 po A
03 iv B
04 h C
05 v A
06 v B
07 po C那得到结果为 A 12
b 9
c 8
因为表A的内容还会有很多就不能用Case 'po' then 3 这样写死,请问怎么办
group by F