MySQL中使用表别名与字段别名

有如下sql语句： 【案例1】 SELECT userName AS a, userAddress AS b FROM TestTableWHEREalike’%am%’ 该语句执行是会报错，因为别名只是对字段的一种引用，不能当作字段一样使用，如果真要把别名当字段一样使用，可以如下变通： SELECT a, b FROM( SELECT userName AS a, userAddress AS b FROM TestTable ) AS tempTable WHERE a like ‘%dd%’ 如此，便可把别名当字段一样使用了 但是需要和足以紫色字体部分，该处要给内嵌的select查询指定一个别名，否则会报错

【案例2】 SELECT coutDay AS a, month1 AS b, (select 信用天数 from PayDays where 自定义值 = month1) AS c, (a+ c) AS ‘total’ FROM TestTable 变通为： SELECT a, b, a + c AS ‘total’ FROM( SELECT coutDay AS a, month1 AS b, (select 信用天数 from PayDays where 自定义值 = month1) AS c FROM TestTable ) AS tempTable

【案例3】 下面的sql语句中中，灰色部分重复了两次，第二个灰色部分有不能用第一个灰色区域的别名CountDays替换，一旦替换会出现语法错误。

SELECT cCustCode As 客户代码, cPayWay As 付款条件, cPayCode As 付款方式, CASE WHEN cPayWay like ‘%月结%’ THEN (datediff(day, dOutDate, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,dOutDate)+1, 0))) + (select 信用天数 from PayDays where 自定义值 = cPayWay)) ELSE (select 信用天数 from PayDays where 自定义值 = cPayWay) END As CountDays, Convert(Varchar(12),dOutDate, 102) As 出库日期, Convert(Varchar(12),DateAdd(Day, Cast( (CASE WHEN cPayWay like ‘%月结%’ THEN (datediff(day, dOutDate, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,dOutDate)+1, 0))) + (select 信用天数 from PayDays where 自定义值 = cPayWay)) ELSE (select 信用天数 from PayDays where 自定义值 = cPayWay) END) AS int), dOutDate), 102) AS 实际回款日期 FROM ODS.F_DISPATCHLIST_ALL where (isum-isnull(nSumARAmt,0))>10e-8

我们把第二个灰色部分提出来，改为

SELECT 客户代码,付款条件, 付款方式,CountDays,出库日期,Convert(Varchar(12),DateAdd(Day, Cast(CountDays)AS int), dOutDate), 102)AS 实际回款日期 FROM( SELECT cCustCode As 客户代码, cPayWay As 付款条件, cPayCode As 付款方式, CASE WHEN cPayWay like ‘%月结%’ THEN (datediff(day, dOutDate, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,dOutDate)+1, 0))) + (select 信用天数 from PayDays where 自定义值 = cPayWay)) ELSE (select 信用天数 from PayDays where 自定义值 = cPayWay) END As CountDays, Convert(Varchar(12),dOutDate, 102) As 出库日期 FROM ODS.F_DISPATCHLIST_ALL where (isum-isnull(nSumARAmt,0))>10e-8 )