通过特定键范围限制mysql结果，包括重复项 [英] limiting mysql results by range of a specific key INCLUDING DUPLICATES

问题描述

我有一个查询

SELECT p.*, m.*,
  (SELECT COUNT(*) FROM newPhotoonAlert n WHERE n.userIDfor='$id' AND n.threadID=p.threadID and n.seen='0')  AS unReadCount 
    FROM posts p
    JOIN myMembers m ON m.id = p.user_id
    LEFT JOIN following f 
    ON (p.user_id = f.user_id AND f.follower_id='$id' 
    AND f.request='0' AND f.status='1')
    JOIN myMembers searcher ON searcher.id = '$id' 
    WHERE ((f.follower_id = searcher.id) OR m.id='$id')
    AND p.flagged <'5' 
    ORDER BY p.threadID DESC,p.positionID

它带来了预期的结果，但是我想添加另一个条款来限制结果. 通过上面的查询，说出一个样本数据集(显示为最小值).

It brings result as expected but I want to add Another CLAUSE to limit the results. Say a sample (minimal shown) set of data looks like this with the above query.

threadID   postID positionID  url
  564       1254     2         a.com
  564       1245     1         a1.com
  541       1215     3         b1.com 
  541       1212     2         b2.com 
  541       1210     1         b3.com 
  523       745      1         c1.com
  435       689      2         d2.com
  435       688      1         a4.com
  256       345      1         s3.com
  164       316      1         f1.com
  .
  .

我想从MAX开始获得对应于2个DISTINCT线程ID的ROWS，但我也想包括重复项.

I want to get ROWS corresponding to 2 DISTINCT threadIDs starting from MAX, but I want to include duplicates as well.

类似

AND p.threadID IN (Select just Two of all threadIDs currently selected, but include duplicate rows)

所以我的结果应该是

threadID   postID positionID  url
  564       1254     2         a.com
  564       1245     1         a1.com
  541       1215     3         b1.com 
  541       1212     2         b2.com 
  541       1210     1         b3.com 

推荐答案

您可以使用类似这样的东西:

You may use something like this:

select threadID, postID, positionID, url 
from your_table where 
threadID in
(select * from (select distinct threadID from your_table order by threadID desc limit 10) as tab_alias);

尝试使用此语法来实现它.

Try this syntax to implement it.

这篇关于通过特定键范围限制mysql结果，包括重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！