若要计算sum（）两个别名列-在SQL中 [英] To calculate sum() two alias named columns - in sql

问题描述

要计算在查询中声明的两个临时列名称的sum（）-在SQL中

To calculate sum() of two temp column names declared in query - in SQL

stud 表具有只有两列 m1，m2 。

select 
   m1, m2, 
   SUM(m1) + SUM(m2) as Total,
   SUM(m1) + SUM(m2) as Total1 
from 
   stud 
group by 
   m1, m2

如何计算总和为总和（total）+ sum（total1），其中列名称声明为要执行查询的临时名称。

How to calculate grandtotal as sum(total)+sum(total1) with the column name declared as temp name for the query to execute.

不支持cte重复的列名？

With cte dosn't support duplicate column names?

如何使用它来支持重复的列名

How to make use of it to support duplicate columnname

推荐答案

您不能直接做到这一点-您需要使用CTE（公用表表达式）之类的东西-

You can't do it directly - you need to use something like a CTE (Common Table Expression) - like this:

;WITH sums AS 
(
   SELECT
      m1, m2, 
      SUM(m1) + SUM(m2) as Total,
      SUM(m1) + SUM(m2) as Total1 
   FROM
      dbo.stud 
   GROUP BY
      m1, m2
)
SELECT 
   m1, m2, 
   total, total1, 
   total+total1 AS 'GrandTotal' 
FROM 
   sums

在SQL Server 2005中有效以及更新的版本（以及其他一些支持CTE的数据库系统-这是ANSI标准）。

This works in SQL Server 2005 and newer (and also in some other database systems that support CTE's - which is an ANSI standard).

这篇关于若要计算sum（）两个别名列-在SQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！