sql语句中select……as的⽤法
原⽂链接如下：
另外⼀个链接感觉也很好，是MySQL的，⼀起贴上来：
谢谢原作者的⾟勤付出，贴过来如下：
有如下sql语句：
【案例1】
SELECT userName  AS a,  userAddress  AS b FROM TestTable WHERE a like'%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_ALLisnull的用法
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
)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。