时间戳

首页 > 解决方案 > 如何对重复行执行 sql 操作/查询

sql - 如何对重复行执行 sql 操作/查询

问题描述

我有 2 张桌子：

1- brokers（这是一家可能有多个经纪人个人的公司）

和

2- brokerIndividuals（具有所属经纪公司外键和个人详细信息的个人/个人表）

我正在尝试为brokers字段companyName唯一且isDeleted为 NULL 的表创建唯一索引列。目前，该表已经填充，所以我想编写一个 SQL QUERY 来查找重复的行，并且每当有相同的行时companyName，isDeleted=NULL我想执行 2 个操作/查询：

1-保持第一行不变，并将其他重复项（第一个重复项之后的行）行的isDeleted列值更改为true.

brokerIndividuals2-为第一行的重复行关联或更改外键。

我正在尝试做的口头描述是：软删除重复的行并将它们对应brokerIndividuals于第一次出现的重复。表需要出现 1 次companyNamewhere isDeletedis NULL。

我正在使用 knex.js ORM，所以如果有帮助，您还可以建议使用 knex 函数的解决方案，但 knex 还不支持部分索引（Knex.js - 如何使用 'where' 子句创建唯一索引？）所以我必须使用原始 SQL 方法。加上我使用的数据库是 mssql（版本：6.0.1）。

标签： sqlsql-serverknex.js

解决方案

这是一个完整的测试用例（已注释），带有指向小提琴的链接：

工作测试用例，使用 MySQL 5.5、5.6、5.7、8.0 和最高 10.6 的 MariaDB 进行测试

创建表并插入具有重复company_name条目的初始数据：

CREATE TABLE brokers (
     id           int  primary key auto_increment
   , company_name VARCHAR(30)
   , isDeleted    boolean default null
);

CREATE TABLE brokerIndividuals (
     id           int  primary key auto_increment
   , broker_id    int references brokers (id)
);

INSERT INTO brokers (company_name) VALUES
     ('name1')
   , ('name1')
   , ('name1')
   , ('name1')
   , ('name123')
   , ('name123')
   , ('name123')
   , ('name123')
;

INSERT INTO brokerIndividuals (broker_id) VALUES
    (2)
  , (7)
;

SELECT * FROM brokers;

+----+--------------+-----------+
| id | company_name | isDeleted |
+----+--------------+-----------+
|  1 | name1        |      null |
|  2 | name1        |      null |
|  3 | name1        |      null |
|  4 | name1        |      null |
|  5 | name123      |      null |
|  6 | name123      |      null |
|  7 | name123      |      null |
|  8 | name123      |      null |
+----+--------------+-----------+


SELECT * FROM brokerIndividuals;

+----+-----------+
| id | broker_id |
+----+-----------+
|  1 |         2 |
|  2 |         7 |
+----+-----------+

调整经纪人以确定 isDeleted 基于MIN(id)per company_name：

UPDATE brokers
  JOIN (
         SELECT company_name, MIN(id) AS id
           FROM brokers
          GROUP BY company_name
       ) AS x
    ON x.company_name = brokers.company_name
   AND isDeleted IS NULL
   SET isDeleted = CASE WHEN (x.id <> brokers.id) THEN 1 END
;

更新的经纪人内容：

SELECT * FROM brokers;

+----+--------------+-----------+
| id | company_name | isDeleted |
+----+--------------+-----------+
|  1 | name1        |      null |
|  2 | name1        |         1 |
|  3 | name1        |         1 |
|  4 | name1        |         1 |
|  5 | name123      |      null |
|  6 | name123      |         1 |
|  7 | name123      |         1 |
|  8 | name123      |         1 |
+----+--------------+-----------+

对于brokerIndividuals，查找/设置正确的broker_id：

UPDATE brokerIndividuals
  JOIN brokers AS b1
    ON b1.id = brokerIndividuals.broker_id
  JOIN brokers AS b2
    ON b1.company_name = b2.company_name
   AND b2.isDeleted IS NULL
   SET brokerIndividuals.broker_id = b2.id
;

新内容：

SELECT * FROM brokerIndividuals;

+----+-----------+
| id | broker_id |
+----+-----------+
|  1 |         1 |
|  2 |         5 |
+----+-----------+

推荐阅读