1.2009-02-15

SELECT a.*,b.surports,b.againsts,count(c.id) FROM phpme_news as a left join phpme_digg as b on(b.content_id=a.id and b.cat_id=a.cat_id) left join phpme_comment as c on(a.id=c.content_id and a.cat_id=c.cat_id) where a.cat_id in(45) group by id order by id desc limit 0,10

 

 

2.2009-02-19

union all连接查询的高级应用

select z.* from (SELECT a.*,b.cat_name FROM phpme_news as a left join phpme_category as b on(a.cat_id=b.cat_id) where tags like '% 测试标签 %'
union all
SELECT a.*,b.cat_name FROM phpme_article as a left join phpme_category as b on(a.cat_id=b.cat_id) where tags like '% 测试标签 %'
union all
SELECT a.*,b.cat_name FROM phpme_download as a left join phpme_category as b on(a.cat_id=b.cat_id) where tags like '% 测试标签 %') as z order by z.time desc limit 0,10

 

 

3.2009-02-21

获取搜索的结果数

select sum(total) from (select count(*) as total from phpme_news as a where title like '%php atr%' union select count(*) as total from phpme_news as a where title like '%php%' union select count(*) as total from phpme_news as a where title like '%atr%' ) as z

 

获取搜索的结果

select z.* from(SELECT a.*,b.surports,b.againsts,count(c.id),d.cat_name FROM phpme_news as a left join phpme_digg as b on (b.content_id=a.id and b.cat_id=a.cat_id) left join phpme_comment as c on(a.id=c.content_id and a.cat_id=c.cat_id) left join phpme_category as d on(d.cat_id=a.cat_id) where a.deleted!=1 and a.title like '%巴西 总统%' group by a.id union SELECT a.*,b.surports,b.againsts,count(c.id),d.cat_name FROM phpme_news as a left join phpme_digg as b on (b.content_id=a.id and b.cat_id=a.cat_id) left join phpme_comment as c on(a.id=c.content_id and a.cat_id=c.cat_id) left join phpme_category as d on(d.cat_id=a.cat_id) where a.deleted!=1 and a.title like '%巴西%' group by id union SELECT a.*,b.surports,b.againsts,count(c.id),d.cat_name FROM phpme_news as a left join phpme_digg as b on (b.content_id=a.id and b.cat_id=a.cat_id) left join phpme_comment as c on(a.id=c.content_id and a.cat_id=c.cat_id) left join phpme_category as d on(d.cat_id=a.cat_id) where a.deleted!=1 and a.title like '%总统%' group by id ) as z order by z.time desc limit 0,20