这是两个表按回复率排行。就是 每个部门共有多少条信息,然后有多少条回复的信息(求百分比的排行)
就是用回复的总条数/总条数结果:
1、**局 100%2、税务局 98%3、人力资源和社会保障局 48%
不太会MYSQL,用SQL写了条语句select phome_enewsclass.classname as 部 门名, tb03.per as 百分 比 from (select tb1.classid, convert(varchar,cast(convert(DECIMAL,tb1.cnt)/convert(DECIMAL,tb2.cnt) * 100 as numeric(12,0))) + '%' as per from (select classid, COUNT(*) as cnt from phome_ecms_xinjian where replycontent<>'' group by classid) tb1,(select classid, COUNT(*) as cnt from phome_ecms_xinjian group by classid) tb2
where tb1.classid = tb2.classid) tb03 right join phome_enewsclass on tb03.classid = phome_enewsclass.classid放到mysql里执行说
请问怎么修改可以正常实现啊?谢谢大家!
就是用回复的总条数/总条数结果:
1、**局 100%2、税务局 98%3、人力资源和社会保障局 48%
不太会MYSQL,用SQL写了条语句select phome_enewsclass.classname as 部 门名, tb03.per as 百分 比 from (select tb1.classid, convert(varchar,cast(convert(DECIMAL,tb1.cnt)/convert(DECIMAL,tb2.cnt) * 100 as numeric(12,0))) + '%' as per from (select classid, COUNT(*) as cnt from phome_ecms_xinjian where replycontent<>'' group by classid) tb1,(select classid, COUNT(*) as cnt from phome_ecms_xinjian group by classid) tb2
where tb1.classid = tb2.classid) tb03 right join phome_enewsclass on tb03.classid = phome_enewsclass.classid放到mysql里执行说
请问怎么修改可以正常实现啊?谢谢大家!
convert(DECIMAL,tb2.cnt)->
cast(tb2.cnt as DECIMAL)
where tb1.classid = tb2.classid) tb03 right join phome_enewsclass on tb03.classid = phome_enewsclass.classid这样对吗?
'1'+'%'->concat('1','%')
from (
select tb1.classid, concat(convert(varchar,cast(cast(tb1.cnt as DECIMAL)/cast(tb2.cnt as DECIMAL)*100 as numeric(12,0))) ,'%') as per
from (
select classid, COUNT(*) as cnt
from phome_ecms_xinjian
where replycontent<>''
group by classid
) tb1,(
select classid, COUNT(*) as cnt
from phome_ecms_xinjian
group by classid
) tb2
where tb1.classid = tb2.classid
) tb03 right join phome_enewsclass on tb03.classid = phome_enewsclass.classid