MySQL多表连接查询，一对多

Question

有4个表，

attr表

字段名	类型
id	BIGINT
name	VARCHAR
amount	BIGINT

prop表

字段名	类型
id	BIGINT
name	VARCHAR
status	BIGINT

prop_attr表

字段名	类型
id	BIGINT
prop_id	BIGINT
attr_id	BIGINT

commodity表

字段名	类型
id	BIGINT
prop_id	BIGINT
name	VARCHAR
price	DOUBLE

这几个表，一个prop可以关联多个attr数据，
使用prop_attr表来关联，是1对多，一个commodity只能关联一个prop，现在要实现以下查询功能：

查询 commodity表中的数据，但是 因为prop可以关联多个attr,所以 希望一条commodity中的数据能带上全部的attr数据，mysql查询语句么写

Answer 1

好吧，用mybatis解决了

 <resultMap id="BaseResultMap" type="com.niuwan.mall.model.Commodity">

        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="state" jdbcType="INTEGER" property="state"/>
        <result column="status" jdbcType="INTEGER" property="status"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="prop_id" jdbcType="BIGINT" property="propId"/>
        <result column="classify_type" jdbcType="INTEGER" property="classifyType"/>
        <result column="price" jdbcType="DOUBLE" property="price"/>
        <result column="amount" jdbcType="BIGINT" property="amount"/>
        <result column="fpath" jdbcType="VARCHAR" property="fpath"/>
        <result column="explain" jdbcType="VARCHAR" property="explain"/>
        <result column="user_id" jdbcType="BIGINT" property="userId"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="modify_time" jdbcType="TIMESTAMP" property="modifyTime"/>

        <collection property="attributeList" column="attribute" ofType="com.niuwan.mall.model.Attribute">
            <id column="attr_id" jdbcType="BIGINT" property="id"/>
            <result column="attr_name" jdbcType="VARCHAR" property="attrName"/>
            <result column="attr_type" jdbcType="BIGINT" property="attrType"/>
            <result column="attr_amount" jdbcType="BIGINT" property="attrAmount"/>
                    <!--select="com.foo.bean.PostMapper.selectByBlogId"-->
        </collection>
    </resultMap>

        <select id="selectCommodityList" resultMap="BaseResultMap" parameterType="Map">
        SELECT c.*,a.id AS attr_id,a.attr_name AS attr_name,a.attr_amount AS attr_amount,a.attr_type AS attr_type

        FROM commodity AS c
        LEFT JOIN prop_attribute AS pr ON c.prop_id = pr.prop_id
        LEFT JOIN attribute AS a ON pr.attr_id = a.id
        WHERE 1=1
        <if test="status != null">
            AND c.status = #{status}
        </if>

        <if test="name != null">
            AND c.name like #{name}
        </if>

        <if test="orderByClause != null">
            order by ${orderByClause}
        </if>

        limit ${offset}, ${limit}

    </select>

bean中定义

 private List<Attribute> attributeList;

这样就能 查询一个commodity的时候查询多个attr了