group by 和 orede by

最新推荐文章于 2023-07-04 07:00:42 发布

关元

最新推荐文章于 2023-07-04 07:00:42 发布

阅读量344

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/u012993454/article/details/82682944

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

同时使用时,先group by再orede by,
所以要获取最新的信息,就要先max(time)再group by,

例子

SELECT
    `content`,
    `dialogue_content`.`fuserid` AS `fuserid`,
    `read_new`,
    `add_time`,
    `count`,
    `chat_type` 
FROM
    ( SELECT `content`, `fuserid`, `add_time`, `chat_type` FROM `shopwwi_dialogue` WHERE `touserid` = '10021'  ) `dialogue_content`
    RIGHT JOIN (
SELECT
    sum( IF ( isread = 0, 1, 0 ) ) AS `count`,
    `fuserid`,
IF
    ( min( isread ) = 0, 0, 1 ) AS `read_new`,
    max( add_time ) AS `max_time` 
FROM
    `shopwwi_dialogue` 
WHERE
    `touserid` = '10021' 
GROUP BY
    fuserid 
    ) dialogue_read ON `dialogue_content`.`fuserid` = `dialogue_read`.`fuserid` 
    AND add_time = max_time 
GROUP BY
    dialogue_content.fuserid 
ORDER BY
    add_time DESC