创建视图

CREATE VIEW `wos_tzgg_con` AS select `wos_xxzy_con`.`id` AS `id`,`wos_xxzy_con`.`title`

AS `title`,`wos_xxzy_con`.`code` AS `code`,from_unixtime(`wos_xxzy_con`.`dtime`) AS

`dtime`,'xw' AS `source` from `wos_xxzy_con` where (`wos_xxzy_con`.`iffb` = 1) union select

`dbbinzhou_xxgk`.`yun_news`.`id` AS `id`,`dbbinzhou_xxgk`.`yun_news`.`title` AS

`title`,`dbbinzhou_xxgk`.`yun_news`.`code` AS

`code`,`dbbinzhou_xxgk`.`yun_news`.`pickerdate` AS `dtime`,'gk' AS `source` from

`dbbinzhou_xxgk`.`yun_news` where (`dbbinzhou_xxgk`.`yun_news`.`iffb` = 1) union select

`dbbinzhou_tyhd`.`yun_mail`.`id` AS `id`,`dbbinzhou_tyhd`.`yun_mail`.`title` AS

`title`,`dbbinzhou_tyhd`.`yun_mail`.`indexno` AS `code`,from_unixtime

(`dbbinzhou_tyhd`.`yun_mail`.`dtime`) AS `dtime`,'smlx' AS `source` from

`dbbinzhou_tyhd`.`yun_mail` where (`dbbinzhou_tyhd`.`yun_mail`.`iffb` = 1 and

`dbbinzhou_tyhd`.`yun_mail`.`isopen` = 1) union select `dbbinzhou_tyhd`.`yun_opinion`.`id`

AS `id`,`dbbinzhou_tyhd`.`yun_opinion`.`title` AS

`title`,`dbbinzhou_tyhd`.`yun_opinion`.`id` AS `code`,from_unixtime

(`dbbinzhou_tyhd`.`yun_opinion`.`dtime`) AS `dtime`,'yjzj' AS `source` from

`dbbinzhou_tyhd`.`yun_opinion` where (`dbbinzhou_tyhd`.`yun_opinion`.`iffb` = 1 ) union

select `dbbinzhou_tyhd`.`yun_survey`.`id` AS `id`,`dbbinzhou_tyhd`.`yun_survey`.`title` AS

`title`,`dbbinzhou_tyhd`.`yun_survey`.`code` AS `code`,from_unixtime

(`dbbinzhou_tyhd`.`yun_survey`.`dtime`) AS `dtime`,'mydc' AS `source` from

`dbbinzhou_tyhd`.`yun_survey` where (`dbbinzhou_tyhd`.`yun_survey`.`iffb` = 1 ) union

select `dbbinzhou_zxft`.`zxft_detail`.`id` AS `id`,`dbbinzhou_zxft`.`zxft_detail`.`ft_name` AS

`title`,'zxftzxft' AS `code`,from_unixtime

(`dbbinzhou_zxft`.`zxft_detail`.`ft_ltime_s`) AS `dtime`,'zxft' AS `source` from

`dbbinzhou_zxft`.`zxft_detail` where (`dbbinzhou_zxft`.`zxft_detail`.`ft_iffb` = 1 )
union

select `dbbinzhou_web`.`wos_news_con`.`id` AS `id`,`dbbinzhou_web`.`wos_news_con`.`title` AS

`title`,`qtqt` AS `code`,from_unixtime

(`dbbinzhou_web`.`wos_news_con`.`dtime`) AS `dtime`,'qt' AS `source` from

`dbbinzhou_web`.`wos_news_con` where (`dbbinzhou_web`.`wos_news_con`.`iffb` = 1 );

 

 

 

