SQL优化

最新推荐文章于 2023-03-01 15:18:31 发布

原创最新推荐文章于 2023-03-01 15:18:31 发布 · 263 阅读

0 ·

CC 4.0 BY-SA版权

CLAA 专栏收录该内容

221 篇文章

订阅专栏

SELECT t.DEVEUI AS deveui, 
		t.DEVTYPE AS devType, 
		d.ORDNO AS ordNo, 
		t.CLAANO AS claaNo, 
		t.DEVSTATUS AS devStatus, 
		t.OPTIME AS opTime, 
		t.REMARK AS remark,
		t.PROJECTID AS projectid,
		t.ADDRESS AS address,
		t.GPSLAT AS gpslat,
		t.GPSLNG AS gpslng,
		t.GPSALT AS gpsalt,
		<!-- t.BOXNO AS boxno, -->
		d.BOXNO AS boxno,
		p.title,
		o.SALECONTRACTNO AS saleContractNo
   FROM osmdb.t_osm_dev t LEFT JOIN t_osm_project_base p ON t.PROJECTID = p.projectid
   LEFT JOIN t_osm_order_dev d ON d.DEVEUI = t.DEVEUI LEFT JOIN t_osm_order_base o ON o.ORDNO = d.ORDNO
  WHERE 1 = 1 
  <if test='p.projectid != null and p.projectid != "" '>
	 AND t.PROJECTID = #{p.projectid, jdbcType=VARCHAR} 
  </if>
  <if test='p.claaNo != 0'>  
	 AND t.CLAANO = #{p.claaNo, jdbcType=INTEGER} 
  </if>
  <if test='p.devType != null and p.devType != "" '>
	 AND t.DEVTYPE = #{p.devType, jdbcType=VARCHAR} 
  </if>
  <if test='p.deveui != null and p.deveui != "" '>
	 AND t.DEVEUI = #{p.deveui, jdbcType=VARCHAR} 
  </if>
  <if test='p.ordNo != null and p.ordNo != "" '> 
	 AND t.DEVEUI IN (SELECT DISTINCT DEVEUI 
						FROM osmdb.t_osm_order_dev 
					   WHERE ORDNO = #{p.ordNo, jdbcType=VARCHAR}) 
  </if>
  <if test='p.devStatus != -1 and p.devStatus != 99 and p.devStatus != -99 '>
	 AND t.DEVSTATUS = #{p.devStatus, jdbcType=INTEGER} 
  </if>
  <if test='p.devStatus == 99'>
	 AND t.DEVSTATUS NOT IN (20,21) 
  </if>
  <!--  -99代表后台重新创建发货清单   -->
  <if test='p.devStatus == -99'>
	 AND t.DEVSTATUS IN (11,13) 
  </if>
  <if test='p.beginDate != null'>
	 AND t.OPTIME &gt;= #{p.beginDate, jdbcType=DATE} 
  </if>
  <if test='p.endDate != null'>
	 AND t.OPTIME &lt;= #{p.endDate, jdbcType=DATE} 
  </if>
  <if test='p.boxnoList != null and p.boxnoList.size()>0'>
	 AND d.BOXNO in  
  <foreach collection="p.boxnoList"  open="("  close=")" separator="," item="boxno">  
		 #{boxno, jdbcType=VARCHAR} 
  </foreach>
  </if>
  <if test='p.projectId != null and p.projectId != "" '>
	 AND t.PROJECTID in (SELECT PROJECTID FROM t_osm_user_project WHERE PROJECTID = #{p.projectId, jdbcType=VARCHAR})
  </if>
  ORDER BY t.OPTIME DESC 
  LIMIT #{p.batch, jdbcType=INTEGER} OFFSET #{p.offset, jdbcType=INTEGER}

SELECT t.DEVEUI AS deveui, 
t.DEVTYPE AS devType, 
d.ORDNO AS ordNo, 
t.CLAANO AS claaNo, 
t.DEVSTATUS AS devStatus, 
t.OPTIME AS opTime, 
t.REMARK AS remark,
t.PROJECTID AS projectid,
t.ADDRESS AS address,
t.GPSLAT AS gpslat,
t.GPSLNG AS gpslng,
t.GPSALT AS gpsalt,
d.BOXNO AS boxno,
p.title,
o.SALECONTRACTNO AS saleContractNo 
FROM (
SELECT * FROM osmdb.t_osm_dev tt 
 WHERE 1 = 1 
  <if test='p.projectid != null and p.projectid != "" '>
	 AND tt.PROJECTID = #{p.projectid, jdbcType=VARCHAR} 
  </if>
  <if test='p.claaNo != 0'>  
	 AND tt.CLAANO = #{p.claaNo, jdbcType=INTEGER} 
  </if>
  <if test='p.devType != null and p.devType != "" '>
	 AND tt.DEVTYPE = #{p.devType, jdbcType=VARCHAR} 
  </if>
  <if test='p.deveui != null and p.deveui != "" '>
	 AND tt.DEVEUI = #{p.deveui, jdbcType=VARCHAR} 
  </if>
  <if test='p.ordNo != null and p.ordNo != "" '> 
	 AND tt.DEVEUI IN (SELECT DISTINCT DEVEUI 
						FROM osmdb.t_osm_order_dev 
					   WHERE ORDNO = #{p.ordNo, jdbcType=VARCHAR}) 
  </if>
  <if test='p.devStatus != -1 and p.devStatus != 99 and p.devStatus != -99 '>
	 AND tt.DEVSTATUS = #{p.devStatus, jdbcType=INTEGER} 
  </if>
  <if test='p.devStatus == 99'>
	 AND tt.DEVSTATUS NOT IN (20,21) 
  </if>
  <!--  -99代表后台重新创建发货清单   -->
  <if test='p.devStatus == -99'>
	 AND tt.DEVSTATUS IN (11,13) 
  </if>
  <if test='p.beginDate != null'>
	 AND tt.OPTIME &gt;= #{p.beginDate, jdbcType=DATE} 
  </if>
  <if test='p.endDate != null'>
	 AND tt.OPTIME &lt;= #{p.endDate, jdbcType=DATE} 
  </if>
  <if test='p.projectId != null and p.projectId != "" '>
	 AND tt.PROJECTID = #{p.projectId, jdbcType=VARCHAR}
  </if>
ORDER BY tt.DEVEUI ASC 
LIMIT #{p.batch, jdbcType=INTEGER} OFFSET #{p.offset, jdbcType=INTEGER}
) AS t 
LEFT JOIN t_osm_project_base p ON t.PROJECTID = p.projectid
LEFT JOIN t_osm_order_base o ON o.ORDNO = t.ORDNO 
LEFT JOIN 
(SELECT a.ORDNO, a.BOXNO, a.DEVEUI FROM t_osm_order_dev a,osmdb.t_osm_dev b 
 WHERE a.DEVEUI = b.DEVEUI 	
  <if test='p.projectid != null and p.projectid != "" '>
	 AND b.PROJECTID = #{p.projectid, jdbcType=VARCHAR} 
  </if>
  <if test='p.claaNo != 0'>  
	 AND b.CLAANO = #{p.claaNo, jdbcType=INTEGER} 
  </if>
  <if test='p.devType != null and p.devType != "" '>
	 AND b.DEVTYPE = #{p.devType, jdbcType=VARCHAR} 
  </if>
  <if test='p.deveui != null and p.deveui != "" '>
	 AND b.DEVEUI = #{p.deveui, jdbcType=VARCHAR} 
  </if>
  <if test='p.ordNo != null and p.ordNo != "" '> 
	 AND b.DEVEUI IN (SELECT DISTINCT DEVEUI 
						FROM osmdb.t_osm_order_dev 
					   WHERE ORDNO = #{p.ordNo, jdbcType=VARCHAR}) 
  </if>
  <if test='p.devStatus != -1 and p.devStatus != 99 and p.devStatus != -99 '>
	 AND b.DEVSTATUS = #{p.devStatus, jdbcType=INTEGER} 
  </if>
  <if test='p.devStatus == 99'>
	 AND b.DEVSTATUS NOT IN (20,21) 
  </if>
  <!--  -99代表后台重新创建发货清单   -->
  <if test='p.devStatus == -99'>
	 AND b.DEVSTATUS IN (11,13) 
  </if>
  <if test='p.beginDate != null'>
	 AND b.OPTIME &gt;= #{p.beginDate, jdbcType=DATE} 
  </if>
  <if test='p.endDate != null'>
	 AND b.OPTIME &lt;= #{p.endDate, jdbcType=DATE} 
  </if>
  <if test='p.projectId != null and p.projectId != "" '>
	 AND b.PROJECTID = #{p.projectId, jdbcType=VARCHAR}
  </if>
  ORDER BY b.DEVEUI ASC 
  LIMIT #{p.batch, jdbcType=INTEGER} OFFSET #{p.offset, jdbcType=INTEGER} 

) AS d ON d.DEVEUI = t.DEVEUI

去掉繁杂的条件看的清楚一点：

 SELECT         t.DEVEUI AS deveui, 
		t.DEVTYPE AS devType, 
		d.ORDNO AS ordNo, 
		t.CLAANO AS claaNo, 
		t.DEVSTATUS AS devStatus, 
		t.OPTIME AS opTime, 
		t.REMARK AS remark,
		t.PROJECTID AS projectid,
		t.ADDRESS AS address,
		t.GPSLAT AS gpslat,
		t.GPSLNG AS gpslng,
		t.GPSALT AS gpsalt,
		d.BOXNO AS boxno,
		p.title,
		o.SALECONTRACTNO AS saleContractNo 
  FROM (SELECT * FROM osmdb.t_osm_dev tt 
	 WHERE 1 = 1 
		  
		ORDER BY tt.DEVEUI ASC 
		LIMIT #{p.batch, jdbcType=INTEGER} OFFSET #{p.offset, jdbcType=INTEGER}
	  ) AS t 
      LEFT JOIN t_osm_project_base p ON t.PROJECTID = p.projectid
      LEFT JOIN t_osm_order_base o ON o.ORDNO = t.ORDNO 
      LEFT JOIN 
	   (SELECT a.ORDNO, a.BOXNO, a.DEVEUI FROM t_osm_order_dev a,osmdb.t_osm_dev b 
	     WHERE a.DEVEUI = b.DEVEUI 	
	     
		  ORDER BY b.DEVEUI ASC 
		  LIMIT #{p.batch, jdbcType=INTEGER} OFFSET #{p.offset, jdbcType=INTEGER} 
		) AS d ON d.DEVEUI = t.DEVEUI