sqlserven 的以b表的内容作为条件,使用delete命令删除a表的记录时遇到的别名问题

今天在写删除语句的时候,碰到了表的别名问题

代码为

delete FROM xt_operate_log as a 
where 
exists( select 1 FROM xt_service as b
where SUBSTRING(LEFT(a.OPER_PARA, CHARINDEX('&', a.OPER_PARA)-1), CHARINDEX('=', LEFT(a.OPER_PARA, CHARINDEX('&', a.OPER_PARA)-1))+1,LEN(LEFT(a.OPER_PARA, CHARINDEX('&', a.OPER_PARA)-1)))  =b.SERVICE_KEY
and b.is_log ='0')

报错 原因是a表有别名

将语句修改为

DELETE 
FROM
	xt_operate_log 
WHERE

	SUBSTRING (
		LEFT ( OPER_PARA, CHARINDEX( '&', OPER_PARA ) - 1 ),
		CHARINDEX( '=', LEFT ( OPER_PARA, CHARINDEX( '&', OPER_PARA ) - 1 ) ) + 1,
	LEN( LEFT ( OPER_PARA, CHARINDEX( '&', OPER_PARA ) - 1 ) ) 
	) IN ( SELECT SERVICE_KEY FROM xt_service WHERE is_log = '0' )

即可正常运行