mysql - 数据库简单查询问题

Question

查询一张表中table_a中，有aid, bid, 查询记录，存在所有成对的记录，例如 aid = 10， bid = 11，如果存在aid = 11, bid = 10，就把这两条记录同时查出来，不存在则跳过。该怎么写呢？

select * from table_a where (aid, bid) in (select bid, aid from table_a )

这样写正确嘛？如果不对该怎么写呢？

Answer 1

It’s simple:

SELECT T.aid, T.bid
FROM table_a T
INNER JOIN table_a S
ON T.aid = S.bid
AND T.bid = S.aid

or

SELECT T.aid, T.bid
FROM table_a T, table_a S
WHERE T.aid = S.bid
AND T.bid = S.aid

Same thing.

Answer 2

How to judge whether the aid bid is a pair?

Answer 3

SELECT * FROM `table_a` WHERE concat ((aid*bid),'_',(aid+bid)) in (
    SELECT concat((aid*bid),'_',(aid+bid)) 
    FROM `table_a` 
    GROUP BY (aid*bid),(aid+bid) 
    HAVING count(id)>1
)

I don’t know if this works (if not, just think of it as an idea)