三亚数据库统计sql

[b]

--差评数据统计

select a.shop_name,a.shop_url,a.ptmc,a.hy,nm.ev_user,nm.ev_time,nm.ev_content from (select t.shop_id,
t.shop_name,
t.shop_url,
(select name from pub_codetable where codetable ='sjly' and code=t.platform_code ) ptmc,
(select name from pub_codetable where codetable ='big' and code=ty.shop_sub_type) hy
from NM_SHOP t inner join nm_shop_type ty on t.shop_id=ty.shop_id) a inner join nm_evaluation nm on a.shop_id=nm.shop_id
where nm.is_bad ='1'

--平台统计 店铺评论数量
select (select name from pub_codetable c1 where codetable ='sjly' and c1.code=t .platform_code) ,t.* from
( select platform_code , sum(n1.ev_num) as d,sum(n1.bad_ev_num) as f from nm_shop n1 group by platform_code )
t
--行业统计 店铺评论数量

select (select name from pub_codetable c1 where codetable ='big' and code =t.hy ),hy, sum(ev_num),sum(bad_ev_num) from
(select (select shop_type from nm_shop_type t1 where t1.shop_id=n1.shop_id) as hy ,n1.ev_num,n1.bad_ev_num from nm_shop n1 )
t group by t.hy

--店铺数据

select t.shop_name,
(select name from pub_codetable where codetable ='sjly' and code=t.platform_code ) ptmc,
(select name from pub_codetable where codetable ='big' and code=ty.shop_sub_type) hy,
t.shop_address,
t.shop_url,
t.hpl,
'',
'',
'',
'',
(case t.sfzc when '1' then '已注册'
else '' end) yyzz,
t.ev_num,
t.bad_ev_num
from NM_SHOP t inner join nm_shop_type ty on t.shop_id=ty.shop_id[/b]