this,满足要求么?: select a.XZMC, c.LFRS as 来信次数, sum(case when c.LFRS=1 then 1 else 0 end) as 个人访次数, sum(case when c.LFRS=1 then 1 else 0 end) as 集体访次数, sum(case when c.LFRS>=2 and c.LFRS<=4 then c.LFRS else 0 end) as 集体访人数, sum(case when c.LFRS=1 then 1 else 0 end) as 群体访人数, sum(case when c.LFRS>4 then c.LFRS else 0 end) as 群体访次数 from S_XZDM a,T_LX b,T_LF c where a.XZDM=b.XZDM and a.XZDM=c.XZDM group by a.XZMC, c.LFRS
错了,这个: select a.XZMC, c.LFRS as 来信次数, sum(case when c.LFRS=1 then 1 else 0 end) as 个人访次数, sum(case when c.LFRS>=2 and c.LFRS<=4 then 1 else 0 end) as 集体访次数, sum(case when c.LFRS>=2 and c.LFRS<=4 then c.LFRS else 0 end) as 集体访人数, sum(case when c.LFRS>4 then 1 else 0 end) as 群体访人数, sum(case when c.LFRS>4 then c.LFRS else 0 end) as 群体访次数 from S_XZDM a,T_LX b,T_LF c where a.XZDM=b.XZDM and a.XZDM=c.XZDM group by a.XZMC, c.LFRS
to flyfing(江山如此多娇): 谢谢你的答复,但是“c.LFRS as 来信次数,”,此处错了,“来信次数”应该是指向“T_LX”表的
试下这个: select xzmc ,(select count(*) from T_LX b where b.xzdm=a.xzdm) as 来信次数, (select count(*) from T_LF c where c.xzdm=a.xzdn and lfrs=1) as 个人访次数, (select count(*) from T_LF d where d.xzdm=a.xzdn and lfrs>2 and lfrs<5) as 集体访次数, (select sum(LFRS) from T_LF e where e.xzdm=a.xzdn and lfrs>2 and lfrs<5) as 集体访人数, (select count(*) from T_LF f where f.xzdm=a.xzdn and lfrs>4) as 群体访次数, (select sum(LFRS) from T_LF g where g.xzdm=a.xzdn and lfrs>4) as 群体访人数 from S_XZDM a
select a.XZMC,
c.LFRS as 来信次数,
sum(case when c.LFRS=1 then 1 else 0 end) as 个人访次数,
sum(case when c.LFRS=1 then 1 else 0 end) as 集体访次数,
sum(case when c.LFRS>=2 and c.LFRS<=4 then c.LFRS else 0 end) as 集体访人数,
sum(case when c.LFRS=1 then 1 else 0 end) as 群体访人数,
sum(case when c.LFRS>4 then c.LFRS else 0 end) as 群体访次数
from S_XZDM a,T_LX b,T_LF c
where a.XZDM=b.XZDM and a.XZDM=c.XZDM
group by a.XZMC, c.LFRS
select a.XZMC,
c.LFRS as 来信次数,
sum(case when c.LFRS=1 then 1 else 0 end) as 个人访次数,
sum(case when c.LFRS>=2 and c.LFRS<=4 then 1 else 0 end) as 集体访次数,
sum(case when c.LFRS>=2 and c.LFRS<=4 then c.LFRS else 0 end) as 集体访人数,
sum(case when c.LFRS>4 then 1 else 0 end) as 群体访人数,
sum(case when c.LFRS>4 then c.LFRS else 0 end) as 群体访次数
from S_XZDM a,T_LX b,T_LF c
where a.XZDM=b.XZDM and a.XZDM=c.XZDM
group by a.XZMC, c.LFRS
flyfing(江山如此多娇):
谢谢你的答复,但是“c.LFRS as 来信次数,”,此处错了,“来信次数”应该是指向“T_LX”表的
如
南京市 18 20 6 12 5 30
江阴市 0 0 0 0 0 0
......
select xzmc ,(select count(*) from T_LX b where b.xzdm=a.xzdm) as 来信次数,
(select count(*) from T_LF c where c.xzdm=a.xzdn and lfrs=1) as 个人访次数,
(select count(*) from T_LF d where d.xzdm=a.xzdn and lfrs>2 and lfrs<5) as 集体访次数,
(select sum(LFRS) from T_LF e where e.xzdm=a.xzdn and lfrs>2 and lfrs<5) as 集体访人数,
(select count(*) from T_LF f where f.xzdm=a.xzdn and lfrs>4) as 群体访次数,
(select sum(LFRS) from T_LF g where g.xzdm=a.xzdn and lfrs>4) as 群体访人数
from S_XZDM a