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 )

————————————————
版权声明：本文为CSDN博主「AtticusWX」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_32863631/article/details/83024322