Every derived table must have its own alias(sql语句错误解决方法)

1. 在做多表查询,或者查询的时候产生新的表的时候会出现这个错误:Every derived table must have its own alias(每一个派生出来的表都必须有一个自己的别名)。

eg:


delete from stock   
where (org_id,material_id,state) 
in  (SELECT * from 
(select org_id,material_id, state from stock WHERE state = 1 group by org_id,material_id,state having count(*) > 1)
) 

当执行这条sql语句的时候就会出现Every derived table must have its own alias;

2. 这条sql:

(select org_id,material_id, state from stock WHERE state = 1 group by org_id,material_id,state having count(*) > 1)

就会产生一张新的表,和前面的表stock联合查询,但是mysql要求每一个派生出来的表都必须有一个自己的别名,那我给派生表加上别名即可;

eg:修改后的sql,直接在新生产的表中加入 他的别命名就行(“as a”或者“a”),“a”为新表的别名


delete from stock   
where (org_id,material_id,state) in  
(SELECT * from (select org_id,material_id, state 
from stock WHERE state = 1 group by org_id,material_id,state having count(*) > 1) as a ) 

这样就解决了问题;

©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值