dev/常用SQL语句/dcp对接文档/中英文差异.txt

<!-----企业中英文差异------>
SELECT * from 
(SELECT * FROM `base_unit_view` where industry_dict IN ('04','05','06','09','11','01','02') AND showable != '0' and `language` = "en") A 
where not exists 
(SELECT * FROM (SELECT * FROM `base_unit_view` where industry_dict IN ('04','05','06','09','11','01','02') AND showable != '0' and `language` = "zh") B WHERE  A.create_date = B.create_date)
<!-----研究成果中英文差异------>
select a.name,
(select '有' from base_research_result_view b where a.id = b.entity_id and b.language ='ZH' and b.status ='0') as 'zh',
(select '有' from base_research_result_view b where a.id = b.entity_id and b.language ='EN' and b.status ='0') as 'en' 
from
base_research_result_entity a
order by zh,en
<!-----合作交流中英文差异------>
select a.activity_name,
(select '有' from activity_info b where a.id = b.base_activity_entity_id and b.language ='ZH' and b.activity_forum ='1') as 'zh',
(select '有' from activity_info b where a.id = b.base_activity_entity_id and b.language ='EN' and b.activity_forum ='1') as 'en' 
from
base_activity_entity a
order by zh,en