MySql ::按日期时间最大分组 [英] MySql :: Group by where date time max
本文介绍了MySql ::按日期时间最大分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有这样的表格结果
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
现在，我想与聚会进行分组，并希望获得数据的最大日期时间，即idChat55.我如何在其中添加分组，因为当前分组是通过给我排在第一行或结果表很大的任何其他分组.
Now i want to group by with party and want to get data max date time i.e. idChat 55. How can i add group by in it because current group by giving me top row or any other if result table is large.
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');
推荐答案
我用多个convos进行了测试，这应该很好.
I tested this with multiple convos, this should be good.
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`
这篇关于MySql ::按日期时间最大分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
