sqlserver 查询案例详解

 【1】、查询一张表的第二高的薪水（该skinid的值随意指定一个int型）

例1：select NULLIF((select skinid from (SELECT skinid,ROW_NUMBER() OVER (ORDER BY skinid desc) AS rn from (select DISTINCT skinid from Skin) s) g WHERE rn = 2),null) as secondSalary

例2：select Max(case when num =2 then Salary else null end)  SecondHighestSalary FROM (select Salary, row_number() over (order by Salary desc) as num from (select distinct Salary from Employee) a) b

扩展：第N高的薪水

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS

BEGIN

    RETURN (

        /* Write your T-SQL query statement below. */

        select Max(case when num =@N then Salary else null end)  SecondHighestSalary FROM (select Salary, row_number() over (order by Salary desc) as num from (select distinct Salary from Employee) a) b

    );

END

 

【2】、 如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

 select  s.Score,

b.Rank from scores s left join (select score,row_number() over (order by score desc) as Rank from scores group by score) b on b.score = s.score order by Rank asc

 

【3】、编写一个 SQL 查询，查找所有至少连续出现三次的数字。

 select distinct num as ConsecutiveNums from (select Id,Num,lag(Num,1) over (order by Id asc) as Num1,lag(Num,2) over (order by Id asc) as Num2 from Logs)a where num = num1 and num = num2

 

【4】、Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

 select a.Name as Employee from employee a right join employee b on a.managerid = b.Id where a.salary > b.salary

 注：在此用右连接，比left join、join的查询速度都更快 

 

【5】、查找 Person 表中所有重复的电子邮箱。

select Email from Person group by Email having count(Email) > 1

 

【6】、编写一个 SQL 查询，查找所有至少连续出现三次的数字。

select distinct num as ConsecutiveNums from (select Id,Num,lag(Num,1) over (order by Id asc) as Num1,lag(Num,2) over (order by Id asc) as Num2 from Logs)a where num = num1 and num = num2

注：LEAD和LAG函数，获取当前数据上下相邻多少行数据

 

【7】、某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

 

 

select c.Name as Customers from Customers c left join Orders s on c.Id = s.CustomerId where s.Id is null

 

【8】、编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

select Department,Name,Salary from (select e.Id,e.Name,d.Name as Department,e.Salary,dense_rank() OVER (partition by e.departmentid order by e.Salary desc) as cnt from Employee e left join Department d on d.Id = e.departmentid ) t where cnt = 1

 

注：dense_rank()函数，保持了重复的数据RN值相同

  分析性函数：partition  by，用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组

 

 【9】、Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

select Department,Employee,Salary from (select e.Id,e.Name as Employee,d.Name as Department,e.Salary,dense_rank() OVER (partition by e.departmentid order by e.Salary DESC) as cnt from Employee e left join Department d on d.Id = e.departmentid ) t where cnt <= 3

order by Department asc,Salary DESC,Id desc

 

【10】、删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个

DELETE FROM Person WHERE Id IN (SELECT Id FROM (SELECT Id, Email,DENSE_RANK() OVER (PARTITION BY Email ORDER BY Id asc) AS cnt FROM Person)t WHERE t.cnt > 1)

 

 【11】、给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id

 

 例1(链表)：SELECT w2.id FROM Weather w1 INNER JOIN Weather w2 ON w1.RecordDate =DATEADD(d,-1,w2.RecordDate) WHERE w1.Temperature < w2.Temperature

 例2：

SELECT w2.Id FROM Weather w1,Weather w2 WHERE w1.Temperature < w2.Temperature AND DATEDIFF(d,w1.RecordDate,w2.RecordDate)=1