MySQL依赖集_mysql – 组合依赖于另一个的结果集的查询

我运行此查询以根据评级,类别等内容从我的wordpress数据库中获取20个随机项

SELECT (A.user_votes/A.user_voters) as site_rating, B.ID as post_id, B.post_author, B.post_date,E.name as category

FROM `wp_gdsr_data_article` as A

INNER JOIN `wp_posts` as B ON (A.post_id = B.id)

INNER JOIN wp_term_relationships C ON (B.ID = C.object_id)

INNER JOIN wp_term_taxonomy D ON (C.term_taxonomy_id = D.term_taxonomy_id)

INNER JOIN wp_terms E ON (D.term_id = E.term_id)

WHERE

B.post_type = 'post' AND

B.post_status = 'publish' AND

D.taxonomy='category' AND

E.name NOT IN ('Satire', 'Declined', 'Outfits','Unorganized', 'AP')

ORDER BY RAND()

LIMIT 20

然后,对于随机项的每个结果,我想找到一个与随机项非常相似的对应项(大约相同的评级)但不相同,也是用户没有看到的项：

SELECT ABS($site_rating-(A.user_votes/A.user_voters)) as diff, (A.user_votes/A.user_voters) as site_rating, B.ID as post_id, B.post_author, B.post_date,E.name as category ,IFNULL(F.count,0) as count

FROM `wp_gdsr_data_article` as A

INNER JOIN `wp_posts` as B ON (A.post_id = B.id)

INNER JOIN wp_term_relationships C ON (B.ID = C.object_id)

INNER JOIN wp_term_taxonomy D ON (C.term_taxonomy_id = D.term_taxonomy_id)

INNER JOIN wp_terms E ON (D.term_id = E.term_id)

LEFT JOIN (

SELECT *,COUNT(*) as count FROM `verus` WHERE ip = '{$_SERVER['REMOTE_ADDR']}'

) as F ON (A.post_id = F.post_id_winner OR A.post_id = F.post_id_loser)

WHERE

E.name = '$category' AND

B.ID <> '$post_id' AND

B.post_type = 'post' AND

B.post_status = 'publish' AND

D.taxonomy='category' AND

E.name NOT IN ('Satire', 'Declined', 'Outfits','Unorganized', 'AP')

ORDER BY count ASC, diff ASC

LIMIT 1

以下php变量引用上一个查询的结果

$post_id = $result['post_id'];

$category = $result['category'];

$site_rating = $result['site_rating'];

和$_SERVER [‘REMOTE_ADDR’]指的是用户的IP.

有没有办法将第一个查询与需要调用的20个额外查询结合起来查找相应的项目,这样我只需要1或2个查询？

编辑：这是简化连接的视图

CREATE VIEW `versus_random` AS

SELECT (A.user_votes/A.user_voters) as site_rating, B.ID as post_id, B.post_author, B.post_date,E.name as category

FROM `wp_gdsr_data_article` as A

INNER JOIN `wp_posts` as B ON (A.post_id = B.id)

INNER JOIN wp_term_relationships C ON (B.ID = C.object_id)

INNER JOIN wp_term_taxonomy D ON (C.term_taxonomy_id = D.term_taxonomy_id)

INNER JOIN wp_terms E ON (D.term_id = E.term_id)

WHERE

B.post_type = 'post' AND

B.post_status = 'publish' AND

D.taxonomy='category' AND

E.name NOT IN ('Satire', 'Declined', 'Outfits','Unorganized', 'AP')

我现在尝试以下观点：

SELECT post_id,

(

SELECT INNER_TABLE.post_id

FROM `versus_random` as INNER_TABLE

WHERE

INNER_TABLE.post_id <> OUTER_TABLE.post_id

ORDER BY (SELECT COUNT(*) FROM `versus` WHERE ip = '54' AND (INNER_TABLE.post_id = post_id_winner OR INNER_TABLE.post_id = post_id_loser)) ASC

LIMIT 1

) as innerquery

FROM `versus_random` as OUTER_TABLE

ORDER BY RAND()

LIMIT 20

但是查询只是超时并冻结了我的mysql.