mybatis关联表多字段排序问题？

Question

1、列表分页排序，前端使用dataTable，排序时有5列，有3列是p表的基本字段，可以直接判断排序，1列是p表字段为parent_id的值不为0的数量来排序的，还有1列是根据关联表（o表）的字段（style）的值为1的数量来排序的。
2、我之前先查询出p表的基本字段排序分页，把count（p.parent_id）和count（o.style）分别查询，然后set到已经查询出来的p的排序分页列表中，count（p.parent_id）和count（o.style）排序出错，又换了一种方法，直接一条sql语句查询出来还是会出现问题，不知该如何解决
3、sql语句

    SELECT p.id,u.username,p.create_time, COUNT(p.parent_id),COUNT(o.style),
    p.likes,p.reward,p.end
    FROM p
    LEFT JOIN u ON p.user_id = u.id 
LEFT JOIN o ON p.id=o.post_id AND o.style=1
    <where>
        p.status=#{status}
        <if test="keyword != null and keyword != ''">
            AND (p.id LIKE CONCAT('%', #{keyword,jdbcType=VARCHAR}, '%') OR u.username LIKE
            CONCAT('%',#{keyword,jdbcType=VARCHAR}, '%'))
        </if>
    </where>
    GROUP BY p.id
    <choose>
        <when test="sortField != null and sortField != '' and sortField == 'parentId' ">
            order by COUNT(p.parent_id)
        </when>
        <when test="sortField != null and sortField != '' and sortField == 'likes' ">
            order by p.likes
        </when>
        <when test="sortField != null and sortField != '' and sortField == 'style' ">
            order by COUNT(o.style)
        </when>
        <when test="sortField != null and sortField != '' and sortField == 'reward' ">
            order by p.reward
        </when>
        <otherwise>
            order by p.create_time
        </otherwise>
    </choose>
    <choose>
        <when test="sortType != null and sortType != '' and sortType == 'asc' ">
            ASC
        </when>
        <otherwise>
            DESC
        </otherwise>
    </choose>
    LIMIT #{start},#{size}

4、按count(o.style=1)升序排序并没有效果

Answer 1

1、使用case when then 的方式进行判断

SELECT p.id,u.username,,p.create_time,case when p.parent_id != 0 then COUNT(p.parent_id) else 0 end AS parent_num,
            case when o.style != 1 then 0 else COUNT(o.style) end AS style_num,
    p.likes,p.reward,p.end
    FROM p
    LEFT JOIN u ON p.user_id = u.id
            LEFT JOIN o ON p.id=o.post_id
            <where>
    p.status=#{status}
    <if test="keyword != null and keyword != ''">
        AND (p.id LIKE CONCAT('%', #{keyword,jdbcType=VARCHAR}, '%') OR u.username LIKE
        CONCAT('%',#{keyword,jdbcType=VARCHAR}, '%'))
    </if>
</where>
GROUP BY p.id
<choose>
    <when test="sortField != null and sortField != '' and sortField == 'parentId' ">
        order by COUNT(p.parent_id)
    </when>
    <when test="sortField != null and sortField != '' and sortField == 'likes' ">
        order by p.likes
    </when>
    <when test="sortField != null and sortField != '' and sortField == 'style' ">
        order by COUNT(o.style)
    </when>
    <when test="sortField != null and sortField != '' and sortField == 'reward' ">
        order by p.reward
    </when>
    <otherwise>
        order by p.create_time
    </otherwise>
</choose>
<choose>
    <when test="sortType != null and sortType != '' and sortType == 'asc' ">
        ASC
    </when>
    <otherwise>
        DESC
    </otherwise>
</choose>
LIMIT #{start},#{size}