mysql - 如何仅从 SQL 中选择唯一的 user_id

Question

这是我的sql:

SELECT `activity`.* 
FROM `activity`,`exchange`  
WHERE `activity`.`id` != 0 
AND `activity`.`type` !=6 
AND (`activity`.`type` = 4 OR `activity`.`type` = 5)
AND (`exchange`.`id` = `activity`.`user_id`) 
AND (`exchange`.`photo` != 0) 
ORDER BY `activity`.`id` DESC  
LIMIT 4

目前我在结果中得到重复的 user_id。我怎样才能从中获得非重复的 user_id，以便它们是不同的？

Exchange 是用户表。

数据:

CREATE TABLE IF NOT EXISTS `activity` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(255) NOT NULL,
  `type` int(2) NOT NULL,
  `link` varchar(255) NOT NULL,
  `time` int(50) NOT NULL,
  `add_text` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12583 ;

--
-- Dumping data for table `activity`
--

INSERT INTO `activity` (`id`, `user_id`, `type`, `link`, `time`, `add_text`) VALUES
(12567, 11615, 4, 'Aprender-Español&lang=es&discussionID=1131&t=viewDiscussion', 1360941086, 'for everyone wants to learn spanish'),
(12560, 6091, 4, 'Random-topics&lang=&discussionID=1130&t=viewDiscussion', 1360936869, 'Valentine''s Day'),
(12542, 11603, 5, 'Aprender-Español&t=viewDiscussion&discussionID=966&page=', 1360923369, 'AMIGA ALEMANA :)'),
(12541, 11603, 5, 'Aprender-Español&t=viewDiscussion&discussionID=1036&page=', 1360923340, 'Want to re-learn Spanish'),
(12540, 11603, 5, 'Aprender-Español&t=viewDiscussion&discussionID=1114&page=', 1360923203, 'Quiero practicar mi espanol!'),
(12539, 11603, 5, 'Cultura-y-Sociedad---Español&t=viewDiscussion&discussionID=106&page=', 1360922991, '¿Estás de acuerdo con que se hagan corridas de toro?'),
(12532, 11604, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=1112&page=', 1360920608, 'Looking for a French Penpal!'),
(12527, 11495, 5, 'Learning-English&t=viewDiscussion&discussionID=54&page=', 1360909410, 'Why are you learning English?'),
(12503, 11100, 5, 'Learning-English&t=viewDiscussion&discussionID=54&page=', 1360876079, 'Why are you learning English?'),
(12502, 11100, 5, 'Learning-English&t=viewDiscussion&discussionID=54&page=', 1360876051, 'Why are you learning English?'),
(12491, 9641, 5, 'Culture-and-Society---English&t=viewDiscussion&discussionID=1129&page=', 1360874309, 'Rock music groups from your country?'),
(12490, 1874, 4, 'Culture-and-Society---English&discussionID=1129&t=viewDiscussion', 1360870935, 'Rock music groups from your country?'),
(12478, 11609, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=1112&page=', 1360856071, 'Looking for a French Penpal!'),
(12448, 11604, 4, 'Aide-demandée,-en-français&lang=es&discussionID=1128&t=viewDiscussion', 1360832732, 'Apprendre le français'),
(12442, 11604, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=1123&page=', 1360832130, 'Français!'),
(12433, 11605, 5, 'Learning-English&t=viewDiscussion&discussionID=54&page=', 1360830795, 'Why are you learning English?'),
(12406, 887, 5, 'Learning-English&t=viewDiscussion&discussionID=1127&page=', 1360796193, 'silver'),
(12405, 11600, 4, 'Learning-English&lang=&discussionID=1127&t=viewDiscussion', 1360796100, 'silver'),
(12399, 11596, 4, 'Apprendre-le-Français&lang=es&discussionID=1126&t=viewDiscussion', 1360794605, 'Je voudrais apprendre le française'),
(12384, 11595, 5, 'Learning-English&t=viewDiscussion&discussionID=173&page=', 1360791086, 'What is your favourite song? '),
(12349, 11578, 5, 'Learning-English&t=viewDiscussion&discussionID=478&page=', 1360771365, 'What kind of music do you like and why?'),
(12306, 11578, 5, 'Culture-and-Society---English&t=viewDiscussion&discussionID=1012&page=', 1360708606, 'Why are you learning a foreign language?'),
(12292, 6091, 5, '&t=viewDiscussion&discussionID=330&page=', 1360693956, 'Which day of the week do you hate the most? Why?'),
(12262, 11567, 5, 'Aprender-Español&t=viewDiscussion&discussionID=1114&page=', 1360661625, 'Quiero practicar mi espanol!'),
(12238, 11544, 5, 'Learning-English&t=viewDiscussion&discussionID=909&page=', 1360648815, 'What instrument(s) do you play?'),
(12195, 887, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=1077&page=', 1360624305, 'Resources to Learn French'),
(12135, 6091, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=471&page=', 1360599702, 'Connaissez-vous le Verlan?'),
(12134, 2933, 5, 'Travel,_Live_and_Study_Abroad_-_English&t=viewDiscussion&discussionID=1124&page=', 1360596914, 'Would you like to make an exchange?'),
(12131, 3934, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=471&page=', 1360595290, 'Connaissez-vous le Verlan?'),
(12123, 11543, 5, 'Aprender-Español&t=viewDiscussion&discussionID=1114&page=', 1360587427, 'Quiero practicar mi espanol!');

SQL Fiddle Schema

Answer 1

这应该很接近——获取与用户和事件关联的最长时间:

SELECT `activity`.* 
FROM `exchange`
    JOIN `activity` ON `exchange`.`id` = `activity`.`user_id`
    JOIN (
        Select User_Id, MAX(Time) timeMax
        FROM Activity
        WHERE `activity`.`id` != 0 
            AND `activity`.`type` !=6 
            AND (`activity`.`type` = 4 OR `activity`.`type` = 5)
        GROUP BY User_Id
    ) maxActivity ON `activity`.`user_id` = maxActivity.User_Id 
            AND `activity`.`Time` = maxActivity.timeMax
WHERE (`exchange`.`photo` != 0) 
ORDER BY `activity`.`id` DESC  
LIMIT 4

这是你的 fiddle :http://sqlfiddle.com/#!2/693e5/10

顺便说一句——不需要 Activity.Type != 6——只需使用 Activity.Type IN (4,5)——或者保留上面的 OR。

SELECT `activity`.* 
FROM `exchange`
    JOIN `activity` ON `exchange`.`id` = `activity`.`user_id`
    JOIN (
        Select User_Id, MAX(Time) timeMax
        FROM Activity
        WHERE `activity`.`id` != 0 
            AND `activity`.`type` IN (4, 5)
        GROUP BY User_Id
    ) maxActivity ON `activity`.`user_id` = maxActivity.User_Id 
            AND `activity`.`Time` = maxActivity.timeMax
WHERE (`exchange`.`photo` != 0) 
ORDER BY `activity`.`id` DESC  
LIMIT 4