gpt4 book ai didi

mysql - 在另一列中使用别名

转载 作者:可可西里 更新时间:2023-11-01 08:20:55 26 4
gpt4 key购买 nike

这个问题是我之前问题的延伸部分, Finding number position in string .

我有如下表 myTable (myWord ==> varchar(10))

++++++++++++
+ myWord +
++++++++++++
+ AB123 +
+ A413 +
+ X5231 +
+ ABE921 +
+ 15231 +
+ 523 +
+ ABC +
++++++++++++

我想要的是如下。

++++++++++++++++++++++++++++++++
+ myWord + myPos + NewString +
++++++++++++++++++++++++++++++++
+ AB123 + 3 + AB +
+ A413 + 2 + A +
+ X5231 + 2 + X +
+ ABE921 + 4 + ABE +
+ 15231 + 1 + +
+ 523 + 1 + +
+ ABC + 999 + ABC +
++++++++++++++++++++++++++++++++

为了获得上面的输出,我使用了下面的查询。

SELECT 
myWord,
LEAST (
if (Locate('0',myWord) >0,Locate('0',myWord),999),
if (Locate('1',myWord) >0,Locate('1',myWord),999),
if (Locate('2',myWord) >0,Locate('2',myWord),999),
if (Locate('3',myWord) >0,Locate('3',myWord),999),
if (Locate('4',myWord) >0,Locate('4',myWord),999),
if (Locate('5',myWord) >0,Locate('5',myWord),999),
if (Locate('6',myWord) >0,Locate('6',myWord),999),
if (Locate('7',myWord) >0,Locate('7',myWord),999),
if (Locate('8',myWord) >0,Locate('8',myWord),999),
if (Locate('9',myWord) >0,Locate('9',myWord),999)
) as myPos,
if (LEAST (
if (Locate('0',myWord) >0,Locate('0',myWord),999),
if (Locate('1',myWord) >0,Locate('1',myWord),999),
if (Locate('2',myWord) >0,Locate('2',myWord),999),
if (Locate('3',myWord) >0,Locate('3',myWord),999),
if (Locate('4',myWord) >0,Locate('4',myWord),999),
if (Locate('5',myWord) >0,Locate('5',myWord),999),
if (Locate('6',myWord) >0,Locate('6',myWord),999),
if (Locate('7',myWord) >0,Locate('7',myWord),999),
if (Locate('8',myWord) >0,Locate('8',myWord),999),
if (Locate('9',myWord) >0,Locate('9',myWord),999)
)=999,myWord,SUBSTR(myWord,1,LEAST (
if (Locate('0',myWord) >0,Locate('0',myWord),999),
if (Locate('1',myWord) >0,Locate('1',myWord),999),
if (Locate('2',myWord) >0,Locate('2',myWord),999),
if (Locate('3',myWord) >0,Locate('3',myWord),999),
if (Locate('4',myWord) >0,Locate('4',myWord),999),
if (Locate('5',myWord) >0,Locate('5',myWord),999),
if (Locate('6',myWord) >0,Locate('6',myWord),999),
if (Locate('7',myWord) >0,Locate('7',myWord),999),
if (Locate('8',myWord) >0,Locate('8',myWord),999),
if (Locate('9',myWord) >0,Locate('9',myWord),999)
)-1)) as NewString
FROM myTable;

我的问题是

在将列命名为 MyPos 后,为什么我不能在另一列中使用该名称,如下面的查询所示?

SELECT 
myWord,
LEAST (
if (Locate('0',myWord) >0,Locate('0',myWord),999),
if (Locate('1',myWord) >0,Locate('1',myWord),999),
if (Locate('2',myWord) >0,Locate('2',myWord),999),
if (Locate('3',myWord) >0,Locate('3',myWord),999),
if (Locate('4',myWord) >0,Locate('4',myWord),999),
if (Locate('5',myWord) >0,Locate('5',myWord),999),
if (Locate('6',myWord) >0,Locate('6',myWord),999),
if (Locate('7',myWord) >0,Locate('7',myWord),999),
if (Locate('8',myWord) >0,Locate('8',myWord),999),
if (Locate('9',myWord) >0,Locate('9',myWord),999)
) as myPos,
if (myPos=999,myWord,SUBSTR(myWord,1,myPos-1)) as NewString
FROM myTable;

但是,这给了我错误

Unknown column 'myPos' in 'field list':

See here for more details

请建议我如何使用别名缩短此查询

最佳答案

尝试将最少的部分作为子选择。你可以别名。

像这样:

SELECT 
mytable.myWord,
myPos.l,
if (l=999,mytable.myWord,SUBSTR(mytable.myWord,1,l-1)) as NewString
FROM myTable,
(select myword, LEAST (
if (Locate('0',myWord) >0,Locate('0',myWord),999),
if (Locate('1',myWord) >0,Locate('1',myWord),999),
if (Locate('2',myWord) >0,Locate('2',myWord),999),
if (Locate('3',myWord) >0,Locate('3',myWord),999),
if (Locate('4',myWord) >0,Locate('4',myWord),999),
if (Locate('5',myWord) >0,Locate('5',myWord),999),
if (Locate('6',myWord) >0,Locate('6',myWord),999),
if (Locate('7',myWord) >0,Locate('7',myWord),999),
if (Locate('8',myWord) >0,Locate('8',myWord),999),
if (Locate('9',myWord) >0,Locate('9',myWord),999)
) as l from mytable)
as myPos
where myPos.myword = mytable.myword

SQLFiddle example

关于mysql - 在另一列中使用别名,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11156563/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com