SQL query duplicate records

This article describes three SQL statements for querying duplicate records and deleting duplicate records. Each of them uses different methods to illustrate other functions, feasibility, and efficiency.

This article describes three SQL statements for querying duplicate records and deleting duplicate records. Each of them uses different methods to illustrate other functions, feasibility, and efficiency.

This article introduces three modelsSQL statement used to query duplicate records and delete duplicate recordsAnd each uses different methods to illustrate other functions, feasibility, and efficiency.

Select *, count (distinct name) from table group by name

Result:

Id name count (distinct name)
1 a 1
2 B 1
3 c 1

Method 2
Table table1
Id regname postionsn personsn
1 Shandong Qilu pharmaceutical 223 2
2 Shandong Qilu pharmaceutical 224 2
3 Beijing Urban Construction Company 225 2
4 technology companies 225 2

The result I want to obtain is

Id regname postionsn personsn
1 Shandong Qilu pharmaceutical 223 2
3 Beijing Urban Construction Company 225 2
4 technology companies 225 2

Select distinct regname, postionsn, personsn from table1

If multiple columns of distinct are queried, do not use the same

Only group

Use group by regname
Select * from table1 where id in (select min (id) from table1 group by regname) and personsn = 2

Instance 3

Personal tables:
Student No. Score
01 01 80
01 02 79
01 03 88
02 01 87
02 02 77
02 03 68

Use SQL to convert the table:
Course No. 01 Lesson 02 Lesson 03
01 80 79 88
02 87 77 68

--------- Create a table ----------
Create table tab_score
(
Bid int identity (0, 1) primary key, -- serial number
Sid varchar (20) not null, -- Student ID
Cid varchar (20) not null, -- course No.
Score int -- score
)
Insert into tab_score select 's01 ', 'c01', '90' union all select 's01', 'c01', '92' union all select 's01', 'c03 ', '93'
Union all select 's02', 'c01', '81 'union all select 's02', '02', '82'

/* --- Fixed column writing. The subsequent writing will be based on several course IDs to dynamically assemble the sum statement in the middle, and then add the header and tail to it,
After understanding this 'static 'writing method, the rest is the work of 'dynamic' assembling the sum statement in the middle ----*/

Select * from tab_score
Select sid, sum (case cid when 'c01' then score else '0' end) as 'c01 ',
Sum (case cid when '02' then score else '0' end) as '02 ',
Sum (case cid when 'c03' then score else '0' end) as 'c03'
From tab_score group by sid

----- 'Dynamic 'column writing, define a variable to assemble the sum statement in the middle, and use the subquery table (repeated columns will appear until this method is used )-------

Declare @ s varchar (1000)
Set @ s =''
Select @ s = @ s + ', sum (case cid when' + ''' +. cid + '''' + ''then score else ''0'' end) as + '''' +. cid + ''''
From (select distinct cid from tab_score)
Print @*
** Ec ('select Sid' + @ s + 'from tab_score group by Sid ')