三亚数据库统计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 ='big' and code=ty.shop_sub_type) hy,

t.shop_address,

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]