mybatis關(guān)聯(lián)表多字段排序問題？

1、列表分頁排序，前端使用dataTable，排序時有5列，有3列是p表的基本字段，可以直接判斷排序，1列是p表字段為parent_id的值不為0的數(shù)量來排序的，還有1列是根據(jù)關(guān)聯(lián)表（o表）的字段（style）的值為1的數(shù)量來排序的。
2、我之前先查詢出p表的基本字段排序分頁，把count（p.parent_id）和count（o.style）分別查詢，然后set到已經(jīng)查詢出來的p的排序分頁列表中，count（p.parent_id）和count（o.style）排序出錯，又換了一種方法，直接一條sql語句查詢出來還是會出現(xiàn)問題，不知該如何解決
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)升序排序并沒有效果

回答

編輯回答

舊螢火

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}

2018年3月7日 07:00