6楼替换
查询:
select iif(id01=1,1,0)+iif(id02=1,1,0)+iif(id03=1,1,0)+...+iif(id07=1,1,0) as c1,
iif(id01=2,1,0)+iif(id02=2,1,0)+iif(id03=2,1,0)+...+iif(id07=2,1,0) as c2,
iif(id01=3,1,0)+iif(id02=3,1,0)+iif(id03=3,1,0)+...+iif(id07=3,1,0) as c3,* from tt
update t1a a set a.c1=iif(id01=1,1,0)+iif(id02=1,1,0)+iif(id03=1,1,0)+...+iif(id07=1,1,0),
a.c2=iif(id01=2,1,0)+iif(id02=2,1,0)+iif(id03=2,1,0)+...+iif(id07=2,1,0),
a.c3=iif(id01=3,1,0)+iif(id02=3,1,0)+iif(id03=3,1,0)+...+iif(id07=3,1,0)
[Quote=引用 2 楼 的回复:]
or
select id,sum(iif(bz=1,1,0)) as c1,sum(iif(bz=2,1,0)) as c2,sum(iif(bz=3,1,0)) as c3
from
(
select id,d01 as bz from tt
union all
select id,d02 from tt
union all
select id,d03 from tt
uni……
[/Quote]
or
select id,sum(iif(bz=1,1,0)) as c1,sum(iif(bz=2,1,0)) as c2,sum(iif(bz=3,1,0)) as c3
from
(
select id,d01 as bz from tt
union all
select id,d02 from tt
union all
select id,d03 from tt
union all
select id,d04 from tt
union all
select id,d05 from tt
union all
select id,d06 from tt
union all
select id,d07 from tt ) group by id
select id,sum(iif(id01=1,1,0))+sum(iif(id02=1,1,0))+...+sum(iif(id07=1,1,0)) as c1,
sum(iif(id01=2,1,0))+sum(iif(id02=2,1,0))+...+sum(iif(id07=2,1,0)) as c2,
sum(iif(id01=3,1,0))+sum(iif(id02=3,1,0))+...+sum(iif(id07=3,1,0)) as c3
from tt group by id