一对多复杂sql查询 

private Integer id;
	private Integer provinceId;
	private Integer month;
	private Integer industryInvest;
	private Integer industryIncome;
	private Integer industryProfits;//工业利润
	private Double industryProfitsMargin;//工业利润率
	private Double industryBiZhong;//工业收入比重
	private Integer eConsumer;
	private Integer agInvest;
	private Integer agIncome;
	private Integer agProfits;//农业利润
	private Double agProfitsMargin;//农业利润率
	
	//省份
	private Province pro;
	private Integer pid;
	private String name;
	private Integer pnum;
	
	//查询条件
	private Integer pnumMin;//最小人数
	private Integer pnumMax;//最大人数
	
	private Integer profitsMin;//最小总利润率
	private Integer profitsMax;//最大总利润率
	
	private Integer industryInvestMin;//最小工业投入
	private Integer industryInvestMax;//最大工业投入
	
	private Integer agIncomeMin;//最小农业收入
	private Integer agIncomeMax;//最大农业收入
	
	private Integer monthMin;//最小月份
	private Integer monthMax;//最大月份
	
	private Integer yearMin;//最小全年收入
	private Integer yearMax;//最大全年收入

　　

private Integer pid;
	private String name;
	private Integer pnum;

　　

PageInfo<Gdp> findAll(Gdp gdp,Integer pageNum,Integer pageSize);

　　

List<Gdp> findAll(Gdp gdp);

　　

@Service
public class GdpServiceImpl implements GdpService{
	@Autowired
	GdpDao gdpDao;
	
	public PageInfo<Gdp> findAll(Gdp gdp, Integer pageNum, Integer pageSize) {
		PageHelper.startPage(pageNum, pageSize);
		List<Gdp> list = gdpDao.findAll(gdp);
		return new PageInfo<Gdp>(list);
	}

}

　　

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <!-- 命名空间的值为dao层接口的权限定名 -->
 6 <mapper namespace="com.bawei.dao.GdpDao" >
 7     
 8     <select id="findAll" resultMap="maps">
 9         select *,(g.industryIncome-g.industryInvest) as industryProfits, 
10             ((g.industryIncome-g.industryInvest)/g.industryIncome*100) as industryProfitsMargin,
11             (g.agIncome-g.agInvest) as agProfits,
12             ((g.agIncome-g.agInvest)/g.agIncome*100) as agProfitsMargin,
13             (g.industryIncome/(g.industryIncome+g.agIncome)*100) as industryBiZhong
14             from tb_gdp g LEFT JOIN tb_province p ON g.provinceId=p.pid
15             <where>
16                 <if test="provinceId!=null and provinceId!=0 ">
17                     and provinceId=#{provinceId}
18                 </if>
19                 <if test="pnumMin!=null ">
20                     and pnum &gt;= #{pnumMin}
21                 </if>
22                 <if test="pnumMax!=null ">
23                     and pnum &lt;= #{pnumMax}
24                 </if>
25                 <if test="profitsMin!=null ">
26                     and ((g.agIncome+g.industryIncome-g.agInvest-g.industryInvest)/(g.agIncome+g.industryIncome)*100) &gt;= #{profitsMin}
27                 </if>
28                 <if test="profitsMax!=null ">
29                     and ((g.agIncome+g.industryIncome-g.agInvest-g.industryInvest)/(g.agIncome+g.industryIncome)*100) &gt;= #{profitsMax}
30                 </if>
31                 <if test="industryInvestMin!=null ">
32                     and g.industryInvest &gt;= #{industryInvestMin}
33                 </if>
34                 <if test="industryInvestMax!=null ">
35                     and g.industryInvest &lt;= #{industryInvestMax}
36                 </if>
37                 <if test="agIncomeMin!=null ">
38                     and g.agIncome &gt;= #{agIncomeMin}
39                 </if>
40                 <if test="agIncomeMax!=null ">
41                     and g.agIncome &lt;= #{agIncomeMax}
42                 </if>
43                 <if test="monthMin!=null ">
44                     and g.month &gt;= #{monthMin}
45                 </if>
46                 <if test="monthMax!=null ">
47                     and g.month &lt;= #{monthMax}
48                 </if>            
49             </where>
50          GROUP BY g.id
51              <trim prefix="having" prefixOverrides="and" >
52                  <if test="yearMin!=null ">
53                     and SUM(g.agIncome+g.industryIncome) &gt;= #{yearMin}
54                 </if>
55                 <if test="yearMax!=null ">
56                     and SUM(g.agIncome+g.industryIncome) &lt;= #{yearMax}
57                 </if>
58              </trim>
59     </select>
60     
61     <resultMap type="Gdp" id="maps">
62         <id property="id" column="id" />
63         <result property="provinceId" column="provinceId" />
64         <result property="month" column="month" />
65         <result property="industryInvest" column="industryInvest" />
66         <result property="industryIncome" column="industryIncome" />
67         <result property="eConsumer" column="eConsumer" />
68         <result property="agInvest" column="agInvest" />
69         <result property="agIncome" column="agIncome" />
70         
71         <result property="industryProfits" column="industryProfits" />
72         <result property="industryProfitsMargin" column="industryProfitsMargin" />
73         <result property="agProfits" column="agProfits" />
74         <result property="agProfitsMargin" column="agProfitsMargin" />
75         <result property="industryBiZhong" column="industryBiZhong" />
76         
77         <association property="pro" javaType="Province">
78             <id property="pid" column="pid" />
79             <result property="name" column="name" />
80             <result property="pnum" column="pnum" />
81         </association>
82     </resultMap>
83     
84     
85     
86     
87 </mapper>
@Controller
public class GdpController {
	@Reference
	GdpService gdpService;
	
