mysql - 选择具有相同列值的行

Question

鉴于下面的表结构，如何仅选择给定名称和 user_id 具有相同根和级别值的行？

    Category
    +----+---------+------+------+-------+
    | id | user_id | name | root | level |
    +----+---------+------+------+-------+
    |  1 |      10 | wzq  |    1 |     1 |
    |  2 |      11 | xyz  |    2 |     1 |
    |  3 |      10 | xyz  |    2 |     2 |
    |  4 |      10 | xyz  |    2 |     2 |
    +----+---------+------+------+-------+   

我现在还选择没有相同根和级别的行

SELECT `c`.`id`, `c`.`user_id`, `c`.`name`, `c`.`root`, `c`.`level`
FROM `category` `c`
WHERE c.id IN (SELECT d.id FROM category AS d WHERE c.root=d.root AND c.level=d.level ) AND c.user_id = 10 AND c.name = 'xyz'
ORDER BY `c`.`id`

对于上面的示例，仅应返回第 3 行和第 4 行

Answer 1

尝试这个仅选择具有相同根和级别的行只需root=level

SELECT `id`, `user_id`, `name`, `root`, `level`
FROM `category` 
WHERE root=level  AND user_id = 10 AND `name` = 'xyz'
ORDER BY `id`