mysql - 如何检索从同一表(MYSQL)中的其他行获取值的行？

Question

好的，我有这张表

ControlID - Version - Type - Rel ID  - Parent Rel ID1         - 2       - 11   - 212     - 51         - 2       - 44   - 542     - 51         - 2       - 47   - 742     - 52         - 2       - 11   - 200     - 42         - 2       - 14   - 565     - 42         - 2       - 20   - 700     - 4

As showed in the above table, we have data in 2 blocks (Note: Rel ID is unique):

Block 1

ControlID - Version - Type - RelID  - ParentRelID1         - 2       - 11   - 212     - 51         - 2       - 44   - 542     - 51         - 2       - 47   - 742     - 5

Block 2

ControlID - Version - Type - RelID  - ParentRelID2         - 2       - 11   - 200     - 42         - 2       - 14   - 565     - 42         - 2       - 20   - 700     - 4

Ok, now user will enter any relID & the system will show the the Rel ID that has type=11 in the same block of entered relID.

Ex: if user enters 700, then it will know that 700 belongs to block 2 & then it will search the type=11 in block 2 & print out 200

Ex2: if user enters 742, then it will know that 742 belongs to block 1 & then it will search the type=11 in block 1 & print out 212

This is my query but I think it is too long & may be slow.

Select relID from mytable where  controlID = (select controlID from mytable where relID=700) 
and 
version= (select version from mytable where relID=700) 
and parentRelID= (select parentRelID from mytable where relID=700) 
and type=11

上面的查询有效但是太长而且可能很慢，你能缩短它并让它运行得更快吗？

Answer 1

为什么还要选择 version 和 ParentRelId ??他们也决定区 block 吗？即，是否可以存在具有相同 RelID 但具有不同 version 和/或不同 parentRelID 的不同 block ？

如果没有，试试这个:

Select * From table t
Where type = 11
   And ControlId =
        (Select ControlId 
         From table
         Where RelId = @RelId)

或....

Select * From table t
Where type = 11
    and exists (Select * from table
                Where relId = @RelId
                  and ControlId = t.ControlId)