首页 / 更多教程 / 关于分页SQL的小总结
关于分页SQL的小总结
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了关于分页SQL的小总结，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11430字，纯文字阅读大概需要17分钟。
内容图文
select id="findPage" resultMap="MinOrderInfo" parameterType="map">
SELECT o.*,w.name buyName,w.MOBILE buyMobile,aa.name sellName,aa.MOBILE sellMobile,rs.CAR_BRAND_NAME carBrandName1,rs.TRANS_PRICE,scn.price,scs.CAR_BRAND_NAME carBrandName3,scs.EXPECT_TRANS_PRICE FROM order_info o
INNER JOIN web_user w ON w.id=o.SEARCH_USER_ID
LEFT JOIN (SELECT o.SOURCE_USER_ID ,w.name,w.MOBILE mobile,o.Id FROM order_info o INNER JOIN web_user w ON w.id=o.SOURCE_USER_ID) aa ON aa.Id=o.Id
LEFT JOIN release_source rs ON o.source_id = rs.id
LEFT JOIN search_car_normal scn ON o.SEARCH_normal_ID = scn.id
LEFT JOIN search_car_special scs ON o.search_special_ID = scs.id
WHERE 1= 1
<if test="id != null and id != ‘‘">and o.Id = #{id}</if>
<if test="sourceId != null and sourceId != ‘‘">and o.SOURCE_ID = #{sourceId}</if>
<if test="sourceUserId != null and sourceUserId != ‘‘">and o.SOURCE_USER_ID = #{sourceUserId}</if>
<if test="searchId != null and searchId != ‘‘">and o.SEARCH_ID = #{searchId}</if>
<if test="searshUserId != null and searshUserId != ‘‘">and o.SEARCH__USER_ID = #{searshUserId}</if>
<if test="num != null and num != ‘‘">and o.NUM = #{num}</if>
<if test="depositPrice != null and depositPrice != ‘‘">and o.DEPOSIT_PRICE = #{depositPrice}</if>
<if test="registratAddress != null and registratAddress != ‘‘">and o.REGISTRAT_ADDRESS = #{registratAddress}</if>
<if test="pullCarTime != null and pullCarTime != ‘‘">and o.PULL_CAR_TIME = #{pullCarTime}</if>
<if test="remark != null and remark != ‘‘">and o.REMARK = #{remark}</if>
<if test="createTime != null and createTime != ‘‘">and o.CREATE_TIME = #{createTime}</if>
<if test="payTime != null and payTime != ‘‘">and o.PAY_TIME = #{payTime}</if>
<if test="state != null and state != ‘‘">and o.STATE = #{state}</if>
<if test="orderAmt != null and orderAmt != ‘‘">and o.ORDER_AMT = #{orderAmt}</if>
<if test="orderType != null and orderType != ‘‘">and o.ORDER_TYPE= #{orderType}</if>
<if test="buyName != null and buyName != ‘‘">and w.NAME like CONCAT(‘%‘,#{buyName},‘%‘)</if>
<if test="buyMobile != null and buyMobile != ‘‘">and w.MOBILE like CONCAT(‘%‘,#{buyMobile},‘%‘)</if>
<if test="startTime !=null and startTime !=‘‘">and o.CREATE_TIME >#{startTime}</if>
<if test="endTime !=null and endTime !=‘‘">and o.CREATE_TIME <#{endTime}</if>
<if test="orderNumber != null and orderNumber != ‘‘">and o.ORDER_NUMBER like CONCAT(‘%‘,#{orderNumber},‘%‘)</if>
ORDER BY o.CREATE_TIME DESC
limit #{startRow},#{pageSize}
</select>
<select id="findPageTotal" resultType="int" parameterType="map"> SELECT COUNT(1) FROM(SELECT o.*,w.name buyName,w.MOBILE buyMobile,aa.name sellName,aa.MOBILE sellMobile,rs.TRANS_PRICE,scn.price,scs.EXPECT_TRANS_PRICE FROM order_info o INNER JOIN web_user w ON w.id=o.SEARCH_USER_ID LEFT JOIN (SELECT o.SOURCE_USER_ID ,w.name,w.MOBILE mobile,o.Id FROM order_info o INNER JOIN web_user w ON w.id=o.SOURCE_USER_ID) aa ON aa.Id=o.Id LEFT JOIN release_source rs ON o.source_id = rs.id LEFT JOIN search_car_normal scn ON o.SEARCH_normal_ID = scn.id LEFT JOIN search_car_special scs ON o.search_special_ID = scs.id WHERE 1= 1)qq WHERE 1= 1 <if test="id != null and id != ‘‘">and Id = #{id}</if> <if test="sourceId != null and sourceId != ‘‘">and SOURCE_ID = #{sourceId}</if> <if test="sourceUserId != null and sourceUserId != ‘‘">and SOURCE_USER_ID = #{sourceUserId}</if> <if test="searchId != null and searchId != ‘‘">and SEARCH_ID = #{searchId}</if> <if test="searshUserId != null and searshUserId != ‘‘">and SEARCH__USER_ID = #{searshUserId}</if> <if test="num != null and num != ‘‘">and NUM = #{num}</if> <if test="depositPrice != null and depositPrice != ‘‘">and DEPOSIT_PRICE = #{depositPrice}</if> <if test="registratAddress != null and registratAddress != ‘‘">and REGISTRAT_ADDRESS = #{registratAddress}</if> <if test="pullCarTime != null and pullCarTime != ‘‘">and PULL_CAR_TIME = #{pullCarTime}</if> <if test="remark != null and remark != ‘‘">and REMARK = #{remark}</if> <if test="createTime != null and createTime != ‘‘">and CREATE_TIME = #{createTime}</if> <if test="payTime != null and payTime != ‘‘">and PAY_TIME = #{payTime}</if> <if test="buyName != null and buyName != ‘‘">and buyName like CONCAT(‘%‘,#{buyName},‘%‘)</if> <if test="buyMobile != null and buyMobile != ‘‘">and buyMobile like CONCAT(‘%‘,#{buyMobile},‘%‘)</if> <if test="startTime !=null and startTime !=‘‘">and CREATE_TIME >#{startTime}</if> <if test="endTime !=null and endTime !=‘‘">and CREATE_TIME <#{endTime}</if> <if test="state != null and state != ‘‘">and STATE = #{state}</if> <if test="orderAmt != null and orderAmt != ‘‘">and ORDER_AMT = #{orderAmt}</if> <if test="orderType != null and orderType != ‘‘">and ORDER_TYPE=#{orderType}</if> <if test="orderNumber != null and orderNumber != ‘‘">and ORDER_NUMBER like CONCAT(‘%‘,#{orderNumber},‘%‘)</if> </select>
<if test="buyName != null and buyName != ‘‘">and buyName like CONCAT(‘%‘,#{buyName},‘%‘)</if> 注意此处需用别名 不能直接用name 作为条件 否则当页面传值查询时候会报SQL语句查不到相应字段！
页面jsp代码
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%> <%@ taglib uri="/WEB-INF/tld/fmt.tld" prefix="fmt"%> <c:set var="ctx" value="${pageContext.request.contextPath}" /> <!-- ubo --> <!DOCTYPE html> <html lang="zh-CN"> <head> <meta charset="UTF-8"> <title>订单管理</title> <meta content=‘width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no‘ name=‘viewport‘> <link href="${ctx}/resources/css/css.css" rel="stylesheet" type="text/css" /> <!-- Bootstrap 3.3.4 --> <link href="${ctx}/resources/frame/bootstrap/css/bootstrap.min.css" rel="stylesheet" type="text/css" /> <!-- Font Awesome Icons --> <link href="${ctx}/resources/frame/dist/css/font-awesome.min.css" rel="stylesheet" type="text/css" /> <!-- Ionicons --> <link href="${ctx}/resources/frame/dist/css/ionicons.min.css" rel="stylesheet" type="text/css" /> <!-- Theme style --> <link href="${ctx}/resources/frame/dist/css/AdminLTE.min.css" rel="stylesheet" type="text/css" /> <!-- AdminLTE Skins. Choose a skin from the css/skins folder instead of downloading all of them to reduce the load. --> <link href="${ctx}/resources/frame/dist/css/skins/_all-skins.min.css" rel="stylesheet" type="text/css" /> <!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries --> <!-- WARNING: Respond.js doesn‘t work if you view the page via file:// --> <!--[if lt IE 9]> <script src="${ctx}/resources/frame/dist/js/html5shiv.min.js"></script> <script src="${ctx}/resources/frame/dist/js/respond.min.js"></script> <![endif]--> </head> <body class="skin-blue sidebar-mini"> <div class="wrapper"> <%@ include file="../../main_header.jsp"%> <%@ include file="../../main_sidebar.jsp"%> <!-- Content Wrapper. Contains page content --> <div class="content-wrapper"> <!-- Content Header (Page header) --> <section class="content-header"> <h1> 优博车平台 <small>订单管理</small> </h1> </section> <!-- Main content --> <section class="content"> <div class="row"> <div class="col-xs-12"> <div class="box"> <div class="box-header"> <form method="post" id="queryForm" action="${ctx }/orderinfo/list" name="form1"> <div class="box-tools"> <input type="hidden" name="currentPage" id="currentPage" value="${page.currentPage}"> </div> <div class="box-header"> <div class=""> <input type="text" class="form-control input-sm pull-left" style="width: 150px;" placeholder="订单号" name="orderNumber" value="${orderNumber}" maxlength="30"> <input type="text" class="form-control input-sm pull-left" placeholder="买方用户查找" name="buyName" value="${buyName}" style="width: 100px; margin-left:15px;" maxlength="20"> <input type="text" class="form-control input-sm pull-left" placeholder="买方手机号查找" name="buyMobile" value="${buyMobile}" style="width: 130px; margin-left:15px;" maxlength="20"> <select class= "form-control input-sm pull-left" name="orderType" id="orderType" style="width:150px;height:30px; margin-left:20px;"> <option <c:if test="${orderType == ‘0‘}">selected="selected"</c:if> value="">类型</option> <option <c:if test="${orderType == ‘1‘}">selected="selected"</c:if> value="1">销车订单</option> <option <c:if test="${orderType == ‘2‘}">selected="selected"</c:if> value="2">普通寻车订单</option> <option <c:if test="${orderType == ‘3‘}">selected="selected"</c:if> value="3">竞价寻车订单</option> </select> <input type="text" class="form-control input-sm pull-left" style="width: 100px; margin-left:20px;" placeholder="创建起始日期" name="startTime" value="${startTime}" readonly="true" onclick="WdatePicker({dateFmt:‘yyyy-MM-dd‘})"> <input type="text" class="form-control input-sm pull-left" style="width: 100px; margin-left:20px;" placeholder="创建结束日期" name="endTime" value="${endTime}" readonly="true" onclick="WdatePicker({dateFmt:‘yyyy-MM-dd‘})"> <button class="btn btn-sm btn-default" id="queryBtn" style=" margin-left:20px;">查询</button> </div> </div> </form> </div> <!-- /.box-header --> <div class="box-body table-responsive no-padding"> <table class="table table-hover"> <tr> <th class="col-md-1" style="width: 10%">订单号</th> <th class="col-md-1" style="width: 6%">买家姓名</th> <th class="col-md-1" style="width: 8%">买家手机号</th> <th class="col-md-1" style="width: 6%">卖家姓名</th> <th class="col-md-1" style="width: 10%">创建时间</th> <th class="col-md-1" style="width: 8%">订单金额</th> <th class="col-md-1" style="width: 8%">类型</th> <th class="col-md-1" style="width:10%">提车时间</th> <th class="col-md-1" style="width: 8%">状态</th> <th class="col-md-1" style="width: 15%">操作</th> <c:if test="${page.items != null}"> <c:forEach items="${page.items}" var="orderinfo"> <tr> <td>${orderinfo.orderNumber }</td> <td>${orderinfo.buyName }</td> <td>${orderinfo.buyMobile }</td> <td>${orderinfo.sellName }</td> <td><fmt:formatDate value="${orderinfo.createTime}" pattern="yyyy.MM.dd HH:mm:ss" /></td> <td>${orderinfo.orderAmt }万元</td> <td><c:if test="${orderinfo.orderType==‘1‘}">销车订单 </c:if> <c:if test="${orderinfo.orderType==‘2‘}">普通寻车订单 </c:if> <c:if test="${orderinfo.orderType==‘3‘}">竞价寻车订单 </c:if></td> <td><fmt:formatDate value="${orderinfo.pullCarTime}" pattern="yyyy.MM.dd HH:mm:ss" /></td> <td><c:if test="${orderinfo.state==‘1‘}">待支付 </c:if> <c:if test="${orderinfo.state==‘2‘}">已冻结订金 </c:if> <c:if test="${orderinfo.state==‘3‘}">已支付订金 </c:if> <c:if test="${orderinfo.state==‘4‘}">已完成 </c:if> <c:if test="${orderinfo.state==‘5‘}">已取消 </c:if> <c:if test="${orderinfo.state==‘6‘}">已删除 </c:if> <c:if test="${orderinfo.state==‘7‘}"><b style="color:red;">纠纷处理中</b> </c:if> <c:if test="${orderinfo.state==‘8‘}"><b style="color:red;">纠纷处理成功</b> </c:if></td> <td><a href="${ctx}/orderinfo/detail?id=${orderinfo.id }" class="btn btn-sm btn-primary detailBtn">详细</a> <c:if test="${orderinfo.state==‘7‘}"><b style="color:red;"><a href="${ctx}/orderinfo/edit?id=${orderinfo.id }" class="btn btn-sm btn-primary detailBtn">违约处理</a></b> </c:if> </td> </tr> </c:forEach> </c:if> <c:if test="${empty page.items}"> <tr> <td colspan="10"><p class="text-center text-danger">暂无数据</p></td> </tr> </c:if> </table> </div> <!-- /.box-body --> <<span style="color: 本文系统来源：http://www.cnblogs.com/xieCong/p/6701489.html
内容总结
以上是互联网集市为您收集整理的关于分页SQL的小总结全部内容，希望文章能够帮你解决关于分页SQL的小总结所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】