	@RequestMapping("findAll")
	public String findAll(Model model,Gdp gdp,@RequestParam(defaultValue = "1")Integer pageNum,@RequestParam(defaultValue = "5")Integer pageSize) {
		PageInfo<Gdp> info = gdpService.findAll(gdp, pageNum, pageSize);
		System.err.println(info);
		model.addAttribute("info", info);
		model.addAttribute("gdp", gdp);
		return "list";
	}
	
}

　　

 1 <%@ page language="java" contentType="text/html; charset=utf-8"
 2     pageEncoding="utf-8"%>
 3     <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
 4 <!DOCTYPE html>
 5 <html>
 6 <head>
 7 <meta charset="utf-8">
 8 <title>Insert title here</title>
 9 <link href="${pageContext.request.contextPath}/resource/bootstrap4/css/bootstrap.css" rel="stylesheet" >
10 <script type="text/javascript" src="${pageContext.request.contextPath}/resource/jquery/jquery-3.4.1.js"></script>
11 <script type="text/javascript" src="${pageContext.request.contextPath}/resource/bootstrap4/js/bootstrap.min.js"></script>
12 </head>
13 <body>
14 
15 <form action="findAll" method="post">
16     省份:<select name="provinceId">
17             <option value="0">请选择</option>
18             <option ${gdp.provinceId==1?"selected":"" } value="1">辽宁</option>
19             <option ${gdp.provinceId==2?"selected":"" } value="2">山西</option>
20             <option ${gdp.provinceId==3?"selected":"" } value="3">河南</option>
21             <option ${gdp.provinceId==4?"selected":"" } value="4">吉林</option>
22             <option ${gdp.provinceId==5?"selected":"" } value="5">黑龙江</option>
23         </select>
24     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
25     人口数量:<input type="text" name="pnumMin" value="${gdp.pnumMin }" >--<input type="text" name="pnumMax" value="${gdp.pnumMax }" >
26     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
27     总利润率:<input type="text" name="profitsMin" value="${gdp.profitsMin }" >--<input type="text" name="profitsMax" value="${gdp.profitsMax }" >
28     <br>
29     工业投入:<input type="text" name="industryInvestMin" value="${gdp.industryInvestMin }" >--<input type="text" name="industryInvestMax" value="${gdp.industryInvestMax }" >
30     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
31     农业收入:<input type="text" name="agIncomeMin" value="${gdp.agIncomeMin }" >--<input type="text" name="agIncomeMax" value="${gdp.agIncomeMax }" >
32     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
33     月份:<input type="text" name="monthMin" value="${gdp.monthMin }" >--<input type="text" name="monthMax" value="${gdp.monthMax }" >
34     <br>
35     整月收入:<input type="text" name="yearMin" value="${gdp.yearMin }" >--<input type="text" name="yearMax" value="${gdp.yearMax }" >
36     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
37     <input type="submit" value="查询" >
38     
39 </form>
40 
41 <table class="table">
42     <tr>
43         <td>全选</td>
44         <td>省份</td>
45         <td>月份</td>
46         <td>人口数量</td>
47         <td>工业收入</td>
48         <td>工业投入</td>
49         <td>工业利润</td>
50         <td>工业利润率</td>
51         <td>耗电量</td>
52         <td>农业投入</td>
53         <td>农业收入</td>
54         <td>农业利润</td>
55         <td>农业利润率</td>
56         <td>工业收入比重</td>
57         <td>操作</td>
58     </tr>
59     <c:forEach items="${info.list }" var="g">
60         <tr>
61             <td></td>
62             <td>${g.pro.name }</td>
63             <td>${g.month }</td>
64             <td>${g.pro.pnum }</td>
65             <td>${g.industryIncome }</td>
66             <td>${g.industryInvest }</td>
67             <td>${g.industryProfits }</td>
68             <td>${g.industryProfitsMargin }%</td>
69             <td>${g.eConsumer }</td>
70             <td>${g.agInvest }</td>
71             <td>${g.agIncome }</td>
72             <td>${g.agProfits }</td>
73             <td>${g.agProfitsMargin }%</td>
74             <td>${g.industryBiZhong }%</td>
75             <td><a>修改</a></td>
76             
77         </tr>
78     </c:forEach>
79 </table>
80 <a href="findAll?pageNum=${info.pageNum==1?info.pageNum:info.pageNum-1 }">上一页</a>
81 <a href="findAll?pageNum=${info.pageNum==info.pages?info.pageNum:info.pageNum+1 }">下一页</a>
82 </body>
83 </html>

 

posted @ 2020-05-12 18:54  Tangjt  阅读(186)  评论(0)    收藏  举报