SELECT * FROM table WHERE字段IN(SELECT ID FROM table ORDER BY field2) [英] SELECT * FROM table WHERE field IN (SELECT id FROM table ORDER BY field2)

问题描述

我有4张桌子:

categories - id, position
subcategories - id, categories_id, position
sub_subcategories - id, subcategories_id, position
product - id, sub_subcategories_id, prod_pos

现在，我正在进行测试，以查明查询出了什么问题.

Now I'm doing tests to find out what's wrong with my query.

所以我想选择sub_subcategories，并得到类似的东西:

So i want to select sub_subcategories, and to get someting like that:

[[1,2,3,4,5,6], [1,2,3,4,5,6,7]], [[1,2,3,4,5,6], [1,2,3,4]]

每个[]的意思是:大-类别，小-子类别，数字在sub_subcategories中的位置.我希望[]按其位置"字段排序，因此查询:

Each [] means: big - categories, small - subcategory, and the numbers are position in sub_subcategories. I want the [] to order by their "position" field, so query:

SELECT id FROM sub_subcategories_id 
WHERE subcategories_id IN (
       SELECT id 
       FROM subcategories_id 
       WHERE categories_id IN (
            SELECT id FROM categories 
            WHERE id = 'X' ORDER BY position) 
            ORDER BY position) 
ORDER BY position

有点不对劲，因为我得到了:

is somehow wrong, because I get:

1,1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,5,6,6,6,7

Dunno为什么-上一个按位置排列"会破坏一切吗?

Dunno why - does last "ORDER BY position" destroy everything?

推荐答案

您需要在最外面的查询中应用所有所需的排序-在子查询中进行ORDER排序没有任何意义-问题此ID是否在<此列表> ;?不论列表以什么顺序排列(实际上，更多属性，< this list>是一个没有顺序的集合)，答案都是相同的.

You need to apply all of your desired ordering in the outermost query - ORDERing within subqueries doesn't make any sense - the question "is this ID in <this list>?" has the same answer, no matter what order the list is in (indeed, more property, <this list> is a set, which has no order).

因此，您需要在最外面的查询中获得所有需要排序的列.

So you'll need to get all of the columns you need to order by in your outermost query.

类似的东西:

SELECT ssi.ID
from
    sub_subcategories_id ssi
        inner join
    subcategories_id si
        on
            ssi.subcategories_id = si.id
        inner join
    categories c
        on
           si.categories_id = c.id
where
    c.id = 'X'
order by
    c.position,
    si.position,
    ssi.position

这篇关于SELECT * FROM table WHERE字段IN(SELECT ID FROM table ORDER BY field2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！