删除表中的冗余数据 [英] Remove redundant data in a table

问题描述

你好，



我合并了两张桌子，我被要求展示清洁过程！所以我有两个实际上是一个客户，让我们说戴维约翰逊，除了帐户金额和贷款限额之外，其他数据相同。我已经为PK定义了新的自动增量值（所以它们有不同的PK）。



我想删除一行，但在剩下的行中总和两个名字的金额，并计算贷款限额的平均值。



这意味着我将留在一个大卫约翰逊与帐户总金额（ amount1 + amount2）和平均贷款限额[（loanlimit1 + loanlimit2）/ 2]



我不怎么写这个sql脚本！任何想法？



谢谢

Hello,

I have merged two tables, and I have been asked to show the cleaning process! So I have two clients that are one in reality, let's say David Johnson, with same data except amount in account and loan limit that differ. I have defined new auto-increment values for the PK (so they have different PKs).

I want to remove one row but have in the remaining row the sum of the amount in account for the two names and make an average of the loan limit.

Which means that I will stay with one David Johnson with the total amount in account (amount1+amount2) and an average of loan limit [(loanlimit1+ loanlimit2)/2]

I don't how to write this sql script! any idea?

Thank you

推荐答案

我不知道你到底尝试了什么，但这是学习SQL的一个很好的开端： W3 Schools：SQL Tutorial

[ ^ ]



另外，您可以直接在合并过程中完成此操作，因为现在您需要删除一个冗余行。



无论如何，尝试这种方法。



首先创建一个存储过程clientName作为in参数并在其中使用此代码

I don't know what you have tried to far, but this is a pretty good start into learning SQL: W3 Schools: SQL Tutorial
[^]

On a side note, you could have done this in your merging process directly, because now you have one redundant row you need to delete.

Anyway, try this approach.

First create a stored procedure the clientName as the in parameter and use this code inside it

SELECT sum(AccountAmount), avg(LoanLimit) INTO @AccountAmount, @LoanLimit FROM mergedtable WHERE ClientName = 'David Johnson';

UPDATE mergedtable SET AccountAmount = @AccountAmount, LoanLimit = @LoanLimit WHERE ID=(SELECT ID FROM mergedtable WHERE ClientName = 'David Johnson' LIMIT 1); -- Updates the first appearance of David

-- Deletes the second appearance of David
DELETE FROM mergedtable WHERE ID=(SELECT ID FROM mergedtable WHERE ClientName = 'David Johnson' LIMIT 1, 1);

只要确保在多个同名客户端的情况下获得独特的客户端。

也许添加一个电话号码或其他内容。



还有其他方法可以做到这一点。

例如你可以使用临时表或嵌套的选择语句s，但这种方式对于初学者来说很容易理解。



[更新]

不知道为什么嵌套的SELECT不起作用在存储过程中。

我尝试了这种方法而且这样做。

Just make sure you get the unique client in case of multiple clients with the same name.
Maybe add a phone number or something into the mix.

There are other ways to do it as well.
For example you can use a temporary table or nested select statments, but this way is easy to understand for a beginner.

[UPDATE]
Not sure why the nested SELECT doesn't work inside a stored procedure.
I tried this approach instead and this works.

CREATE PROCEDURE `RemoveDuplicateClients`(IN _clientName VARCHAR(45))
BEGIN
    SELECT sum(AccountAmount), avg(LoanLimit) INTO @AccountAmount, @LoanLimit FROM mergedtable WHERE ClientName = _clientName;
    
    SELECT ID INTO @clientID FROM mergedtable WHERE ClientName = _clientName LIMIT 1;
    UPDATE mergedtable SET AccountAmount = @AccountAmount, LoanLimit = @LoanLimit WHERE ID = @clientID;
    
    SELECT ID INTO @clientID FROM mergedtable WHERE ClientName = _clientName LIMIT 1, 1;
    DELETE FROM mergedtable WHERE ID = @clientID;
END

CALL RemoveDuplicateClients('David Johnson');

这篇关于删除表中的冗余数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！