时间戳

首页 > 解决方案 > PARTITION BY 多列同时从另一个表插入数据

sql - PARTITION BY 多列同时从另一个表插入数据

问题描述

如何在 SQL Server 中跳过唯一约束错误？

这是我的源表：

CREATE TABLE source 
(
    RollNo INTEGER,
    Nam VARCHAR(6),
    Gender VARCHAR(1),
    Score INTEGER
);
    
INSERT INTO source (RollNo, Nam, Gender, Score)
VALUES ('101', 'John', 'M', '85'),
       ('102', 'Tracy', 'F', '79'),
       ('103', 'Jake', 'M', '92'),
       ('104', 'Edgar', 'M', NULL),
       ('105', 'Monica', 'F', '25'),
       ('106', 'Monica', 'F', '50'),
       ('1070', 'Yash', 'M', '68'),
       ('107', 'Yash', 'M', '70'),
       ('108', 'SFS', 'M', '68'),
       ('18', 'SFS77', 'F', '65');

dest我想从&是唯一键的source表中填充表，并且应该自动递增：nameGenderSeqNo

dest表说明：

CREATE TABLE dest 
(
    SeqNo BIGINT IDENTITY(1000,1) PRIMARY KEY,
    RollNo INTEGER,
    Nam VARCHAR(6),
    Gender VARCHAR(1),
    Score INTEGER
);

这是我尝试过的：

尝试#1：

INSERT INTO dest (RollNo, Nam, Gender, Score) 
    SELECT 
        FIRST_VALUE(RollNo) OVER (PARTITION BY Nam, Gender ORDER BY Score DESC),
        FIRST_VALUE(Nam) OVER (PARTITION BY Nam, Gender ORDER BY Score DESC),
        FIRST_VALUE(Gender) OVER (PARTITION BY Nam, Gender ORDER BY Score DESC),
        FIRST_VALUE(Score) OVER (PARTITION BY Nam, Gender ORDER BY Score DESC)
    FROM 
        source 
    WHERE 
        Nam IS NOT NULL AND Gender IS NOT NULL ;

错误：违反 UNIQUE KEY 约束

尝试#2：

INSERT INTO dest (RollNo, Nam, Gender, Score) 
    SELECT MAX(RollNo),Nam, Gender, MAX(Score) 
    FROM source
    GROUP BY Nam, Gender
    ORDER BY MAX(Score) DESC;

输出：

| SeqNo | RollNo |    Nam | Gender |  Score |
|-------|--------|--------|--------|--------|
|  1000 |    103 |   Jake |      M |     92 |
|  1001 |    101 |   John |      M |     85 |
|  1002 |    102 |  Tracy |      F |     79 |
|  1003 |   1070 |   Yash |      M |     70 |
|  1004 |    108 |    SFS |      M |     68 |
|  1005 |     18 |  SFS77 |      F |     65 |
|  1006 |    106 | Monica |      F |     50 |
|  1007 |    104 |  Edgar |      M | (null) |

如果您看到 yash 行，则它占用了最大的 RollNo。和最大的分数是错误的，我希望它取第一个值，但我不知道该怎么做。

有没有其他方法可以解决排除以上两种方法？

标签： sqlsql-serversql-insertgreatest-n-per-group

解决方案

您可以使用 标识每个 Nam/Gender 元组得分最高的行ROW_NUMBER()，并使用该信息过滤源数据：

INSERT INTO dest (RollNo, Nam, Gender, Score) 
SELECT RollNo, Nam, Gender, Score
FROM (
    SELECT s.*,
        ROW_NUMBER() OVER(PARTITION BY Nam, Gender ORDER BY Score DESC) rn
    FROM source s
) s
WHERE rn = 1

旁注：我建议在目标表中对 Nam/Gender 元组设置唯一约束，因此在插入时总是拒绝潜在的重复：

CREATE TABLE dest (
  SeqNo  BIGINT IDENTITY(1000,1) PRIMARY KEY,
  RollNo INTEGER,
  Name   VARCHAR(6),
  Gender VARCHAR(1),
  Score  INTEGER,
  UNIQUE (Name, Gender)
);

旁注#2：不要在列名周围加上单引号；它们代表标准 SQL 中的文字字符串。

推荐阅读