我有这个查询
SELECT ann_arr.id,ann_arr.user_id,ann_arr.date,ann_arr.time,
ann_arr.venue_id
FROM ann_arr
WHERE user_id
IN (SELECT DISTINCT n2.mem_id AS frnd FROM network n1,network n2 WHERE n1.frd_id=n2.mem_id AND n1.mem_id='329' AND n2.frd_id='329')
AND ann_arr.user_id !='329'
ORDER BY DATE DESC,TIME DESC
我正在获得的结果
id user_id date time venue_id
995 399 2012-02-02 12:37:30 56
994 399 2012-02-02 12:36:06 56
993 399 2012-02-02 12:35:15 56
992 399 2012-02-02 12:32:40 56
991 399 2012-02-02 12:26:17 56
990 399 2012-02-02 12:23:06 56
989 399 2012-02-02 12:21:52 56
968 693 2012-01-25 13:27:11 1016
我想要的结果必须包含唯一的用户id和最后一个id
,就像用户399
的id将是955
一样
*注释SELECT DISTINCT n2.mem_id AS frnd FROM network n1,network n2 WHERE n1.frd_id=n2.mem_id AND n1.mem_id='329' AND n2.frd_id='329'
将包含用户的朋友ID,如'399','693'
等
//预期答案
id user_id date time venue_id
995 399 2012-02-02 12:37:30 56
968 693 2012-01-25 13:27:11 1016
请帮助
感谢
您应该尝试GROUP BY user_id,&更改日期顺序。
如果我理解正确,您需要按user_id:分组
SELECT ann_arr.id,ann_arr.user_id,ann_arr.date,ann_arr.time,
ann_arr.venue_id
FROM ann_arr
WHERE user_id
IN (SELECT DISTINCT n2.mem_id AS frnd FROM network n1,network n2 WHERE n1.frd_id=n2.mem_id AND n1.mem_id='329' AND n2.frd_id='329')
AND ann_arr.user_id !='329'
GROUP BY ann_arr.user_id
ORDER BY DATE DESC,TIME DESC
将id DESC添加到ORDER BY子句中。