MySQL查询-订购问题 [英] MySQL query - problem with order by

问题描述

我正在使用两个表在用户之间选择消息.表"messages"用于记录消息，表"members"用于检查用户数据(他们是激活还是删除).

I'm using two tables for selecting messages between users. Table "messages" for recording the messages and table "members" for checking users data (are they activ or deleted).

此查询工作正常.

我需要做的是按最后收到的邮件列出收到的邮件顺序.我在此查询的末尾尝试使用"ORDER BY messages.id DESC"进行操作，但是没有用. 从收到的所有邮件开始列出所有邮件.

What I need to do is list the received messsages order by last received. I tried to do with "ORDER BY messages.id DESC" at the end of this query but it didn't work. All messages are listed from first received.

这是我正在使用的mysql联接表查询:

This is the mysql join table query that I'm using:

sql = "SELECT DISTINCT messages.fromid,
            messages.readed,
            messages.fromid,
            messages.toid  ,
            members.id AS pid
FROM            messages
INNER JOIN members
ON              members.id = messages.fromid
WHERE           messages.toid              =  ".$mid."
AND             members.status             = 7
AND             messages.kreaded          !='1'
AND             messages.subject          != 'readed'
GROUP BY        fromid"

有什么办法吗?

替代文本http://www.freeimagehosting.net/uploads/e12a5b5437.jpg

推荐答案

如何听起来像

SELECT DISTINCT messages.fromid, 
            messages.readed, 
            messages.fromid, 
            messages.toid  , 
            members.id AS pid 
FROM            messages 
INNER JOIN members 
ON              members.id = messages.fromid 
WHERE           messages.toid              =  ".$mid." 
AND             members.status             = 7 
AND             messages.kreaded          !='1' 
AND             messages.subject          != 'readed'
ORDER BY    messages.fromid ASC,
            messages.id DESC

OR

SELECT DISTINCT messages.fromid, 
            messages.readed, 
            messages.fromid, 
            messages.toid  , 
            members.id AS pid 
FROM            messages 
INNER JOIN members 
ON              members.id = messages.fromid 
WHERE           messages.toid              =  ".$mid." 
AND             members.status             = 7 
AND             messages.kreaded          !='1' 
AND             messages.subject          != 'readed'
ORDER BY    messages.fromid ASC,
            messages.sendtime DESC

在 messages.fromid ASC 中， ASC 纯粹是语法上的，因为未指定ASC/DESC的ORDER BY假定为ASC.

In messages.fromid ASC the ASC is purely syntactically, as ORDER BY without specifying ASC/DESC assumes ASC.

SELECT *
FROM TABLE
ORDER BY COL ASC

与

SELECT *
FROM TABLE
ORDER BY COL

这篇关于MySQL查询-订购问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！