多表查询添加序号

最新推荐文章于 2023-12-11 14:16:22 发布

柴散人

最新推荐文章于 2023-12-11 14:16:22 发布

阅读量407

点赞数

文章标签：多表查询添加序号

本文链接：https://blog.csdn.net/qq_42708356/article/details/81331481

版权

SELECT
*,
(@rowNO := @rowNo+1) AS rowno, 此处定义变量
(SELECT tw_name FROM tb_words where tw_id = tdi_model_id) AS modelName,
(SELECT tw_name FROM tb_words where tw_id = tdi_brand_id) AS brandName,
(SELECT tmi_name FROM tb_model3d_info where tmi_id = tdi_model3d_id) AS model3dName
FROM
tb_device_position
LEFT JOIN tb_customer_room_device_refer ON tdp_room_device_id = tcrdr_id
AND tcrdr_status = 1
LEFT JOIN tb_device_info ON tdi_id = tcrdr_device_id
AND tdi_status = 1
left join (SELECT @rowNo:=0) r on 1=1 此处创建虚表 left join 需要加条件所以有1=1
WHERE
tcrdr_room_id = 18
AND tcrdr_status = 1 ;
(SELECT tw_name FROM tb_words where tw_id = tdi_model_id) AS modelName
(SELECT tw_name FROM tb_words where tw_id = tdi_brand_id) AS brandName
(SELECT tmi_name FROM tb_model3d_info where tmi_id = tdi_model3d_id) AS model3dName
LEFT JOIN tb_words ON tw_id = tdi_model_id