mysql 多层子查询怎么获取最外层的别名引用

1、首先，在一个查询中的查询结果作为外层查询的条件，可以用IN关键字，代码如下图所示。

2、其次，外层查询的条件不在内层查询结果，可以用NOT IN关键字，代码如下所示。

3、使用EXISTS关键字查询时，内层查询语句不返回查询的记录，而是返回一个Boolean值；当内层查询返回的值为true时，外层查询语句将进行查询，如果返回false时，将不进行查询或查询结果为空，代码如下所示。

4、由第三步可知，与EXISTS相对的是NOT EXISTS，当内层查询返回的值为false时，外层查询语句将进行查询，如果返回true时，将不进行查询或查询结果为空，代码如下。

5、满足其中的任一条件，就可以通过该条件来执行外层查询语句，用关键字ANY，代码如下。

6、满足所有条件，只有满足内层查询语句返回所有结果，代码如下所示，就完成了。

去提是否关联正确问题，细查之下，看到二个不同的as a，这是一条SQL语句中不允许的，所以把下面代码：

SELECT * FROM (SELECT a.intID, a.vhrCode AS 编码, a.vhrName AS 名称, a.vhrShortCode AS 简码,

a.intProItemType, b.vhrName AS 分类, a.intUnit, c.vhrName AS 单位, a.decCost AS 成本价,

a.decOutPrice AS 零售单价, a.intGainType, d.vhrName AS 提成方式, a.decGainValue AS 提成参数,

a.bitIsStop, case when a.bitIsStop then `停用` else `启用` end AS 使用状态, a.vhrRemark AS 说明,

a.intDataType AS 数据类型

FROM ((ProductItem AS a LEFT JOIN BaseData AS b ON a.intProItemType=b.intID) LEFT JOIN BaseData AS c ON a.intUnit=c.intID)

LEFT JOIN BaseData AS d ON a.intGainType=d.intID

WHERE (((a.intDataType)=0))ORDER BY a.vhrCode) AS a

改为下面代码：

SELECT * FROM (SELECT a.intID, a.vhrCode AS 编码, a.vhrName AS 名称, a.vhrShortCode AS 简码,

a.intProItemType, b.vhrName AS 分类, a.intUnit, c.vhrName AS 单位, a.decCost AS 成本价,

a.decOutPrice AS 零售单价, a.intGainType, d.vhrName AS 提成方式, a.decGainValue AS 提成参数,

a.bitIsStop, case when a.bitIsStop then `停用` else `启用` end AS 使用状态, a.vhrRemark AS 说明,

a.intDataType AS 数据类型

FROM ((ProductItem AS a LEFT JOIN BaseData AS b ON a.intProItemType=b.intID) LEFT JOIN BaseData AS c ON a.intUnit=c.intID)

LEFT JOIN BaseData AS d ON a.intGainType=d.intID

WHERE (((a.intDataType)=0))ORDER BY a.vhrCode)

---