CREATE TABLE Product
(
ID INT IDENTITY (PRIMARY) KEY is not NULL,
Pid INT not NULL,
Pname VARCHAR () not NULL,
Punit CHAR (Ten) is not NULL,
Pspec VARCHAR (50),
Pbarcode VARCHAR (20),
)
INSERT into Product (Pid,pname,punit,pspec,pbarcode) VALUES (10000, ' l ' oreal day fix ', ' bottle ', ' 50ML ', ' 1975126589653 ')
INSERT into Product (Pid,pname,punit,pspec,pbarcode) VALUES (10001, ' l ' oreal night fix ', ' bottle ', ' 50ML ', ' 1975126589643 ')
INSERT into Product (Pid,pname,punit,pspec,pbarcode) VALUES (10002, ' Clinique ', ' box ', ' 150ML ', ' 1545126589653 ')
INSERT into Product (Pid,pname,punit,pspec,pbarcode) VALUES (10003, ' hundred percent ', ' bottle ', ' 250ML ', ' 2575126589653 ')
INSERT into Product (Pid,pname,punit,pspec,pbarcode) VALUES (10004, ' European face Wash ', ' bottle ', ' 80ML ', ' 1275126589653 ')
INSERT into Product (Pid,pname,punit,pspec,pbarcode) VALUES (10005, ' Edidas ', ' bottle ', ' 40ML ', ' 1975126589653 ')
INSERT into Product (Pid,pname,punit,pspec,pbarcode) VALUES (10006, ' SK2 ', ' bottle ', ' 20ML ', ' 1975126589653 ')
Find:1 、--Check the duplicate values of a column (or columns) (only the value of the duplicate record can be found, not the entire recorded information)
--such as: Find barcode Duplicate records
Select Pbarcode from Product
GROUP BY Pbarcode
Having (count (*)) >1 2 、--find duplicate barcode items
SELECT * FROM Product
where Pbarcode in (
Select Pbarcode from Product
GROUP BY Pbarcode
Having (count (*)) >1
ORDER BY Pid--The following can query how many keywords are the same as Select Pbarcode,count (pbarcode) from product
GROUP BY Pbarcode have Count (pbarcode) >=2; 3 、--Find Duplicate barcode Item Two, the result is the same as above by Quan
SELECT * FROM product where Pbarcode in
(Select Pbarcode from
(select P1. Pbarcode,count (P1. Pbarcode) number from the Product P1, (select DISTINCT (pbarcode) from product) P2 where
P1. Pbarcode=p2. Pbarcode GROUP by P1. Pbarcode) A1 where number>1)
Transferred from: http://blog.csdn.net/eastlift/archive/2006/12/23/1456984.aspx
SQL Lookup Duplicate Record
