将Temp列的结果加入表SQL Server

I am using SQL Server to create my database.

我正在使用SQL Server来创建我的数据库。

I want to add a column to my Table which would calculate the number of NULL values in each row, like this:

我想在我的表中添加一列来计算每行中NULL值的数量，如下所示：

Column1 | Column2 | Column3 | Score
a       |   B     |   C     |   0
x       |   NULL  |   NULL  |   2

Currently, I have this:

目前，我有这个：

Column1 | Column2 | Column3
a       |   B     |   C     
x       |   NULL  |   NULL

I have created a new column called Score, and in order to calculate it, I have used:

我创建了一个名为Score的新列，为了计算它，我使用了：

SELECT
CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + 
CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END 
As TMP
FROM MyTable

That returns a column with all my lines and the Score for each line:

这会返回一个包含我所有行的列和每行的分数：

  |TMP
1 |0
2 |2

I would like to update the column Score in myTable with those values.

我想使用这些值更新myTable中的列分数。

Thanks for your help.

谢谢你的帮助。

4 个解决方案

#1

You could use a computed column - a virtual column that is always computed with a given expression, and not stored on disk. This way, you avoid problems with data consistency. The syntax is easy:

您可以使用计算列 - 一个始终使用给定表达式计算的虚拟列，而不是存储在磁盘上。这样，您可以避免数据一致性问题。语法很简单：

CREATE TABLE myTab
(
    column1 datatype
  , column2 datatype
...
  , Score AS CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END 
);

In order to alter the existing table and add such a column, use:

要更改现有表并添加此类列，请使用：

 ALTER TABLE myTab ADD Score AS CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END

Source: https://msdn.microsoft.com/en-us/library/ms188300.aspx

资料来源：https：//msdn.microsoft.com/en-us/library/ms188300.aspx

#2

It's generally a bad idea to store calculated values depending on other columns. (Data redundancy, risk of data inconsistency.) Create a view instead:

根据其他列存储计算值通常是个坏主意。（数据冗余，数据不一致的风险。）创建视图：

create view MyView as
SELECT column1, column2, column3,
       CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
       CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END As TMP
FROM MyTable

#3

UPDATE mt
SET Score = t.Tmp
FROM MyTable mt INNER JOIN (SELECT idx, 
    CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + 
    CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END 
FROM MyTable) t ON t.idx = mt.idx

#4

DECLARE @temp TABLE
(
    Column1 CHAR(1),
    Column2 CHAR(1),
    Column3 CHAR(1),
    Score AS (
        CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + 
        CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
        CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END)
)

要么

CREATE VIEW dbo.vw_Table
AS
    SELECT
          t.Column1
        , t.Column2
        , t.Column3
        , Score = (
            SELECT COUNT(t.val)
            FROM (
                VALUES
                    (t.Column1),
                    (t.Column2),
                    (t.Column3)
            ) t(val)
        )
    FROM dbo.test t
GO

要么

ALTER TABLE dbo.tbl
    ADD Score AS (
        CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + 
        CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END +
        CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END) PERSISTED
GO

#1