SQL面试题 (一)

 

SQL面试题 (一)

有一个张员工工资表(SALARY),表有三列：员工编号(ID),工资(SALARY)

1.查询id重复记录

2.删除id重复记录，只保留第一条(说明，不需要考虑表中存在完全相同纪录的情况)

SQL语句如下：

CREATE TABLE SALARY(

              ID INT NOT NULL,

               SALARY MONEY NOT NULL

)

INSERT INTO SALARY

SELECT 1,1000

UNION ALL SELECT 1,800

UNION ALL SELECT 2,1200

UNION ALL SELECT 3,1100

UNION ALL SELECT 3,1200

UNION ALL SELECT 3,1300

SELECT * FROM SALARY

-- 1

SELECT *

FROM SALARY

WHERE ID IN

              (

              SELECT ID

              FROM SALARY

              GROUP BY ID

              HAVING COUNT(*)>1

              )

-- 2

DELETE T

FROM SALARY T

WHERE EXISTS(SELECT 1 FROM SALARY WHERE T.ID=ID AND SALARY>T.SALARY)