CREATE VIEW `wos_bxzonghesearch`  AS  select `wos_xxzy_con`.`id` AS `id`,`wos_xxzy_con`.`title` AS `title`,
`wos_xxzy_con`.`code` AS `code`,from_unixtime(`wos_xxzy_con`.`dtime`)
AS `dtime`,'xw' AS `source` from `wos_xxzy_con`
where (`wos_xxzy_con`.`iffb` = 1) union select `dbbinzhou_xxgkbx`.`yun_news`.`id` AS `id`,
`dbbinzhou_xxgkbx`.`yun_news`.`title` AS `title`,`dbbinzhou_xxgkbx`.`yun_news`.`code` AS `code`,
`dbbinzhou_xxgkbx`.`yun_news`.`pickerdate` AS `dtime`,'gk' AS `source` from `dbbinzhou_xxgkbx`.`yun_news` where
(`dbbinzhou_xxgkbx`.`yun_news`.`iffb` = 1) union select `dbbinzhou_tyhd`.`yun_mail`.`id` AS `id`,
`dbbinzhou_tyhd`.`yun_mail`.`title` AS `title`,`dbbinzhou_tyhd`.`yun_mail`.`indexno` AS `code`,
from_unixtime(`dbbinzhou_tyhd`.`yun_mail`.`dtime`) AS `dtime`,'smlx' AS `source` from `dbbinzhou_tyhd`.`yun_mail` where
((`dbbinzhou_tyhd`.`yun_mail`.`iffb` = 1) and (`dbbinzhou_tyhd`.`yun_mail`.`isopen` = 1) and (`dbbinzhou_tyhd`.`yun_mail`.`blname` like '%博兴%')) union select
`dbbinzhou_tyhd`.`yun_opinion`.`id` AS `id`,`dbbinzhou_tyhd`.`yun_opinion`.`title` AS `title`,`dbbinzhou_tyhd`.`yun_opinion`.`id` AS `code`,
from_unixtime(`dbbinzhou_tyhd`.`yun_opinion`.`dtime`) AS `dtime`,'yjzj' AS `source` from
`dbbinzhou_tyhd`.`yun_opinion` where (`dbbinzhou_tyhd`.`yun_opinion`.`iffb` = 1) and (`dbbinzhou_tyhd`.`yun_opinion`.`orgid` = 'Cebb8ed395c8133a45ec8e5b27bb7750b') union select `dbbinzhou_tyhd`.`yun_survey`.`id` AS `id`,
`dbbinzhou_tyhd`.`yun_survey`.`title` AS `title`,`dbbinzhou_tyhd`.`yun_survey`.`code` AS `code`,
from_unixtime(`dbbinzhou_tyhd`.`yun_survey`.`dtime`) AS `dtime`,'mydc' AS `source` from
`dbbinzhou_tyhd`.`yun_survey` where (`dbbinzhou_tyhd`.`yun_survey`.`iffb` = 1) and (`dbbinzhou_tyhd`.`yun_survey`.`areaid` = 371625) union select `dbbinzhou_zxft`.`zxft_detail`.`id` AS `id`,
`dbbinzhou_zxft`.`zxft_detail`.`ft_name` AS `title`,'zxftzxft' AS `code`,from_unixtime(`dbbinzhou_zxft`.`zxft_detail`.`ft_ltime_s`) AS
`dtime`,'zxft' AS `source` from `dbbinzhou_zxft`.`zxft_detail` where (`dbbinzhou_zxft`.`zxft_detail`.`ft_iffb` = 1) and ((`dbbinzhou_zxft`.`zxft_detail`.`areaid` = 371625))
union select `wos_news_con`.`id` AS `id`,`wos_news_con`.`title` AS `title`,'qtqt' AS `code`,from_unixtime(`wos_news_con`.`dtime`)
AS `dtime`,'qt' AS `source` from `wos_news_con` where (`wos_news_con`.`iffb` = 1) and ((`wos_news_con`.`areaid` = 371625))









SELECT c . * , COUNT( kp.id ) AS num
FROM biz_company AS c, biz_kp_fsn AS kp
WHERE kp.zj_id = c.memberid
GROUP BY kp.zj_id
ORDER BY c.pxinfo DESC , num DESC



SELECT c . * , ( SELECT COUNT( kp.zj_id ) AS num FROM biz_kp_fsn AS kp WHERE c.memberid = kp.zj_id GROUP BY kp.zj_id ) AS num FROM biz_company AS c WHERE c.iffb='1' and c.catpath regexp '0633' and c.zcaddr in (200,557,558,559,560,561,562,563,564,565,566,567) ORDER BY c.pxinfo DESC , num DESC limit 10,10