一个数据库表中有多个字段和另一个表中的主键对应,sql如何查询?

现在这里有两个表:
stock_dismantle
《一个数据库表中有多个字段和另一个表中的主键对应,sql如何查询?》
stock_info
《一个数据库表中有多个字段和另一个表中的主键对应,sql如何查询?》
其中stock_dismantle中的后两个字段都是从stock_info的主键id中获取的,那么这时连表查询怎样才能同时找到stock_id_first和stock_id_second对应的stock_info里的信息呢?
要连两次表,并给表起别名

left JOIN stock_infor AS t1 ON stock_dismantle.stock_id_first = t1.id
left JOIN stock_infor AS t2 ON stock_dismantle.stock_id_second = t2.id
SELECT
	stock_dismantle.id,
	stock_dismantle.stock_id_first,
	stock_dismantle.stock_id_second,
	(
		SELECT
			t1.original_barcode
		WHERE
			t1.id = stock_dismantle.stock_id_first
	) AS aCode,
	(
		SELECT
			t2.original_barcode
		WHERE
			t2.id = stock_dismantle.stock_id_second
	) AS bCode,
	(
		SELECT
			t1.stock_name
		WHERE
			t1.id = stock_dismantle.stock_id_first
	) AS aName,
	(
		SELECT
			t2.stock_name
		WHERE
			t2.id = stock_dismantle.stock_id_second
	) AS bName,
(
		SELECT
			t1.stock_num
		WHERE
			t1.id = stock_dismantle.stock_id_first
	) AS aNum,
	(
		SELECT
			t2.stock_num
		WHERE
			t2.id = stock_dismantle.stock_id_second
	) AS bNum
FROM
	`stock_dismantle`
left JOIN stock_infor AS t1 ON stock_dismantle.stock_id_first = t1.id
left JOIN stock_infor AS t2 ON stock_dismantle.stock_id_second = t2.id

得到如下结果:
《一个数据库表中有多个字段和另一个表中的主键对应,sql如何查询?》

    原文作者:whisper~~
    原文地址: https://blog.csdn.net/qq_43677686/article/details/105738962
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