最近两周做项目,到处是sql查询,写了不少sql,技术略有提升,在此做个一些列记录,一遍后续写sql做参考。
有如下结构数据的两张表:
表tb_info
uuid | asset_key
-------------------------------------------------------+---------------------------------------096b55bd03944444853dd49de42c1413 | 19.19.19.19
aac8d8f6b36e41aeb211bffc51b9d6fc | http://140.140.1.5:8000
表tb_relation
base_uuid | relation_uuid
---------------------------------------------------+------------------------------------------------------
aac8d8f6b36e41aeb211bffc51b9d6fc | 096b55bd03944444853dd49de42c1413
其中,表tb_relation的base_uuid,relation_uuid对应表tb_info的两条记录,标识着将表tb_info的这两条记录关联起来
现要求,用一条sql查询出所有在表B中有关联的表tb_info中的asset_key对应关系,即需要如下结果:
uuid | asset_key | uuid | asset_key
--------------------------------------------------+--------------------------------+------------------------------------------------------+-------------------
aac8d8f6b36e41aeb211bffc51b9d6fc | http://140.140.1.5:8000 | 096b55bd03944444853dd49de42c1413 | 19.19.19.19
附上我的sql:
SELECT
A.uuid,
A.asset_key,
B.relation_uuid uuid,
C.asset_key
FROM
tb_info A
INNER JOIN
tb_relation B
ON
A.uuid = B.base_uuid
INNER JOIN
tb_info C
ON
B.relation_uuid = C.uuid
先关联查询出tb_info与tb_relation有关的数据,把其结果当成另一条sql的临时表,用此临时表再去关联表tb_info,得出结果,如下:
SELECT
C.uuid,
C.asset_key,
D.uuid,
D.asset_key
FROM
(
SELECT
A.uuid,
A.asset_key,
B.relation_uuid
FROM
tb_info A
INNER JOIN
tb_relation B
ON
A.uuid = B.base_uuid
) C
INNER JOIN
tb_info D
ON
C.relation_uuid = D.uuid
结束!!!