我有这个SQL查询mysql的工作很好。查询在2个表中找到,这是两个最大的数值,并返回mysql中的值,我必须在sql server中做同样的查询,但告诉我一个错误语法错误附近转换,预期为
查询是下一个:
SELECT MAX(CAST(RIGHT(nrt, 5) AS UNSIGNED))
FROM
(
SELECT nrt from asp where nrt != ' ' and nrt is not null
UNION ALL
SELECT nrt from asp_historic where nrt != ' ' and nrt is not null
) as subQuery
有一种方法可以让这个查询运行吗?
下面是SQL Server…
的相同代码SELECT MAX(RIGHT(ABS(nrt), 5))
FROM
(
SELECT nrt from asp where nrt <> ' ' and nrt is not null
UNION ALL
SELECT nrt from asp_historic where nrt <> ' ' and nrt is not null
) as subQuery
您在找nrt != ' ' and nrt is not null
吗?
我认为应该是OR
条件而不是AND
条件。
我将从完全不转换开始:
SELECT MAX(RIGHT(nrt, 5)) FROM asp WHERE nrt <> ' ' and nrt is not null
UNION ALL
SELECT nrt FROM asp_historic WHERE nrt M< ' ' and nrt is not null
这里假设nrt
至少有5个字符。
如果不是,则转换为整数:
SELECT CAST(MAX(RIGHT(nrt, 5)) as int) FROM asp WHERE nrt <> ' ' and nrt is not null
UNION ALL
SELECT nrt FROM asp_historic WHERE nrt <> ' ' and nrt is not null
编辑:这个问题似乎是关于mysql隐式转换。我建议:
SELECT MAX(CAST(LEFT(nrt + ' ', PATINDEX(nrt, '%[^0-9]%') - 1) as int))
. . .