在Transact-SQL上：可以构建一个语句来创建一个新列，同时使用来自同一个表的另一个列的数据。

On Transact-SQL: Would like to create a statement that adds a column to my current active table, the newly added column would take data from another column of the same table. It will add the data on each row and sum it up on the new column.

在Transact-SQL上:想要创建一个将列添加到当前活动表的语句,新添加的列将从同一个表的另一列中获取数据。它将在每一行上添加数据并在新列上汇总。

To be more specific,

更具体,

I work for a company that produces Sweet Potato fries, we use labels to describe the content of each box.

我在一家生产甘薯薯条的公司工作,我们使用标签来描述每个盒子的内容。

I have a table that gets updated and populated when a label gets printed, the label goes on each box; So number of labels == number of boxes we produce. The table has a column that describes the weight of each box; the column being named [CaseWeight].

我有一个表格,当标签被打印时,它会被更新和填充,每个盒子上都有标签;所以标签数量==我们生产的盒子数量。该表有一列描述每个盒子的重量;该列名为[CaseWeight]。

I want to take this CaseWeight column do a Sum([CaseWeight]) on a new column and call it total pounds. While still been able to see the rest of the columns in the table.

我想把这个CaseWeight列在一个新列上做一个Sum([CaseWeight])并称之为总磅数。虽然仍然可以看到表中的其余列。

2 个解决方案

#1

You can use a view with a window function:

您可以使用具有窗口功能的视图:

create view v_t as
    select t.*, sum(weight) over () as total_pounds
    from t;

You cannot put a window function in as a generated column, so this is probably the best approach.

您不能将窗口函数作为生成列放入,因此这可能是最好的方法。

#2

To add a column to a table, you may need to drop the table, depending on the server settings. What I do in that case is back the data of the table up into another table (SELECT * INTO MyTableBackup FROM MyTable). Then I drop the table and recreate it with the new column (NOTE: When dropping and recreating the tables, don't forget to recreate the indexes and keys as well!). Then I do an insert from the backup table to the new one. You would do the Group By on the insert when summing. So if you wanted the sum of the weight of each package in the order, you would do:

要向表中添加列,可能需要删除表,具体取决于服务器设置。在这种情况下我做的是将表的数据备份到另一个表(SELECT * INTO MyTableBackup FROM MyTable)。然后我删除表并使用新列重新创建它(注意:删除并重新创建表时,不要忘记重新创建索引和键!)。然后我从备份表插入到新表。在求和时,您可以在插入上执行Group By。因此,如果您想要订单中每个包裹的重量总和,您可以:

    SELECT SUM(CaseWeight) AS TotalPounds FROM MyTableBackup GROUP BY OrderNumber

You can put that in the middle of the select like this:

您可以将它放在选择的中间,如下所示:

    INSERT INTO MyTable
    SELECT
        bak.OrderNumber
        ,bak.NumberOfCases
        ,bak.CaseWeight
        ,(SELECT SUM(CaseWeight) FROM MyTableBackup b WHERE b.OrderNumber = bak.OrderNumber GROUP BY OrderNumber) AS TotalPounds
    FROM MyTableBackup bak

Just make sure you test the select part of your query before doing the DROP and INSERT.

只需确保在执行DROP和INSERT之前测试查询的选择部分。

#1