个人随笔之分页查询的sql语句

三表联合分页查询（显示分页和按条件查询）

select id="getCookMenuPageList" parameterType="com.v2dawn.diet.param.CookMenuPageParam" resultType="com.v2dawn.diet.vo.CookMenuQueryVo">
        select
        cm.*,su.nickname as cookName
        from cook_menu cm
        left join sys_user su on su.id = cm.cook_id
        <trim prefix="where" prefixOverrides="AND">
            <if test="param.cookId !=null">
                and cm.cook_id = #{param.cookId}
            </if>
            <if test="param.foodId !=null">
                and cm.food_id = #{param.foodId}
            </if>
            <if test="param.foodName !=null and param.foodName !=''">
                and cm.food_name like concat('%',#{param.foodName},'%')
            </if>
            <if test="param.cookName !=null and param.cookName !=''">
                and su.nickname like concat('%',#{param.cookName},'%')
            </if>
            <if test="param.type !=null">
                and cm.type  = #{param.type}
            </if>
            <if test="param.cookMethod !=null and param.cookMethod !=''">
                and cm.cook_method like concat('%',#{param.cookMethod},'%')
            </if>
        </trim>
    </select>

根据ID两表联合查询详情

<select id="getSingleById" parameterType="Long" resultType="com.v2dawn.diet.vo.CookMenuQueryVo">
        select
        cm.*, su.nickname as cookName, su.id as cook
        from cook_menu cm
        left join sys_user su on su.id = cm.cook_id
        where cm.id = #{id}
    </select>