sql - 是否可以在一列上连接两个表 = 串联 2 列？

Question

表 A 有 X 列，它是由表 B 中的 Y 列和 Z 列(均为 float )串联而成的 int。我想以类似于此的方式连接表 A 和 B:

select *
from tableA a inner join tableB b
on a.X = b.cast(concat(cast(b.Y as varchar), cast(b.Z as varchar)) as integer

除此之外，显然，我的示例没有正确完成。

Answer 1

你可以这样做:

select * 
from tableA a 
inner join tableB b 
on a.X = cast(cast(b.Y as varchar) + cast(b.Z as varchar) as int)

如果您的任何一个 float 都有小数点，则转换为 int 将失败。

例如，这有效:

declare @f1 as float
declare @f2 as float
set @f1 = 1
set @f2 = 7
select cast(cast(@f1 as varchar) + cast(@f2 as varchar) as int)

输出: 17

但这不是:

declare @f1 as float
declare @f2 as float
set @f1 = 1.3
set @f2 = 7
select cast(cast(@f1 as varchar) + cast(@f2 as varchar) as int)

输出: 将 varchar 值 '1.37' 转换为数据类型 int 时转换失败。