自关联查询实例SQL语句

1. SELECT a.info_id,a.info_name FROM dc_info a INNER JOIN dc_info b
   
2. 
   
3. ON a.info_id=b.info_id
   
4. 
   
5. WHERE a.info_id > 395241
   
6. 
   
7. and a.info_module = 'cms'
   
8. 
   
9. and b.info_name like '%游戏%'

复制代码

视图查询可以实现不依赖数据库视图的多表查询，并不需要数据库支持视图，例如：

1. SELECT * FROM dc_info
   
2. 
   
3. JOIN (SELECT info_id FROM dc_info LIMIT 100,10) t1 ON t1.info_id = dc_info.info_id
   
4. 
   
5. JOIN (SELECT info_id FROM dc_info WHERE  `info_status` = 'normal'  AND `info_action` = 'index'  AND `info_controll` = 'detail'  AND `info_module` = 'cms') t2 ON t2.info_id = t1.info_id
   
6. 
   
7. JOIN (SELECT detail_id FROM dc_term_map where term_id=66) t3 ON t3.detail_id = dc_info.info_id
   
8. 
   
9. order by info_views desc

复制代码