php - 从mysql中的同一个表获取链接行

Question

所以我有一个与此类似的产品表:

+--------+-----------+----------+------+
| serial | container | location | type |
+--------+-----------+----------+------+
| A      | C1        | L1       | 1    |
| B      | C1        | L1       | 2    |
| C      | C1        | L1       | 3    |
| D      | C2        | L1       | 1    |
| E      | C2        | L1       | 2    |
| F      | C2        | L2       | 1    |
| G      | C2        | L2       | 2    |
+--------+-----------+----------+------+

对于给定的序列号，我想在同一容器和位置返回类型 3 产品的序列号。

例如给定serial = A，返回serial = C

我猜测同一个表上存在内部联接，但我还无法构建合适的 SQL。非常感谢所有帮助:-)

Answer 1

select serial
from your_table
where type = 3
and concat( container, location ) = (select concat( container, location ) from your_table where serial = 'A')

或更好

select t1.serial
from your_table t1
join your_table t2 on t1.container = t2.container
                  and t1.location = t2.location 
where t1.type = 3
and t2.serial = 'A'