从子查询将两列加在一起

Question

好的，开始了。这一定是一件很容易的事情，但我所做的只是扯掉我不断萎缩的头发!!!

我有一个SQL查询，大致如下：

Select A.ID,
      A.field
      (select vchr_Number from tbl_two B where B.int_ParentId = A.ID) as 'Number1',
      (select vchr_Number from tbl_three C where C.int_ParentId = A.ID) as 'Number2',
      (Number1 + Number2) as 'Number3' 
From   tbl_Something A

我正在尝试做的是将数字 1 和 Numnber2 加在一起。我确实需要返回所有三个值

由于如果我只使用正常的加法"+"，这两个值都是字符串，它只会连接两个字符串，所以如果 Number1 = 7 和 number2 = 8，那么 Number3 将是 78，而不是 15。我尝试使用 Cast 命令将字符串转换为整数，但不确定语法。

Answer 1

您需要将数据转换为正确的数据类型（在下面的示例中，我使用 INT）：

SELECT T.*,  CONVERT(INT, T.Number1) + CONVERT(INT, T.Number2) as 'Number3' 
FROM (
    SELECT A.ID, A.field, 
        (select vchr_Number
         from tbl_two B
         where B.int_ParentId = A.ID) as 'Number1',
        (select vchr_Number
         from tbl_three C
         where C.int_ParentId = A.ID) as 'Number2',
    From tbl_Something A
) AS T

或

SELECT A.ID, A.field, 
    (select CONVERT(INT, vchr_Number)
     from tbl_two B
     where B.int_ParentId = A.ID) +
    (select CONVERT(INT, vchr_Number)
     from tbl_three C
     where C.int_ParentId = A.ID) as 'SumOfNumbers',
From tbl_Something A

注意：您需要将数据存储为其原始数据类型：数字作为数字，文本作为文本，日期作为日期等。切勿使用文本/char， （n）varchar/来存储数字或日期数据类型！

Answer 2

Select  A.ID,
      A.field,(Number1 + Number2) as 'Number3' 
From
(
Select A.ID,
      A.field
      (select vchr_Number from tbl_two B where B.int_ParentId = A.ID) as 'Number1',
      (select vchr_Number from tbl_three C where C.int_ParentId = A.ID) as 'Number2',
      (Number1 + Number2) as 'Number3' 
From   tbl_Something A
) T1