我有一个用户表,一个订单表和一个城市名称表,这是cities_translation。在users表中,我有用户id、名、姓、城市id和代表id。代表只是同一表中的另一个用户。我要做的是将这些表连接起来得到这样一个表
user id first name last name city name representative wholesaler
1 foo bar city1 2 yes
2 user user city2 0 no
预期结果为
user id first name last name order id city name representative
1 foo bar 1 city1 user
查询:Select
orders.id, orders.user_id, orders.total, orders.final_total, orders.order_status_id,
orders.unix_time, u.id,u.email, u.first_name, u.last_name,m.first_name as rep_firstname,
m.last_name as rep_lastname, u.representative_id,u.city_id,cities_translation.*
From orders,cities_translation,users u
left join cities_translation
Where u.city_id = cities_translation.city_id
And orders.user_id = u.id
And cities_translation.lang_id='2'
And orders.order_status_id='1'
Left join users r on u.representative_id = r.id
Group by orders.user_id
Limit 5
但是结果是:"#1066 - Not unique table/alias: 'cities_translation'"那么如何重写此查询以避免此错误并获得每个批发商的代表性名称
您正在混合显式和隐式连接
From orders,cities_translation,users u
left join cities_translation
,应该采用适当的显式连接格式。
无论如何,错误消息与您使用两个时间表cities_translation
(在from和左连接中)有关
如果您需要这个表两次,您应该使用正确的别名来引用表的每个单独的实例,并使用正确的别名作为相关列名的前缀
From orders,cities_translation as a ,users u
left join cities_translation as b
根据错误提示,您必须为要连接两次的表创建一个唯一的别名。
Select
orders.id, orders.user_id, orders.total, orders.final_total, orders.order_status_id,
orders.unix_time, u.id,u.email, u.first_name, u.last_name,m.first_name as rep_firstname,
m.last_name as rep_lastname, u.representative_id,u.city_id,cities_translation.*
From orders,cities_translation ct1,users u
left join cities_translation ct2
ON u.city_id = ct1.city_id /** you probably wanted ON here INSTEAD OF WHEN*/
And orders.user_id = u.id
And ct2.lang_id='2'
And orders.order_status_id='1'
Left join users r on u.representative_id = r.id
Group by orders.user_id
Limit 5
注意适当地更改ct1和ct2。
:习惯上,SQL保留字用大写,列表名用小写。
您正在混合显式和隐式连接。当您稍后阅读代码时,这总是会导致混乱。尽可能使用显式连接。
这种形式的GROUP BY将不能在mysql 5.7+