数据库表级联查询

所谓的级联查询也就是通过一个uid 将两个表进行链接起来，你可以获取另一个表中的字段数据。

下面列一个例子和并讲解使用：

用的是mybatis框架。

<?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">
<mapper namespace="com.vanvalt.payhui.modules.pay.dao.CreditLogDao">
    
    <sql id="cols">
    	l.tid AS "tid",
    	l.id AS "id",
    	l.create_by AS "createBy",
    	l.create_date AS "createDate",
    	l.update_by AS "updateBy",
    	l.update_date AS "updateDate",
    	l.user_id AS "userId",
    	l.credit_rule_id AS "creditRuleId",
    	l.type AS "type",
    	l.credit AS "credit",
    	l.experience AS "experience",
    	l.credit_time AS "creditTime",
    	l.remarks AS "remarks",
    	l.del_flag AS "delFlag",
    	u.system_id AS "user.systemId",<!-- 注意 这里在前台的调用 -->
    	u.name AS "user.name",
    	u.phone AS "user.phone",
    	r.code AS "creditRule.code",
    	r.name AS "creditRule.name"   	
    </sql>
    
    <sql id="creditLogJoins">
		LEFT JOIN sys_user c ON c.id = l.create_by
		JOIN sys_user u ON u.id = l.user_id
		JOIN credit_rule r ON r.id = l.credit_rule_id
	</sql>
	
	<select id="findList" resultType="CreditLog">
		SELECT 
		<include refid="cols"/> 
		FROM credit_log l
		<include refid="creditLogJoins"/>
		WHERE l.del_flag = #{DEL_FLAG_NORMAL} 
		<if test="userId != null and userId != ''">
			AND l.user_id = #{userId}
		</if>
		<if test="creditRuleId != null and creditRuleId != ''">
			AND l.credit_rule_id = #{creditRuleId}
		</if>
		<if test="type != null and type != ''">
			AND l.type = #{type}
		</if>
		<if test="systemId != null and systemId != ''">
            AND u.system_id = #{systemId}
        </if>
		<if test="phone != null and phone != ''">
			AND u.phone LIKE CONCAT('%', #{phone}, '%')
		</if>
		ORDER BY l.credit_time DESC
	</select>
</mapper>

前台显示数据

使用的时候用的是partner.user.systemId    xml中  u.system_id AS "user.systemId"

发送后台时用的是 systemId    查询 语句中 ：

<if test="systemId != null and systemId != ''">
           AND u.system_id = #{systemId}
 </if>

说明:

这个例子就是通过用户表的id 和 这个合伙人表的 userid 进行链接，通过积分表里面的userid 去查询用户表中的支付ID

链接语句 ：

 <sql id="creditLogJoins">
		LEFT JOIN sys_user c ON c.id = l.create_by
		JOIN sys_user u ON u.id = l.user_id
		JOIN credit_rule r ON r.id = l.credit_rule_id
</sql>

<sql id="cols">
    	l.tid AS "tid",
    	l.id AS "id",
    	l.create_by AS "createBy",
    	l.create_date AS "createDate",
    	l.update_by AS "updateBy",
    	l.update_date AS "updateDate",
    	l.user_id AS "userId",
    	l.credit_rule_id AS "creditRuleId",
    	l.type AS "type",
    	l.credit AS "credit",
    	l.experience AS "experience",
    	l.credit_time AS "creditTime",
    	l.remarks AS "remarks",
    	l.del_flag AS "delFlag",
    	u.system_id AS "user.systemId",
    	u.name AS "user.name",
    	u.phone AS "user.phone",
    	r.code AS "creditRule.code",
    	r.name AS "creditRule.name"   	
    </sql>

	<select id="findList" resultType="CreditLog">
		SELECT 
		<include refid="cols"/> 
		FROM credit_log l
		<include refid="creditLogJoins"/>
		WHERE l.del_flag = #{DEL_FLAG_NORMAL} 
		<if test="userId != null and userId != ''">
			AND l.user_id = #{userId}
		</if>
		<if test="creditRuleId != null and creditRuleId != ''">
			AND l.credit_rule_id = #{creditRuleId}
		</if>
		<if test="type != null and type != ''">
			AND l.type = #{type}
		</if>
		<if test="systemId != null and systemId != ''">
            AND u.system_id = #{systemId}
        </if>
		
		ORDER BY l.credit_time DESC
	</select>

最后

以上就是靓丽时光为你收集整理的数据库表级联查询的全部内容，希望文章能够帮你解决数据库表级联查询所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。