SQL Server：对通过查询返回的值执行算术运算

问题描述：

我是一个SQL noob，因此在此寻找一些帮助。我有一个像下面给出的示例查询。现在我想修改查询，这样我可以在同一查询测试，测试1和Test2的值执行下列操作：SQL Server：对通过查询返回的值执行算术运算

OutputValue = Test + Test1 - Test2

然后显示OutputValue与测试，测试1 &的Test2沿结果。

SELECT 
    CAST(balancefwd.Shares AS NUMERIC)/1000 AS Test, 
    SUM(CAST(booksdata.sharesissued AS NUMERIC)/1000) AS Test1, 
    SUM(CAST(booksdata.sharesretired AS NUMERIC)/1000) AS Test2 
FROM 
    SQLTest.TestBooks.dbo.booksdata booksdata 
JOIN 
    SQLTest.TestBooks.dbo.balancefwd balancefwd ON booksdata.companynumber = balancefwd.companynumber 
WHERE 
    booksdata.CompanyNumber= '<number>' 
GROUP BY 
    balancefwd.shares

有什么建议吗？

mysql或sql server 2008在您的查询中读取dbo看起来像您正在使用sql-server 2008.请通过编辑您的问题来更正标记。 –

更改了标签。测试是一个公司的静态值，我已经添加了group by子句 –

答

以下是问题的答案：给出子查询（t1）的名称我能够对派生值执行算术运算。

Select t1.Test, t1.Test1, t1.Test2, t1.Test+t1.Test1-t1.Test2 as OutputValue from 
(SELECT 
    CAST(balancefwd.Shares AS NUMERIC)/1000 AS Test, 
    SUM(CAST(booksdata.sharesissued AS NUMERIC)/1000) AS Test1, 
    SUM(CAST(booksdata.sharesretired AS NUMERIC)/1000) AS Test2 
FROM 
    SQLTest.TestBooks.dbo.booksdata booksdata 
JOIN 
    SQLTest.TestBooks.dbo.balancefwd balancefwd ON booksdata.companynumber = balancefwd.companynumber 
WHERE 
    booksdata.CompanyNumber= '<number>' 
GROUP BY 
    balancefwd.shares) t1

答

您可以在另一个层包裹查询：

SELECT t.*, t.test + t.test1 - t.test2 as total 
FROM (Select cast(balancefwd.Shares as numeric)/1000 as Test, 
      sum(cast(booksdata.sharesissued as numeric)/1000)as Test1, 
      sum(cast(booksdata.sharesretired as numeric)/1000)as Test2 
     from SQLTest.TestBooks.dbo.booksdata booksdata 
     join SQLTest.TestBooks.dbo.balancefwd balancefwd on booksdata.companynumber=balancefwd.companynumber 
     where booksdata.CompanyNumber= '<number>')

然后派生列将可供使用。

尽管您的查询看起来有点不对，您在这里要做什么？为什么没有分组条款？表中只有1条记录？你为什么总结test1和test2而不是test？

SQL Server：对通过查询返回的值执行算术运算

相关推荐