MySql::Group by where date time max

Question

我有这样的表格结果

idCHAT  USER_NAME   TEXT     CURRENT_DATE_TIME  CHAT_COUNTERPARTY   party
54       taruna      red hat 2014-10-30 05:44:26    pooja          pooja
55        pooja      cat      2014-10-30 05:45:48   taruna         pooja

执行下面的查询后

  SELECT `idCHAT`, `USER_NAME`, `TEXT`, `CURRENT_DATE_TIME`, `CHAT_COUNTERPARTY`,
          IF(USER_NAME='taruna',`CHAT_COUNTERPARTY`,`USER_NAME`) AS party 
    FROM ( 
        SELECT c.* 
          FROM chat c
            JOIN (
              SELECT MAX(`CURRENT_DATE_TIME`) AS CURRENT_DATE_TIME, `CHAT_COUNTERPARTY`
                FROM chat 
                GROUP BY `CHAT_COUNTERPARTY`
            ) c1 USING (CURRENT_DATE_TIME,`CHAT_COUNTERPARTY`) 
          WHERE c.USER_NAME='taruna'
      UNION 
        SELECT c.* 
          FROM chat c
            JOIN (
              SELECT MAX(`CURRENT_DATE_TIME`) AS CURRENT_DATE_TIME,`USER_NAME` 
        FROM chat GROUP BY `USER_NAME`
            ) c1 USING (CURRENT_DATE_TIME,`USER_NAME`)
          WHERE c.CHAT_COUNTERPARTY='taruna'
    ) res

现在我想与派对分组并希望获得数据最大日期时间，即 idChat 55。如果结果表很大，我如何在其中添加分组依据，因为当前分组给我顶行或任何其他行。

