上个月写的关于论坛帖子值得学习的SQL语句(仅供自己回顾)

--我参与的
SELECT DISTINCT
    (forum.f01) AS foroumId,
    forum.f06 AS titleType,
    user.f04 AS headPic,
    IFNULL(user.f03, '匿名') AS postNickName,
    forum.f09 AS readNum,
    forum.f10 AS commentNum,
    forum.f03 AS title,
    (SELECT 
            reply.f04
        FROM
            s90.t9031 reply
        WHERE
            F02 = forum.f01 AND reply.f03 = 1807
        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
        LIMIT 0 , 1) AS ReplyContent,
    (SELECT 
            reply.f06
        FROM
            s90.t9031 reply
        WHERE
            F02 = forum.f01 AND reply.f03 = 1807
        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
        LIMIT 0 , 1) AS replyPraiseNum,
    (SELECT 
            reply.f07
        FROM
            s90.t9031 reply
        WHERE
            F02 = forum.f01
        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
        LIMIT 0 , 1) AS noName
FROM
    s90.t9031 reply
        LEFT JOIN
    s90.t9030 forum ON reply.f02 = forum.f01
        LEFT JOIN
    s90.t9011 user ON forum.f02 = user.f01
WHERE
    forum.f11 = 'y' AND forum.f01 IS NOT NULL
        AND reply.f03 = 1807
ORDER BY reply.f05 DESC;

-- 我发起的
SELECT DISTINCT
    (forum.f01) AS foroumId,
    user.f04 AS headPic,
    IFNULL(user.f03, '匿名') AS postNickName,
    forum.f06 AS titleType,
    forum.f09 AS readNum,
    forum.f10 AS commentNum,
    forum.f03 AS title,
    (SELECT 
            reply.f04
        FROM
            s90.t9031 reply
        WHERE
            F02 = forum.f01
        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
        LIMIT 0 , 1) AS ReplyContent,
    (SELECT 
            reply.f06
        FROM
            s90.t9031 reply
        WHERE
            F02 = forum.f01
        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
        LIMIT 0 , 1) AS replyPraiseNum,
    (SELECT 
            reply.f07
        FROM
            s90.t9031 reply
        WHERE
            F02 = forum.f01
        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
        LIMIT 0 , 1) AS noName,
    IFNULL((SELECT 
                    u.f03
                FROM
                    s90.t9011 u
                WHERE
                    u.f01 = (SELECT 
                            reply.f03
                        FROM
                            s90.t9031 reply
                        WHERE
                            F02 = forum.f01
                        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
                        LIMIT 0 , 1)),
            '匿名') AS replyNickName
FROM
    s90.t9030 forum
        LEFT JOIN
    s90.t9031 reply ON forum.f01 = reply.f02
        LEFT JOIN
    s90.t9011 user ON forum.f02 = user.f01
WHERE
    forum.f11 = 'Y' AND forum.f02 = 1807
ORDER BY forum.f07 DESC

--我的收藏
SELECT 
    user.f04 AS headPic,
    IFNULL(user.f03, '匿名') AS postNickName,
    forum.f01 AS foroumId,
    forum.f06 AS titleType,
    forum.f09 AS readNum,
    forum.f10 AS commentNum,
    forum.f03 AS title,
    (SELECT 
            reply.f04
        FROM
            s90.t9031 reply
        WHERE
            F02 = forum.f01
        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
        LIMIT 0 , 1) AS ReplyContent,
    (SELECT 
            reply.f06
        FROM
            s90.t9031 reply
        WHERE
            F02 = forum.f01
        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
        LIMIT 0 , 1) AS replyPraiseNum,
    (SELECT 
            reply.f07
        FROM
            s90.t9031 reply
        WHERE
            F02 = forum.f01
        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
        LIMIT 0 , 1) AS noName,
    IFNULL((SELECT 
                    u.f03
                FROM
                    s90.t9011 u
                WHERE
                    u.f01 = (SELECT 
                            reply.f03
                        FROM
                            s90.t9031 reply
                        WHERE
                            F02 = forum.f01
                        ORDER BY F06 DESC , F05 DESC , CHAR_LENGTH(F04) DESC
                        LIMIT 0 , 1)),
            '匿名') AS replyNickName
FROM
    s90.t9033 collect
        LEFT JOIN
    s90.t9030 forum ON collect.f03 = forum.f01
        LEFT JOIN
    s90.t9011 user ON forum.f02 = user.f01
WHERE
    forum.f11 = 'Y'
        AND forum.f01 IS NOT NULL
        AND collect.f02 = 1807
ORDER BY collect.f04 DESC