MySQL:使用“In"有多个子查询? [英] MySQL: Using "In" with Multiple SubQueries?

问题描述

我正在尝试使用此查询:

<前>SELECT COUNT(PF.PageID) AS Total,P.PageID从页面 PLEFT JOIN Pages_Filters PF ON PF.PageID=P.PageIDP.PageID 在 ((选择页面IDFROM Pages_FiltersWHERE FilterID="1"AND FilterOptionID="2"),(选择页面IDFROM Pages_FiltersWHERE FilterID="7"AND FilterOptionID="57"))和 P.PageID !="283"按 PF.PageID 分组

产生错误的原因:

子查询返回超过 1 行

我希望 MySQL 合并所有子查询的结果，并使用共同的值作为主查询中的 IN 子句.

因此，如果第一个子查询返回 1,2,4，第二个子查询返回 2,3,4，则in"子句将显示为:

WHERE P.PageID IN (2,4)

因为 2 和 4 是两个子查询的共同值.

这可以通过一个查询实现吗?

更新:

我将尝试更好地澄清这个问题.

结果应返回所有将指定过滤器设置为特定值的页面，包括设置了不属于查询一部分的额外过滤器的页面.换句话说，我在一个设置了某些过滤器的页面上，向我显示我可以从这里转到的具有这些相同过滤器的所有页面，并有一个额外的过滤器集.

解释起来有点棘手，但我正在寻找一个结果集，该结果集包含具有其他过滤器集的页面，但不包含具有相同过滤器 ID 的不同值的页面.

因此，在示例中，我们希望所有将过滤器 1 设置为值 2，并将过滤器 7 设置为 57 的页面.我们还希望分配具有其他过滤器(除了 1 和 7)的页面，但不是具有过滤器 7 的页面设置为 57 以外的值，而不是将过滤器 1 设置为 2 以外的值的页面.

使用样本数据更新

Pages_Filters:

<前>PageID FilterID FilterOptionID297 2 5297 7 57297 9 141305 2 5101 2 5101 7 57

页面:

<前>页面ID 页面名称第297页第305页第101话

在这种情况下，我应该可以问，哪些页面将过滤器 2 设置为 5，将过滤器 7 设置为 57，并将过滤器 9 设置为 141"?

答案应该是只有 297".

解决方案

隔离生成 PageID 列表的查询.您需要以一个返回您需要的值的查询结束.你可以这样做:

SELECT PageID P1 FROM Page_FiltersWHERE FilterID = "1" AND FilterOptionID = "2" AND EXISTS(SELECT * FROM PageID P2WHERE P2.PageID = P1.PageID AND FilterID = "7" AND FilterOptionID = "57")

I'm trying to use this query:

SELECT COUNT(PF.PageID) AS Total,P.PageID
FROM Pages P
LEFT JOIN Pages_Filters PF ON PF.PageID=P.PageID
WHERE P.PageID IN (
    (SELECT PageID
       FROM Pages_Filters
       WHERE FilterID="1"
       AND FilterOptionID="2"
    ),
    (SELECT PageID
       FROM Pages_Filters
       WHERE FilterID="7"
       AND FilterOptionID="57"
    )
)
AND P.PageID !="283"
GROUP BY PF.PageID

Which produces the error:

Sub-query returns more than 1 row

I'd like for MySQL to merge the results of all the sub-queries, and use values in common as the IN clause in the main query.

So if the first sub-query returns 1,2,4 and the second sub-query returns 2,3,4, then the "in" clause would read:

WHERE P.PageID IN (2,4)

because 2 and 4 are the values the two sub-queries have in common.

Is this possible with one query?

UPDATE:

I'm going to try to clarify the question a bit better.

The result should return all of the pages which have the specified filters set to a specific value INCLUDING pages that have extra filters set which are not part of the query. In other words, I'm on a page with certain filters in place, show me all the pages that I can go to from here that have these same filters, and have one additional filter set.

It's a bit tricky to explain, but I'm looking for a result set that INCLUDES pages with other filters set, but NOT pages with a different value for the same filter ID.

So in the example, we want all pages that have filter 1 set to value 2, AND filter 7 set to 57. We also want pages with other filters (besides 1 and 7) assigned, but NOT pages who have filter 7 set to a value other than 57, and NOT pages who have filter 1 set to a value other than 2.

UPDATE with SAMPLE DATA

Pages_Filters:

PageID  FilterID    FilterOptionID
297        2           5
297        7           57
297        9           141
305        2           5
101        2           5
101        7           57

Pages:

PageID   PageName
297        Some Page
305        Another Page
101        A Stupid Page

In this case I should be able to ask, "What pages have Filter 2 set to 5, also have filter 7 set to 57, and also have filter 9 set to 141"?

The answer should be "only 297".

解决方案

Isolate the query that produces your list of PageIDs. You need to end up with a single query that returns the values you need. You can do that like this:

SELECT PageID P1 FROM Page_Filters
WHERE  FilterID = "1" AND FilterOptionID = "2" AND EXISTS
    (SELECT * FROM PageID P2 
    WHERE P2.PageID = P1.PageID AND FilterID = "7" AND FilterOptionID = "57")

这篇关于MySQL:使用“In"有多个子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！