Identifies repeated data in a column.

-- Method 1
-- Time: 00: 00: 29.09
Select B. m_no, B. SQ, B. p_roll, B. NPL, B. tr_date from t_tr B,
(Select count (*), m_no, SQ, p_roll, NPL
From t_tr
Group by m_no, SQ, p_roll, NPL
Having count (*)> 1)
Where B. m_no = A. m_no and B. sq = A. SQ and B. p_roll = A. p_roll and B. NPL = A. NPL
Order by B. tr_date

-- Method 2
-- Time used: n seconds, so I had to take off the card.
Select m_no, SQ, p_roll, NPL, tr_date
From t_tr
Where a. rowid! = (Select max (rowid)
From t_tr B
Where a. m_no = B. m_no and A. sq = B. SQ and A. p_roll = B. p_roll and A. NPL = B. NPL)

-- Method 3
-- Time: 5 seconds,
-- Use ORACLE analysis functions

Select m_no, SQ, PT, p_roll, p_yds, p_kgs, tr_date, DEPT from tr
Where (m_no, SQ, nvl (PT, '<null>'), p_roll) in
(Select m_no, SQ, nvl (PT, '<null>'), p_roll from
(Select m_no, SQ, PT, p_roll, row_number () over (partition by m_no, SQ, PT, p_roll order by m_no, SQ, PT, p_roll) Rn
From tr order by m_no, SQ, PT, p_roll) T
Where rn> 1)
And tr_date> to_date ('2017-04-01 ', 'yyyy-mm-dd ')

Summary: the rational use of Oracle analysis functions can greatly improve the efficiency of SQL Execution.

 

Select * from user
Where uid not in (select u1.uid from user as U1, user as U2 "=" here, a not in finds the largest uid. So you finally found the largest ID in the duplicate name.
"= This select is to find all uid1 <uid2 IDS under the current duplicate name. At this time, the ID in uid1 certainly does not include the largest uid,
Where u1.uid <u2.uid and u1.username = u2.username and u1.username in
(Select username from user group by username)

 

 

This SQL statement has finally evolved. Hey. Yes.
Select * from
User u1, (select Min (UID) as minuid, username from user group by username) U2 -- U2 queries out duplicate usernames and obtains the smallest ID in the username group.
Where
U1.uid = u2.minuid and u1.username = u2.username -- Unique ID
You can get a full record that is not repeated. Because the min (ID) ID is not repeated, you can leave it empty. If it is Min (email), there may be duplicate emails.
When the table is connected, the where condition must be added.
Order by u1.uid