mysql - php - 需要从自引用数据中获取最新记录

问题描述：

enter image description here

Hi, I need some help with SQL. Attached is the image of my table.

If you see rootmessageid column there are 4 records of 99s. All these 4 makes one complete conversation.

Similarly the 2 records of 119 makes an other conversation.

116, 117, 118 are single message conversation.

Now I need to get all the records where msgfrom = 7 or msgto = 7 (this was the easy part)

Now the complicated bit. I want the only the latest record (based on datetimecreated) from each conversation.

Following the script to create this table.

CREATE TABLE IF NOT EXISTS `selectioncommunication` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comactionid` int(11) NOT NULL,
  `usercomment` varchar(2048) DEFAULT NULL,
  `msgfrom` int(11) NOT NULL,
  `msgto` int(11) NOT NULL,
  `projectid` int(11) NOT NULL,
  `parentmessageid` int(11) NOT NULL,
  `datetimecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rootmessageid` int(11) NOT NULL,
  `isread` tinyint(1) NOT NULL DEFAULT '0',
  `isclosed` tinyint(1) DEFAULT '0',
  `relative_date_time` datetime DEFAULT NULL,
  `consultant_response` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=121 );

嗨，我需要一些SQL帮助。附上我的桌子的图像。 p>

如果你看到rootmessageid列，则有4条99条记录。所有这4个进行了一次完整的对话。 p>

同样，119的2条记录进行了另一次对话。 p>

116,117,118是单信息对话。 p> \ n

现在我需要获取msgfrom = 7或msgto = 7的所有记录（这是容易的部分） p>

现在是复杂的位。我想要每个会话中唯一的最新记录（基于datetimecreated）。 p>

按照脚本创建此表。 p>

  CREATE 表IF NOT NOT EXISTS`selectioncommunication`（
`id`int（11）NOT NULL AUTO_INCREMENT，
`comactionid`int（11）NOT NULL，
`usercomment`varchar（2048）DEFAULT NULL，
`msgfrom`  int（11）NOT NULL，
`msgto` int（11）NOT NULL，
`projectid` int（11）NOT NULL，
`parentmessageid` int（11）NOT NULL，
`datetimecreated` timestamp NOT  NULL DEFAULT CURRENT_TIMESTAMP，
“rootmessageid”int（11）NOT NULL，
`inread` tinyint（1）NOT NULL DEFAULT'0'，
`isclosed`minintint（1）DEFAULT'0'，
`delative_date_time  `datetime DEFAULT NULL，
`consultant_response` tinyint（4）DEFAULT NULL，
 PRIMARY KEY（`id`）
）ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 121）; 
  code>  pre  > 
  div>

答

You want the groupwise maximum:

SELECT s.*
FROM   selectioncommunication s NATURAL JOIN (
  SELECT   parentmessageid, MAX(datetimecreated) datetimecreated
  FROM     selectioncommunication
  WHERE    msgfrom = 7 OR msgto = 7
  GROUP BY parentmessageid
) t
WHERE  s.msgfrom = 7 OR s.msgto = 7

答

use ORDER BY datetime ASC/DESC

this will sort your results in order then add LIMIT 1 to the end of your query to only get the first record in your list.

答

Here is your SQl Fiddle without Join

SELECT *
FROM selectioncommunication k
WHERE datetimecreated = (SELECT
                           MAX(datetimecreated)
                         FROM selectioncommunication s
                         WHERE s.rootmessageid = k.rootmessageid
                         GROUP BY s.rootmessageid
                         ORDER BY s.id)

mysql - php - 需要从自引用数据中获取最新记录

相关推荐