SQL server calculates the value of a field in the database, removes duplicate data, and SQL server

SQL server calculates the value of a field in the database, removes duplicate data, and SQL server

Sometimes, some duplicate data exists in the database, but we want to calculate the value of a field, and we need to remove the duplicate data ,:

In the preceding figure, we can see that all four pieces of data with id = 2, 3, id = 4, and 5 are duplicated. We only need one of them to count amount. The SQL statement is as follows:

Select sum (B. money) as 'ant' from (select MAX (amount) as money from test group by name) B

In the SQL server database table, how does one Delete duplicate data based on a field?

I used a cursor to implement your function.

First, create an empty table with the same structure as your operation table, but the table must be empty without any content, such as tempReg2.

Copy the following code to the SQL query analyzer and make some modifications.
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
DECLARE Cursor_Title cursor for select distinct title FROM RegMember

OPEN Cursor_Title

Declare @ str varchar (50)
Fetch next from Cursor_Title Into @ str

WHILE @ FETCH_STATUS = 0
BEGIN
Insert into tempReg2 select top 1 * from RegMember where title = @ str
Fetch next from Cursor_Title Into @ str
END

CLOSE Cursor_Title
DEALLOCATE Cursor_Title
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

The table name I used is RegMember and the duplicate column name is title. Therefore, you need to replace these two names. Others can remain unchanged.

How can I delete all duplicate data with the same fields in the SQL Server database?

Select distinct field 1, Field 2... field N into talbe2 from table1
Drop table1
Rename talbe2 to table1

You can do it in this way.