需求描述:对文章按国家分组,统计正面,负面,中立态度分别统计
select
(SELECT cn_name FROM country_info where auto_id= t.country_id) name,
t.country_id,
sum(case when t.support_level=1 then 1 else 0 end) zheng,
sum(case when t.support_level=2 then 1 else 0 end) fu,
sum(case when t.support_level=3 then 1 else 0 end) zhongli
from trace_info_ref t where t.conf_id in (123,345) group by t.country_id
#分析
1.此句sql country_id和 country name的关联写法值得学习。
2.此句的 sum case写法 统计同一字段不同值的写法值得学习。
需求:按用户分组统计转发的文章和参与的话题
分析:转发包含字符串 //@ ,参与话题 包含 #话题#
select count(1) total_num,
sum(case when `fulltext` like '//@%' then 1 else 0 end) tag1_num,
sum(case when `fulltext` like '%#%#%' then 1 else 0 end) tag2_num ,
usercode
from sj_test group by usercode