面试中常被问到的Sql 查询。

引言

在 .NET/SQL Server 工作面试中经常被问到的 SQL 查询。 这些棘手的查询可能在您日常的数据库使用中需要用到。

背景

本文演示了一些在面试中常被问到的 SQL 查询。 我将涵盖一些常见但棘手的查询，例如：-

(i) 查找员工的第 n 高工资。
(ii) 从每个组中查找前 X 条记录。
(iii) 从表中删除重复行。

注意： 本文提到的所有 SQL 均已在 SQL Server 2005 下测试过。

(i) 查找员工的第 n 高工资。

创建一个名为 Employee_Test 的表，并插入一些测试数据，如下所示：-

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

查找最高工资非常简单，如下所示：-

--Highest Salary
select max(Emp_Sal) from Employee_Test

现在，如果您被要求查找第三高的工资，则查询如下：-

--3rd Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

结果如下：1200
要查找第 n 高工资，请将前 3 名替换为前 n 名（n 为整数 1、2、3 等）。

--nth Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

(ii) 从每个组中查找前 X 条记录

创建一个名为 photo_test 的表，并插入一些测试数据，如下所示：-

create table photo_test
(
pgm_main_Category_id int,
pgm_sub_category_id int,
file_path varchar(MAX)
)

insert into photo_test values
(17,15,'photo/bb1.jpg');     
                                                 
insert into photo_test values(17,16,'photo/cricket1.jpg');                                                    
insert into photo_test values(17,17,'photo/base1.jpg');                                                       
insert into photo_test values(18,18,'photo/forest1.jpg');                                                       
insert into photo_test values(18,19,'photo/tree1.jpg');                                                           
insert into photo_test values(18,20,'photo/flower1.jpg');                                                     
insert into photo_test values(19,21,'photo/laptop1.jpg');                                                       
insert into photo_test values(19,22,'photo/camer1.jpg');                                                 

insert into photo_test values(19,23,'photo/cybermbl1.jpg');                                                    
insert into photo_test values
(17,24,'photo/F1.jpg');

有三个 pgm_main_category_id 组，每个组的值为 17（组 17 有四条记录）、18（组 18 有三条记录）和 19（组 19 有三条记录）。
现在，如果您想从每个组中选择前 2 条记录，则查询如下：-

select pgm_main_category_id,pgm_sub_category_id,file_path from
(
select pgm_main_category_id,pgm_sub_category_id,file_path,
rank() over (partition by pgm_main_category_id order by pgm_sub_category_id asc) as rankid
from photo_test
) photo_test
where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.
order by pgm_main_category_id,pgm_sub_category_id

结果如下：-

pgm_main_category_id	pgm_sub_category_id	file_path
17	                   15	                   photo/bb1.jpg
17	                   16	                   photo/cricket1.jpg
18	                   18	                   photo/forest1.jpg
18	                   19	                   photo/tree1.jpg
19	                   21	                   photo/laptop1.jpg
19	                   22	                   photocamer1.jpg

(iii) 从表中删除重复行

带有主键的表不包含重复项。 但是，由于某种原因，必须禁用键，或者在从其他来源导入数据时，表中会出现重复数据，因此通常需要删除这些重复数据。
可以通过两种方式实现：-
(a) 使用临时表。
(b) 不使用临时表。

(a) 使用临时或暂存表

让表 employee_test1 包含一些重复数据，如下所示：-

CREATE TABLE Employee_Test1
(
Emp_ID INT,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);
INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);
INSERT INTO Employee_Test1 VALUES (3,'John',1100);
INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);
INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

步骤 1： 从主表创建一个临时表，如下所示：-

select top 0* into employee_test1_temp from employee_test1

步骤 2： 将 GROUP BY 查询的结果插入临时表，如下所示：-

insert into employee_test1_temp
select Emp_ID,Emp_name,Emp_Sal
from employee_test1
group by Emp_ID,Emp_name,Emp_Sal

步骤 3： 截断原始表，如下所示：-

truncate table employee_test1

步骤 4： 使用临时表的行填充原始表，如下所示：-

insert into employee_test1
select * from employee_test1_temp

现在，主表中的重复行已被删除。

select * from employee_test1

给出结果如下：-

Emp_ID	Emp_name   Emp_Sal
1	Anees	   1000
2	Rick	   1200
3	John	   1100
4	Stephen	   1300
5	Maria	   1400
6	Tim	   1150

(b) 不使用临时表

;with T as
(
	select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
	from employee_test1
)

delete
from T
where rank > 1

结果如下：-

Emp_ID	Emp_name   Emp_Sal
1	Anees	   1000
2	Rick	   1200
3	John	   1100
4	Stephen	   1300
5	Maria	   1400
6	Tim	   1150

结论

我希望这些查询能帮助您进行面试以及日常数据库活动。