从另一行复制值 [英] Copying values from another row

问题描述

我有一张看起来像这样的表格:

I have a table that looks like this:

  ID               AMID           DESC                  value 
  -----------------------------------------------------------------
  100              type A         AMID type A             10
  101              type B         AMID type B             18
  102              type C         AMID type C             34
  101               null            null                   4
  102               null            null                  19
  103              type D         AMID type D              6   
  103              type E            null                  7 

该表包含大约 600 万行.

The table contains around 6 million rows.

现在我想要这样的结果

  ID               AMID           DESC                  value 
  -------------------------------------------------------------
  100              type A         AMID type A             10
  101              type B         AMID type B             18
  102              type C         AMID type C             34
  101              type B         AMID type B              4
  102              type C         AMID type C             19
  103              type D         AMID type D              6   
  103              type E            null                  7

当两行中 ID 的 AMID 相等时，它显示相同的值，其中一个为空，如果 AMID 不同，则保持原样..

It has show the same values when the AMID for ID in both rows are equal are one of it is null, if the AMID is different then leave it like that..

在此先感谢您的帮助..

Thanks in advance for the help..

干杯，

苦涩

推荐答案

首先，让我们设置一些适当的 DDL 和示例数据:

First, let's set up some proper DDL and sample data:

USE tempdb;
GO

CREATE TABLE dbo.AMID
(
  ID     INT, 
  AMID   VARCHAR(32), 
  [DESC] VARCHAR(32), 
  value  INT
);

INSERT dbo.AMID SELECT 100, 'type A', 'AMID type A', 10
      UNION ALL SELECT 101, 'type B', 'AMID type B', 18
      UNION ALL SELECT 102, 'type C', 'AMID type C', 34
      UNION ALL SELECT 101,  null   , null         , 4
      UNION ALL SELECT 102,  null   , null         , 19
      UNION ALL SELECT 103, 'type D', 'AMID type D', 6   
      UNION ALL SELECT 103, 'type E', null         , 7;

不是使用没有 ORDER BY 的 TOP，我什至不知道这意味着什么，您可以选择一行来使用分区 + 顺序填充 NULL 行.在您有限的样本数据中，任何 ID 最多只有两行，但如果您可以拥有更多行，这很重要(否则，就像没有 ORDER BY 的 TOP，您将拉一些任意行，进行更新低于预期).这按 AMID 列中的值排序，但您可以更改此设置以使用表中的任何条件选择每个 ID 的第一行.

Instead of using TOP without ORDER BY, which I don't even know what it means, you can pick exactly one row to use for populating the NULL rows using partition + order. In your limited sample data you only have a max of two rows for any ID, but in case you can have more, this is important (otherwise, like TOP without ORDER BY, you're going to pull some arbitrary row, making your update less than predictable). This orders by the value in the AMID column, but you can change this to pick the top row per ID using any criteria in the table.

;WITH src AS 
(
  SELECT ID, AMID, [DESC]
  FROM dbo.AMID 
  WHERE AMID IS NULL AND [DESC] IS NULL
),
nv AS 
(
  SELECT ID, AMID, [DESC], rn = ROW_NUMBER() OVER
  (PARTITION BY ID ORDER BY AMID) -- change this ordering accordingly
  FROM dbo.AMID 
  WHERE AMID IS NOT NULL
)
UPDATE src
  SET AMID = nv.AMID, [DESC] = COALESCE(nv.[DESC], src.[DESC])
FROM src INNER JOIN nv
ON src.ID = nv.ID
WHERE nv.rn = 1;

SELECT ID, AMID, [DESC], Value FROM dbo.AMID;

结果:

ID   AMID    DESC         value
---  ------  -----------  -----
100  type A  AMID type A  10
101  type B  AMID type B  18
102  type C  AMID type C  34
101  type B  AMID type B  4
102  type C  AMID type C  19
103  type D  AMID type D  6
103  type E  NULL         7     

别忘了清理:

DROP TABLE dbo.AMID;
GO

顺便说一句，DESC 是一个可怕的列名称，因为它是一个 T-SQL 关键字，因此 总是 必须用 [双引号].

As an aside, DESC is a horrible name for a column, since it's a T-SQL keyword, and therefore always has to be escaped with [double quotes].

这篇关于从另一行复制值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！