疑难sql记录

   <!-- 通过项目id和权限id查询不相关的用户信息 -->
   <select id="selectRoleNoByUser" resultMap="BaseResultMap">
  		select u.user_id,u.username,u.alias,u.email,u.createtime,u.phone,u.password 
  		from user u
		where u.user_id not iN(
		select
		u.user_id
		from  user u
		LEFT JOIN user_roles t on t.user_id= u.user_id
		where t.proj_id = #{projId,jdbcType=INTEGER} and t.role_id = #{roleId,jdbcType=INTEGER})
	</select>
	 
	 
	<!-- 表名重命名查询验证  -->
	<select id="checkName" parameterType="java.lang.String" resultType="int">
		select count(business_rule_name) from business_rule where business_rule_name = #{businessRuleName,jdbcType=VARCHAR}
	</select>
  
	<!-- 根据tableid查询字段信息  -->
	<select id="selectFieldByTableId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
		select  
		f.field_id  field_id,field_name,field_notes,type,length,primaryKey,notNull
		from  field_manage f
		//t.field_id = f.field_id
		LEFT JOIN table_field t on t.field_id = f.field_id
		//通过tableId查到 t.field_id 和 f.field_id是否相等
		where t.table_id = #{tableId,jdbcType=INTEGER}
	</select>
  
  
	<查询关联信息>
		select
		u.user_id,u.username,u.alias,u.email,u.createtime,u.phone,u.password
		from  user u
		left join user_roles t on t.user_id= u.user_id  
		where t.proj_id = 1
		and t.role_id = 1
		and u.usertype =2
  
	<筛选与机器人相关联的表>
	SELECT count(*) FROM robot_table rt JOIN robot_manage rm ON rt.robot_id = rm.robot_id 
	JOIN table_manage tm ON rt.table_id = tm.table_id WHERE rm.robot_id = ? 
	
	<!-- 根据发起者状态（sponsor），显示机器人推送信息,及机器人姓名（robot_name），机器人ip（robot_ip） -->
	<select id="getRobotBusinessRule" parameterType="int" resultType="java.util.Map">
		select   t.id,t.exchange_from,t.exchange_to,t.exchange_info,t.exchang_status,t.sponsor,r.robot_name,r.robot_ip
		from robot_manage r
		LEFT JOIN  exchange_service t ON t.exchange_to = r.robot_id
		where sponsor = sponsor = #{sponsor,jdbcType=INTEGER}
	</select>
  
	<添加信息的时候可以返回对象主键>
	<insert id="insert" parameterType="platform.tableManage.pojo.TableManage" useGeneratedKeys="true" keyProperty="tableId">
    insert into table_manage (table_id, table_name, table_notes
      )
    values (#{tableId,jdbcType=INTEGER}, #{tableName,jdbcType=VARCHAR}, #{tableNotes,jdbcType=VARCHAR}
      )
	</insert>
	
	<查询三表关联>
	SELECT rm.robot_name,rm.robot_ip,tm.table_id,tm.table_name 
	FROM robot_manage rm 
	LEFT JOIN robot_table rt ON rm.robot_id = rt.robot_id 
	LEFT JOIN table_manage tm ON tm.table_id = rt.table_id	
	WHERE rm.robot_id = 3

	<查询三表关联>
	SELECT tm.table_id,tm.table_name 
	FROM table_manage tm 
	LEFT JOIN robot_table rt ON tm.table_id = rt.table_id
	WHERE rt.table_id  NOT IN (
	SELECT et.table_id
	FROM
	exchang_table et 
	LEFT JOIN exchange_service es ON et.service_id = es.id 
	WHERE es.exchange_from = 3 AND es.exchange_to = 145
	)AND rt.robot_id = 3



	<子查询>
	<将日期格式转化成想要的日期格式>
	子查询+格式化
	select he.area,he.contractno,CONVERT(varchar(100),he.contractnodatetime, 25),he.price,he.site,CONVERT(varchar(100),he.updates, 25),pi.id_cardno,pi.name from HS_exchange he,purchaser_information pi,
	(select * from correlation_chart)cc 
	where cc.active_id = he.active_id AND cc.id_cardno = pi.id_cardno;


	<总数据中，相同数据条数分类汇总（同一条数据出现了几次>
	select carNumber,count(*) from license_management group by carNumber


	<查询时间前几分钟的时间的指定数据>
	select apparent_Power_A from meter3_base where datatime between date_add('2018-09-07 12:38:00', interval - 5 minute) and '2018-09-07 12:38:00';

	<将子查询出来的数据作为表名，查询总条数>
	SELECT count(*) FROM (select distinct XZQHDM from zjbfw)aa WHERE XZQHDM

    <联合删除（两个表）>
DELETE t_sys_xtdmb,t_sys_xtdmlxb FROM t_sys_xtdmb,t_sys_xtdmlxb WHERE t_sys_xtdmb.DMLX='"+pid+"' AND t_sys_xtdmlxb.DMLX='"+pid+"'"

    <子查询2>
select tsxb.YSNF,cc.DMLXMC as DMLX,tsxb.KEYNAME,tsxb.KEYVALUE from t_sys_xtdmb as tsxb,(select * from t_sys_xtdmlxb where DMLXMC='"+search+"')cc where cc.DMLX=tsxb.DMLX


    <查询指定类型，并且字段长度等于3的数据>
SELECT * FROM t_sys_xtdmb where DMLX='PTUSERLX' and length(KEYNAME)=3

    <查询指定类型，并且字段长度大于3并且从左边数起的前三位等于某个值（“”）的数据>

    SELECT * FROM t_sys_xtdmb where DMLX='PTUSERLX' AND length(KEYNAME)>3 AND left(KEYNAME,3)='"+map.get("KEYNAME").toString()+"'"


<多重嵌套查询>
SELECT gf.UNAME,gf.UID,gf.TEL1,gf.ADDRESS,gf.XJ,gf.LXR,uend.KEYVALUE AS SZQY FROM t_sys_xtdmb as uend JOIN
(SELECT  t.UNAME,t.UID,t.TEL1,t.ADDRESS,f.KEYVALUE AS XJ,t.LXR,t.SZQY  FROM t_sys_xtdmb as f JOIN 
(SELECT usr.UNAME,uex.UID,uex.TEL1,uex.ADDRESS,uex.XJ,uex.LXR,uex.SZQY 
FROM t_sys_user as usr,t_sys_user_ex as uex,
(SELECT * FROM t_sys_xtdmb WHERE DMLX = 'PTUSERLX' AND left(KEYNAME,6) = '006001') as mb 
WHERE usr.USERLX = mb.KEYNAME AND usr.UID = uex.UID) t ON t.XJ = f.KEYNAME WHERE f.DMLX= 'DDHYXJSELECT') gf
ON gf.SZQY = uend.KEYNAME AND uend.DMLX = 'DDHYLXSELECT'



jdbc：

	@Override
	public JSONArray selectPendingWorkById(String usrId) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		JSONArray jsonArrayWork = new JSONArray(); 
		String querySql = "select * from WORKPROCESSDEMO where sendeUsrId = ?";
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(querySql);
			pstmt.setString(1, usrId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				WorkProcessDemo workProcessDemoModel = new WorkProcessDemo();
//				json.put("id", rs.getString(1));
//				json.put("levelName", rs.getString(2));
//				json.put("levelColor", rs.getString(3));
//				json.put("levgrade", rs.getString(4));
//				json.put("userlevId", rs.getString(5));
//				json.put("userlevText", rs.getString(6));
//				json.put("levelLimit", rs.getString(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
			JdbcUtil.close(conn);
		}
		return null;
	}