select ditinct a.节目名称 as progname ,a.业务代码 as progcode,count(b.id) as incount ,
count(c.id) as outcount,sum(c.费率) price
from 表1 as a,
表2 as b,
表3 as c加distinct子句试一下。
count(c.id) as outcount,sum(c.费率) price
from 表1 as a,
表2 as b,
表3 as c加distinct子句试一下。
再从中间表中统计
count(c.id) as outcount,sum(c.费率) price
from 表1 as a,
(select min(id) as id,业务代码 from 表2 group by 业务代码) as b,
(select min(id) as id,业务代码,sum(费率) as 费率 from 表3 group by 业务代码) as c
where a.业务代码=b.业务代码 and a.业务代码=c.业务代码
group by a.节目名称,a.业务代码
count(c.id) as outcount,sum(c.费率) price
from (表1 as a inner join 表2 as b on a.业务代码 =b.业务代码) inner join 表3 as c on a.业务代码=c.业务代码
怎样改才好!能不能说清楚!
liyunsong2000(我选择,我喜欢)兄:
象你说的,条数对了,费率不对。还是用的乘法,其实只有表三才有费率!
(select count(id) from 表2 where a.业务代码=业务代码) as incount ,
(select count(id) from 表3 where a.业务代码=业务代码) as outcount ,
(select sum(费率) from 表3 where a.业务代码=业务代码) as price
from 表1 as a
group by a.节目名称,a.业务代码
(select count(id) from 表2 where a.业务代码=业务代码) as incount ,
(select count(id) from 表3 where a.业务代码=业务代码) as outcount ,
(select sum(费率) from 表3 where a.业务代码=业务代码) as price
from 表1 as a
group by a.节目名称,a.业务代码
你的方法还不错,就是把表一所有的业务代码都显示出来了,能不能只显示表二,表三有的代码!
(select count(id) from 表2 where a.业务代码=业务代码) as incount ,
(select count(id) from 表3 where a.业务代码=业务代码) as outcount ,
(select sum(费率) from 表3 where a.业务代码=业务代码) as price
from 表1 as a
where a.业务代码 in (select 业务代码 from 表2 union select 业务代码 from 表3)
group by a.节目名称,a.业务代码
(select count(id) from 表2 where a.业务代码=业务代码) as incount ,
(select count(id) from 表3 where a.业务代码=业务代码) as outcount ,
(select sum(费率) from 表3 where a.业务代码=业务代码) as price
from 表1 as a
where a.业务代码 in (select 业务代码 from 表2 union select 业务代码 from 表3)
group by a.节目名称,a.业务代码