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

Source: Internet
Author: User

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.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.