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)

 

 

  SQL面试题 （二）

有一张工资表，包含三列：员工编号(ID),部门编号(GROUPS)，工资(SALARY)

1. .找到每个部门工资最高的人(包括并列第一)

2. 找到每个部门工资最高的人(只选一个)

SQL语句如下：

DECLARE @G TABLE(ID INT,GROUPS NVARCHAR(20),SALARY MONEY)

INSERT INTO @G

SELECT 1,1,1000

UNION ALL SELECT 2,1,1000

UNION ALL SELECT 3,1,800

UNION ALL SELECT 4,2,2000

-- 1

SELECT *

FROM @G G

WHERE NOT EXISTS(SELECT 1 FROM @G WHERE GROUPS=G.GROUPS AND SALARY>G.SALARY)

 

SELECT G.*

FROM @G G

INNER JOIN

        (

        SELECT GROUPS,MAX(SALARY) AS SALARY

        FROM @G

        GROUP BY GROUPS

        )T

ON      G.GROUPS=T.GROUPS

AND     G.SALARY=T.SALARY

-- 2

SELECT MIN(G.ID),G.GROUPS,G.SALARY

FROM @G G

INNER JOIN

(

SELECT GROUPS,MAX(SALARY) AS SALARY

FROM @G

GROUP BY GROUPS

)T

ON      G.GROUPS=T.GROUPS

AND     G.SALARY=T.SALARY

GROUP BY G.GROUPS,G.SALARY

一定有更好的方法，请不吝赐教。