高级 SQL查询,如分组,统计,两个字段的差比运算
//根据人口数量查询
select * from tb_gdp g , tb_province p where g.provinceId=p.id and p.pnum > 10 and p.pnum<200000 and provinceId=1
//计算总利润率
select * from tb_gdp g , tb_province p where g.provinceId=p.id and p.pnum > 10 and p.pnum<200000 and provinceId=1
// 根据计算字段进行范围查询
select *, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
from tb_gdp g , tb_province p
where g.provinceId=p.id and p.pnum > 10 and p.pnum<200000 and provinceId=1
and ((g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100) > 1 and ( (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 )< 100
//使用子查询的方式
select * from ( select g.*, p.`name` ,p.pnum, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
from tb_gdp g , tb_province p
where g.provinceId=p.id ) as myview
where pnum > 10 and pnum<200000 and provinceId=1
and rate > 1 and rate < 100
//根据汇总数据查询明细
select * from ( select g.*, p.`name` ,p.pnum, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
from tb_gdp g , tb_province p
where g.provinceId=p.id ) as myview
根据分组做条件查询明细
select * from ( select g.*, p.`name` ,p.pnum, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
from tb_gdp g , tb_province p
where g.provinceId=p.id ) as myview
WHERE provinceId in (
SELECT g.provinceId from tb_gdp g GROUP BY g.provinceId HAVING sum(g.agIncome + g.industryIncome) > 1000
)
//汇总
select sum(pnum),sum(industryIncome),sum(agInvest) sum_ai, myview.month from ( select g.*, p.`name` ,p.pnum, (g.agIncome + g.industryIncome - g.agInvest - g.industryInvest)/( g.agInvest + g.industryInvest )*100 as rate
from tb_gdp g , tb_province p
where g.provinceId=p.id ) as myview
WHERE myview.month >2 and myview.month < 4
GROUP BY myview.month
having sum_ai>100 and sum_ai<200000
实体类
package com.bawei.pojo;
import java.io.Serializable;
public class Gdp implements Serializable{
/**
*
*/
private static final long serialVersionUID = 6040188117289196519L;
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;//最大全年收入
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getProvinceId() {
return provinceId;
}
public void setProvinceId(Integer provinceId) {
this.provinceId = provinceId;
}
public Integer getMonth() {
return month;
}
public void setMonth(Integer month) {
this.month = month;
}
public Integer getIndustryInvest() {
return industryInvest;
}
public void setIndustryInvest(Integer industryInvest) {
this.industryInvest = industryInvest;
}
public Integer getIndustryIncome() {
return industryIncome;
}
public void setIndustryIncome(Integer industryIncome) {
this.industryIncome = industryIncome;
}
public Double getIndustryProfitsMargin() {
return industryProfitsMargin;
}
public void setIndustryProfitsMargin(Double industryProfitsMargin) {
this.industryProfitsMargin = industryProfitsMargin;
}
public Double getIndustryBiZhong() {
return industryBiZhong;
}
public void setIndustryBiZhong(Double industryBiZhong) {
this.industryBiZhong = industryBiZhong;
}
public Integer geteConsumer() {
return eConsumer;
}
public void seteConsumer(Integer eConsumer) {
this.eConsumer = eConsumer;
}
public Integer getAgInvest() {
return agInvest;
}
public void setAgInvest(Integer agInvest) {
this.agInvest = agInvest;
}
public Integer getAgIncome() {
return agIncome;
}
public void setAgIncome(Integer agIncome) {
this.agIncome = agIncome;
}
public Integer getIndustryProfits() {
return industryProfits;
}
public void setIndustryProfits(Integer industryProfits) {
this.industryProfits = industryProfits;
}
public Integer getAgProfits() {
return agProfits;
}
public void setAgProfits(Integer agProfits) {
this.agProfits = agProfits;
}
public Double getAgProfitsMargin() {
return agProfitsMargin;
}
public void setAgProfitsMargin(Double agProfitsMargin) {
this.agProfitsMargin = agProfitsMargin;
}
public Province getPro() {
return pro;
}
public void setPro(Province pro) {
this.pro = pro;
}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getPnum() {
return pnum;
}
public void setPnum(Integer pnum) {
this.pnum = pnum;
}
public Integer getPnumMin() {
return pnumMin;
}
public void setPnumMin(Integer pnumMin) {
this.pnumMin = pnumMin;
}
public Integer getPnumMax() {
return pnumMax;
}
public void setPnumMax(Integer pnumMax) {
this.pnumMax = pnumMax;
}
public Integer getProfitsMin() {
return profitsMin;
}
public void setProfitsMin(Integer profitsMin) {
this.profitsMin = profitsMin;
}
public Integer getProfitsMax() {
return profitsMax;
}
public void setProfitsMax(Integer profitsMax) {
this.profitsMax = profitsMax;
}
public Integer getIndustryInvestMin() {
return industryInvestMin;
}
public void setIndustryInvestMin(Integer industryInvestMin) {
this.industryInvestMin = industryInvestMin;
}
public Integer getIndustryInvestMax() {
return industryInvestMax;
}
public void setIndustryInvestMax(Integer industryInvestMax) {
this.industryInvestMax = industryInvestMax;
}
public Integer getAgIncomeMin() {
return agIncomeMin;
}
public void setAgIncomeMin(Integer agIncomeMin) {
this.agIncomeMin = agIncomeMin;
}
public Integer getAgIncomeMax() {
return agIncomeMax;
}
public void setAgIncomeMax(Integer agIncomeMax) {
this.agIncomeMax = agIncomeMax;
}
public Integer getMonthMin() {
return monthMin;
}
public void setMonthMin(Integer monthMin) {
this.monthMin = monthMin;
}
public Integer getMonthMax() {
return monthMax;
}
public void setMonthMax(Integer monthMax) {
this.monthMax = monthMax;
}
public Integer getYearMin() {
return yearMin;
}
public void setYearMin(Integer yearMin) {
this.yearMin = yearMin;
}
public Integer getYearMax() {
return yearMax;
}
public void setYearMax(Integer yearMax) {
this.yearMax = yearMax;
}
@Override
public String toString() {
return "Gdp [id=" + id + ", provinceId=" + provinceId + ", month=" + month + ", industryInvest="
+ industryInvest + ", industryIncome=" + industryIncome + ", industryProfits=" + industryProfits
+ ", industryProfitsMargin=" + industryProfitsMargin + ", industryBiZhong=" + industryBiZhong
+ ", eConsumer=" + eConsumer + ", agInvest=" + agInvest + ", agIncome=" + agIncome + ", agProfits="
+ agProfits + ", agProfitsMargin=" + agProfitsMargin + ", pro=" + pro + ", pid=" + pid + ", name="
+ name + ", pnum=" + pnum + ", pnumMin=" + pnumMin + ", pnumMax=" + pnumMax + ", profitsMin="
+ profitsMin + ", profitsMax=" + profitsMax + ", industryInvestMin=" + industryInvestMin
+ ", industryInvestMax=" + industryInvestMax + ", agIncomeMin=" + agIncomeMin + ", agIncomeMax="
+ agIncomeMax + ", monthMin=" + monthMin + ", monthMax=" + monthMax + ", yearMin=" + yearMin
+ ", yearMax=" + yearMax + "]";
}
}
package com.bawei.pojo;
import java.io.Serializable;
public class Province implements Serializable{
/**
*
*/
private static final long serialVersionUID = -4629196124178013745L;
private Integer pid;
private String name;
private Integer pnum;
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getPnum() {
return pnum;
}
public void setPnum(Integer pnum) {
this.pnum = pnum;
}
@Override
public String toString() {
return "Province [pid=" + pid + ", name=" + name + ", pnum=" + pnum + "]";
}
}
service接口
package com.bawei.service;
import com.bawei.pojo.Gdp;
import com.github.pagehelper.PageInfo;
public interface GdpService {
PageInfo<Gdp> findAll(Gdp gdp,Integer pageNum,Integer pageSize);
}
dao接口
package com.bawei.dao;
import java.util.List;
import com.bawei.pojo.Gdp;
public interface GdpDao {
List<Gdp> findAll(Gdp gdp);
}
service实现类
package com.bawei.service.impl;
import java.util.List;
import org.apache.dubbo.config.annotation.Service;
import org.springframework.beans.factory.annotation.Autowired;
import com.bawei.dao.GdpDao;
import com.bawei.pojo.Gdp;
import com.bawei.service.GdpService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
@Service
public class GdpServiceImpl implements GdpService{
@Autowired
GdpDao gdpDao;
@Override
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);
}
}
Mapper
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 命名空间的值为dao层接口的权限定名 -->
<mapper namespace="com.bawei.dao.GdpDao" >
<select id="findAll" resultMap="maps">
select *,(g.industryIncome-g.industryInvest) as industryProfits,
((g.industryIncome-g.industryInvest)/g.industryIncome*100) as industryProfitsMargin,
(g.agIncome-g.agInvest) as agProfits,
((g.agIncome-g.agInvest)/g.agIncome*100) as agProfitsMargin,
(g.industryIncome/(g.industryIncome+g.agIncome)*100) as industryBiZhong
from tb_gdp g LEFT JOIN tb_province p ON g.provinceId=p.pid
<where>
<if test="provinceId!=null and provinceId!=0 ">
and provinceId=#{provinceId}
</if>
<if test="pnumMin!=null ">
and pnum >= #{pnumMin}
</if>
<if test="pnumMax!=null ">
and pnum <= #{pnumMax}
</if>
<if test="profitsMin!=null ">
and ((g.agIncome+g.industryIncome-g.agInvest-g.industryInvest)/(g.agIncome+g.industryIncome)*100) >= #{profitsMin}
</if>
<if test="profitsMax!=null ">
and ((g.agIncome+g.industryIncome-g.agInvest-g.industryInvest)/(g.agIncome+g.industryIncome)*100) >= #{profitsMax}
</if>
<if test="industryInvestMin!=null ">
and g.industryInvest >= #{industryInvestMin}
</if>
<if test="industryInvestMax!=null ">
and g.industryInvest <= #{industryInvestMax}
</if>
<if test="agIncomeMin!=null ">
and g.agIncome >= #{agIncomeMin}
</if>
<if test="agIncomeMax!=null ">
and g.agIncome <= #{agIncomeMax}
</if>
<if test="monthMin!=null ">
and g.month >= #{monthMin}
</if>
<if test="monthMax!=null ">
and g.month <= #{monthMax}
</if>
</where>
GROUP BY g.id
<trim prefix="having" prefixOverrides="and" >
<if test="yearMin!=null ">
and SUM(g.agIncome+g.industryIncome) >= #{yearMin}
</if>
<if test="yearMax!=null ">
and SUM(g.agIncome+g.industryIncome) <= #{yearMax}
</if>
</trim>
</select>
<resultMap type="Gdp" id="maps">
<id property="id" column="id" />
<result property="provinceId" column="provinceId" />
<result property="month" column="month" />
<result property="industryInvest" column="industryInvest" />
<result property="industryIncome" column="industryIncome" />
<result property="eConsumer" column="eConsumer" />
<result property="agInvest" column="agInvest" />
<result property="agIncome" column="agIncome" />
<result property="industryProfits" column="industryProfits" />
<result property="industryProfitsMargin" column="industryProfitsMargin" />
<result property="agProfits" column="agProfits" />
<result property="agProfitsMargin" column="agProfitsMargin" />
<result property="industryBiZhong" column="industryBiZhong" />
<association property="pro" javaType="Province">
<id property="pid" column="pid" />
<result property="name" column="name" />
<result property="pnum" column="pnum" />
</association>
</resultMap>
</mapper>
Controller
package com.bawei.controller;
import org.apache.dubbo.config.annotation.Reference;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import com.bawei.pojo.Gdp;
import com.bawei.service.GdpService;
import com.github.pagehelper.PageInfo;
@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";
}
}
前台jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert title here</title>
<link href="${pageContext.request.contextPath}/resource/bootstrap4/css/bootstrap.css" rel="stylesheet" >
<script type="text/javascript" src="${pageContext.request.contextPath}/resource/jquery/jquery-3.4.1.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/resource/bootstrap4/js/bootstrap.min.js"></script>
</head>
<body>
<form action="findAll" method="post">
省份:<select name="provinceId">
<option value="0">请选择</option>
<option ${gdp.provinceId==1?"selected":"" } value="1">辽宁</option>
<option ${gdp.provinceId==2?"selected":"" } value="2">山西</option>
<option ${gdp.provinceId==3?"selected":"" } value="3">河南</option>
<option ${gdp.provinceId==4?"selected":"" } value="4">吉林</option>
<option ${gdp.provinceId==5?"selected":"" } value="5">黑龙江</option>
</select>
人口数量:<input type="text" name="pnumMin" value="${gdp.pnumMin }" >--<input type="text" name="pnumMax" value="${gdp.pnumMax }" >
总利润率:<input type="text" name="profitsMin" value="${gdp.profitsMin }" >--<input type="text" name="profitsMax" value="${gdp.profitsMax }" >
<br>
工业投入:<input type="text" name="industryInvestMin" value="${gdp.industryInvestMin }" >--<input type="text" name="industryInvestMax" value="${gdp.industryInvestMax }" >
农业收入:<input type="text" name="agIncomeMin" value="${gdp.agIncomeMin }" >--<input type="text" name="agIncomeMax" value="${gdp.agIncomeMax }" >
月份:<input type="text" name="monthMin" value="${gdp.monthMin }" >--<input type="text" name="monthMax" value="${gdp.monthMax }" >
<br>
整月收入:<input type="text" name="yearMin" value="${gdp.yearMin }" >--<input type="text" name="yearMax" value="${gdp.yearMax }" >
<input type="submit" value="查询" >
</form>
<table class="table">
<tr>
<td>全选</td>
<td>省份</td>
<td>月份</td>
<td>人口数量</td>
<td>工业收入</td>
<td>工业投入</td>
<td>工业利润</td>
<td>工业利润率</td>
<td>耗电量</td>
<td>农业投入</td>
<td>农业收入</td>
<td>农业利润</td>
<td>农业利润率</td>
<td>工业收入比重</td>
<td>操作</td>
</tr>
<c:forEach items="${info.list }" var="g">
<tr>
<td></td>
<td>${g.pro.name }</td>
<td>${g.month }</td>
<td>${g.pro.pnum }</td>
<td>${g.industryIncome }</td>
<td>${g.industryInvest }</td>
<td>${g.industryProfits }</td>
<td>${g.industryProfitsMargin }%</td>
<td>${g.eConsumer }</td>
<td>${g.agInvest }</td>
<td>${g.agIncome }</td>
<td>${g.agProfits }</td>
<td>${g.agProfitsMargin }%</td>
<td>${g.industryBiZhong }%</td>
<td><a>修改</a></td>
</tr>
</c:forEach>
</table>
<a href="findAll?pageNum=${info.pageNum==1?info.pageNum:info.pageNum-1 }">上一页</a>
<a href="findAll?pageNum=${info.pageNum==info.pages?info.pageNum:info.pageNum+1 }">下一页</a>
</body>
</html>