CREATE TABLE IF NOT EXISTS `chat` (
  `idCHAT` int(11) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(12) NOT NULL,
  `TEXT` varchar(512) DEFAULT NULL,
  `CURRENT_DATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CHAT_COUNTERPARTY` varchar(12) NOT NULL,
  PRIMARY KEY (`idCHAT`),
  KEY `CHAT_CHAT_COUNTERPARTY_IDX` (`CHAT_COUNTERPARTY`),
  KEY `CHAT_CURRENT_DATE_TIME_IDX` (`CURRENT_DATE_TIME`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=56 ;

--
-- Dumping data for table `chat`
--

INSERT INTO `chat` (`idCHAT`, `USER_NAME`, `TEXT`, `CURRENT_DATE_TIME`, `CHAT_COUNTERPARTY`)
  VALUES
(1, 'taruna', 'hello', '2014-10-30 11:57:11', 'pooja'),
(2, 'pooja', 'hey', '2014-10-30 11:57:36', 'taruna'),
(3, 'taruna', 'r u getting my msg', '2014-10-30 11:58:07', 'pooja'),
(4, 'pooja', 'yes', '2014-10-30 11:58:12', 'taruna'),
(5, 'pooja', 'yes', '2014-10-30 11:58:42', 'taruna'),
(6, 'pooja', 'u will', '2014-10-30 11:59:04', 'taruna'),
(7, 'taruna', ':)', '2014-10-30 12:00:17', 'pooja'),
(8, 'pooja', 'why u dont reply me', '2014-10-30 12:00:21', 'taruna'),
(9, 'pooja', '?', '2014-10-30 12:00:25', 'taruna'),
(10, 'taruna', 'i want to buy your lappy', '2014-10-30 12:00:57', 'pooja'),
(11, 'pooja', 'sure', '2014-10-30 12:01:19', 'taruna'),
(12, 'taruna', 'fr hw much', '2014-10-30 12:01:34', 'pooja'),
(13, 'pooja', '1 lakh', '2014-10-30 12:01:44', 'taruna'),
(14, 'taruna', 'dimag khrb h???', '2014-10-30 12:02:23', 'pooja'),
(15, 'taruna', 'der??', '2014-10-30 12:04:08', 'pooja'),
(16, 'taruna', 'test', '2014-10-30 12:04:10', 'pooja'),
(17, 'taruna', 'gv sm rply', '2014-10-30 12:11:18', 'pooja'),
(18, 'pooja', 'test', '2014-10-30 12:11:32', 'taruna'),
(19, 'taruna', '????', '2014-10-30 12:11:43', 'pooja'),
(20, 'pooja', 'u got my msgs', '2014-10-30 12:11:48', 'taruna'),
(21, 'taruna', 'anything else??', '2014-10-30 12:11:50', 'pooja'),
(22, 'pooja', '??', '2014-10-30 12:11:54', 'taruna'),
(23, 'taruna', 'yupp', '2014-10-30 12:12:08', 'pooja'),
(24, 'taruna', 'hie', '2014-10-30 12:12:22', 'pooja'),
(25, 'taruna', 'hlo', '2014-10-30 12:12:30', 'pooja'),
(26, 'pooja', 'hi', '2014-10-30 12:13:09', 'taruna'),
(27, 'pooja', 'hi', '2014-10-30 12:13:16', 'taruna'),
(28, 'pooja', 'taruna', '2014-10-30 12:13:33', 'taruna'),
(29, 'taruna', 'hws u', '2014-10-30 12:13:35', 'pooja'),
(30, 'taruna', 'ring nhi ho rha phn', '2014-10-30 12:14:36', 'pooja'),
(31, 'taruna', 'jai ho', '2014-10-30 12:15:52', 'pooja'),
(32, 'pooja', 'I find error', '2014-10-30 12:17:33', 'taruna'),
(33, 'pooja', 'u got it', '2014-10-30 12:17:45', 'taruna'),
(34, 'taruna', 'yupppp', '2014-10-30 12:17:59', 'pooja'),
(35, 'pooja', 'r u well??', '2014-10-30 12:19:40', 'taruna'),
(36, 'pooja', 'hey reply', '2014-10-30 12:23:07', 'taruna'),
(37, 'pooja', 'taruna', '2014-10-30 12:23:11', 'taruna'),
(38, 'pooja', 'hey', '2014-10-30 12:23:15', 'taruna'),
(39, 'pooja', 'hey', '2014-10-30 12:23:17', 'taruna'),
(40, 'pooja', 'hey', '2014-10-30 12:23:18', 'taruna'),
(41, 'pooja', 'hey', '2014-10-30 12:23:19', 'taruna'),
(42, 'pooja', 'hi', '2014-10-30 12:24:06', 'taruna'),
(43, 'pooja', 'taru una', '2014-10-30 12:31:44', 'taruna'),
(44, 'taruna', 'its taruna', '2014-10-30 12:32:53', 'pooja'),
(45, 'taruna', 'nt taruuna', '2014-10-30 12:34:22', 'pooja'),
(46, 'pooja', 'whos u', '2014-10-30 12:35:42', 'taruna'),
(47, 'taruna', 'Taruna Sharma', '2014-10-30 12:36:05', 'pooja'),
(48, 'pooja', 'hi', '2014-10-30 12:39:27', 'taruna'),
(49, 'taruna', 'hlo', '2014-10-30 12:40:00', 'pooja'),
(50, 'pooja', 'hi', '2014-10-30 12:40:56', 'taruna'),
(51, 'taruna', '133', '2014-10-30 12:41:38', 'pooja'),
(52, 'taruna', '1234', '2014-10-30 12:41:41', 'pooja'),
(53, 'pooja', 'hat', '2014-10-30 12:42:14', 'taruna'),
(54, 'taruna', 'red hat', '2014-10-30 12:44:26', 'pooja'),
(55, 'pooja', 'cat', '2014-10-30 12:45:48', 'taruna');

Answer 1

我用多个 convo 测试了这个，这应该很好。

SELECT * FROM
  (SELECT *,
    IF(USER_NAME='taruna',CHAT_COUNTERPARTY,USER_NAME)AS `party`

    FROM `chat`

    WHERE `USER_NAME` = 'taruna' OR `CHAT_COUNTERPARTY` = 'taruna'

    ORDER BY `CURRENT_DATE_TIME` DESC) as `parties`

GROUP BY `parties`.`party`