在MySQL中使用限制来限制基于列值的结果(PHP/MySQL)

I did multiple searches for an answer to this (on SO and elsewhere), but haven't found an answer that really fit my needs (if it's out there, I apologize in advance).

我对这个问题进行了多次搜索(等等)，但没有找到一个真正符合我需要的答案(如果它确实存在，我提前道歉)。

I have a query, using PHP, that returns an array from a database (WordPress). Basically what I want to do is look at a column's value, and then LIMIT based on that value. Here's the array that's returned for a better idea:

我有一个使用PHP的查询，它从数据库(WordPress)返回一个数组。基本上我要做的是看一列的值，然后根据这个值限制。这是为了更好的想法而返回的数组:

http://pastebin.com/AC043qfh

In the query, you'll notice that the value for post_parent repeats for several of returned arrays. What I want to do is have it LIMIT to 3 based on the post_parent value e.g. I want 3 entries for post_parent 79, 87, 100, etc.

在查询中，您将注意到post_parent的值对几个返回数组重复。我要做的是根据post_parent值将其限制为3，例如，我希望post_parent 79、87、100等有3个条目。

I'm not well versed (see: at all) in MySQL queries, but this is what I have to get that array:

我对MySQL查询不是很精通(看:一点也不)，但这就是我要得到这个数组的原因:

SELECT DISTINCT ID, guid, post_parent, post_title 
FROM $wpdb->posts p 
WHERE p.post_type = 'attachment'
    AND p.post_mime_type LIKE 'image/%'
    AND p.post_status = 'inherit'
    AND p.post_parent IN
        (SELECT object_id FROM $term_relationships WHERE term_taxonomy_id = $post_term)

I've tried using GROUP BY, but that didn't get me what I wanted. Any help is appreciated.

我试过用GROUP BY，但是没有得到我想要的。任何帮助都是感激。

EDIT Just to clarify, these are the results I want: http://pastebin.com/pWXdUuXv

编辑只是为了澄清，这些是我想要的结果:http://pastebin.com/pWXdUuXv

1 个解决方案

#1

This might do the trick: (I'm assuming ID is unique, if not substitute something that is)

这可能会起到这样的作用:(我假设ID是惟一的，如果不能替换它的话)

SELECT
  p.ID, guid, post_parent, post_title
FROM (
SELECT
  a.ID as ID,
  COUNT(*) as rank
FROM (
  SELECT ID, post_parent
  FROM $wpdb->posts
  WHERE post_type = 'attachment'
    AND post_mime_type LIKE 'image/%'
    AND post_status = 'inherit'
  ) AS a
JOIN (
  SELECT ID, post_parent
  FROM $wpdb->posts
  WHERE post_type = 'attachment'
    AND post_mime_type LIKE 'image/%'
    AND post_status = 'inherit'
  ) AS b ON b.ID <= a.ID AND b.post_parent = a.post_parent
GROUP BY a.ID
) AS r
JOIN $wpdb->posts p ON r.ID = p.ID AND r.rank <= 3
WHERE p.post_parent IN (
  SELECT object_id FROM $term_relationships
  WHERE term_taxonomy_id = $post_term)
GROUP BY p.ID
;

EDIT: Attempt to include category in rank so it'll actually work.

编辑:尝试在等级中包含类别，这样它就可以工作了。

Specifying conditions twice is a bit ugly, but I didn't see an easy way around it.

两次指定条件有点难看，但是我没有找到一个简单的方法。

#1