jsp通过union实现数据库多表分页查询的小例子
发布时间:2015-09-25 编辑:jiaochengji.com
本文分享一个jsp多表分页查询的例子,用到了union联合查询,感兴趣的朋友可以参考学习下。
本节内容:
jsp union 数据库的多表分页查询。
例子:
复制代码 代码示例:
sql.append("select obj.objid,");
sql.append(" obj.objname,");
sql.append(" obj.objtype,");
sql.append(" ep.pic_id,");
sql.append(" ep.pic_dir,");
sql.append(" ep.s_file,");
sql.append(" ep.m_file,");
sql.append(" ep.l_file,");
sql.append(" ep.type,");
sql.append(" ep.state,");
sql.append(" ep.create_time");
sql.append(" from ecom_pictures ep,");
sql.append(" (select ec.company_id as objid,");
sql.append(" ec.name as objname,");
sql.append(" ec.pic_id as pic_id,");
sql.append(" 'COMPANY' as objtype");
sql.append(" from ecom_company ec");
sql.append(" union");
sql.append(" select eg.goods_id as ojbid,");
sql.append(" eg.name as objname,");
sql.append(" eg.pic_id as pic_id,");
sql.append(" 'GOODS' as objtype");
sql.append(" from ecom_goods eg");
sql.append(" union");
sql.append(" select eu.user_id as objid,");
sql.append(" eu.name as objname,");
sql.append(" eu.pic_id as pic_id,");
sql.append(" 'USER' as objtype");
sql.append(" from ecom_user eu) obj");
sql.append(" where obj.pic_id = ep.pic_id");
sql.append(" obj.objname,");
sql.append(" obj.objtype,");
sql.append(" ep.pic_id,");
sql.append(" ep.pic_dir,");
sql.append(" ep.s_file,");
sql.append(" ep.m_file,");
sql.append(" ep.l_file,");
sql.append(" ep.type,");
sql.append(" ep.state,");
sql.append(" ep.create_time");
sql.append(" from ecom_pictures ep,");
sql.append(" (select ec.company_id as objid,");
sql.append(" ec.name as objname,");
sql.append(" ec.pic_id as pic_id,");
sql.append(" 'COMPANY' as objtype");
sql.append(" from ecom_company ec");
sql.append(" union");
sql.append(" select eg.goods_id as ojbid,");
sql.append(" eg.name as objname,");
sql.append(" eg.pic_id as pic_id,");
sql.append(" 'GOODS' as objtype");
sql.append(" from ecom_goods eg");
sql.append(" union");
sql.append(" select eu.user_id as objid,");
sql.append(" eu.name as objname,");
sql.append(" eu.pic_id as pic_id,");
sql.append(" 'USER' as objtype");
sql.append(" from ecom_user eu) obj");
sql.append(" where obj.pic_id = ep.pic_id");
说明:
通过union对四个表做联合查询,如果分页的话,再加上分页条件就可以了。
您可能感兴趣的文章:
jsp通过union实现数据库多表分页查询的小例子
JSP 怎么提高数据库访问效率
mysql explain用法学习
mysql中RAND()随机查询记录的效率问题和解决办法
设计高效合理的MySQL查询语句的建议
mysql where or和where in查询语句的效率分析及优化
MySQL 分表存储的使用示例
建立JSP操作以提高数据库访问的效率
mysql中limit、order by和group by的用法实例解析
JSP常见问题
上一篇:自动启动tomcat服务的简单配置方法
下一篇:jsp下载文件的三种方法
[关闭]