求助一条mysql查询_SQL代码_论坛

求助一条mysql查询

岛***疤分类：SQL代码人气：775 回帖：1 发布于6年前收藏

这是原先的sql

SELECT o.*, ifnull(ssc.value,'网站自营') as shopname,
(goods_amount + shipping_fee + insure_fee + pay_fee + pack_fee + card_fee + tax - discount) AS total_fee 
FROM `zhshop`.`ecs_order_info` as o 
LEFT JOIN `zhshop`.`ecs_supplier_shop_config`as ssc 
ON o.supplier_id=ssc.supplier_id AND ssc.code='shop_name' 
WHERE user_id = '1522'  ORDER BY add_time DESC

需要添加一个ecs_back_order表然后查询所有ecs_back_order里面user_id的匹配项之后用ecs_back_order里面的order_id和上面的查询对应

这个是我写的

SELECT o.*, ifnull(ssc.value,'网站自营') as shopname,
(goods_amount + shipping_fee + insure_fee + pay_fee + pack_fee + card_fee + tax - discount) AS total_fee 
FROM `zhshop`.`ecs_order_info` as o 
LEFT JOIN `zhshop`.`ecs_supplier_shop_config`as ssc
LEFT JOIN`ecs_back_order` as ebo
ON o.supplier_id=ssc.supplier_id AND ssc.code='shop_name' AND ebo.order_id=o.order_id 
WHERE ebo.user_id = '1522' 
ORDER BY o.add_time DESC limit 50

怎么写都不行求教~

标签：mysql

讨论这个帖子（1）垃圾回帖将一律封号处理……

Lv3 码奴

凤***奇职业无 6年前#1

感觉你这个语句写得有问题

SELECT o.*, ifnull(ssc.value,'网站自营') as shopname,
(goods_amount + shipping_fee + insure_fee + pay_fee + pack_fee + card_fee + tax - discount) AS total_fee 
FROM `zhshop`.`ecs_order_info` as o 
LEFT JOIN `zhshop`.`ecs_supplier_shop_config`as ssc
ON o.supplier_id = ssc.supplier_id
LEFT JOIN`ecs_back_order` as ebo
on ebo.order_id=o.order_id
WHERE ebo.user_id = '1522' AND ssc.code='shop_name'
ORDER BY o.add_time DESC limit 50

这个是我根据你的代码调整的，你可以试一下。

对了，我在这里说一下，查询的时候，不是left join放一起，而是left join ... on这样放一起，成一对
如：

select tb.* from table as tb
left join table1 as t1 on t1.tb_id = tb.id # 这里是left join ... on组成一对
left join table2 as t2 on t2.tb_id = tb.id # 这里又是left join ... on组成一对

而不是

select tb.* from table as tb
left join table1 as t1
left join table2 as t2
on t1.tb_id = tb.id
on t2.tb_id = tb.id

后面的这种写法会报sql语句错误，亲测是这样，我想你应该能看明白的

文明上网，理性发言！ 😉 阿里云幸运券，戳我领取