SQL语句练习实例之一——找出最近的两次晋升日期与工资额

代码如下: –程序员们在编写一个雇员报表，他们需要得到每个雇员当前及历史工资状态的信息， –以便生成报表。报表需要显示每个人的晋升日期和工资数目。 –如果将每条工资信息都放在结果集的一行中，并让宿主程序去格式化它。 –应用程序的程序员都是一帮懒人，他们需要在每个雇员的一行上得到当前 –和历史工资信息。这样就可以写一个非常简单的循环语句。 —示例： create table salaries ( name nvarchar(50) not null, sal_date date not null, salary money not null, ) go ALTER TABLE [dbo].sal 在这个SQL语句练习实例中，我们关注的是如何从一个名为`salaries`的表中提取每个员工最近两次的晋升日期及其对应的工资数额。这个表包含了员工姓名（`name`）、晋升日期（`sal_date`）和工资金额（`salary`）等字段。以下是三种不同的SQL查询方法来解决这个问题： **方法一：使用LEFT JOIN** 对于SQL Server 2000及以上版本，可以使用LEFT JOIN来找到每个员工的最近两次晋升日期和工资。通过子查询找到每个员工的最大晋升日期（`maxdate`），然后再找到第二个最大晋升日期（`maxdate2`）。接着，通过LEFT JOIN连接`salaries`表，将相应的日期和工资匹配到员工记录上。 ```sql SELECT b.name, b.maxdate, y.salary, b.maxdate2, z.salary FROM ( SELECT a.name, a.maxdate, MAX(x.sal_date) AS maxdate2 FROM ( SELECT w.name, MAX(w.sal_date) AS maxdate FROM salaries AS w GROUP BY w.name ) AS a LEFT OUTER JOIN salaries AS x ON a.name = x.name AND a.maxdate > x.sal_date GROUP BY a.name, a.maxdate ) AS b LEFT OUTER JOIN salaries AS y ON b.name = y.name AND b.maxdate = y.sal_date LEFT OUTER JOIN salaries AS z ON b.name = z.name AND b.maxdate2 = z.sal_date ``` **方法二：使用RANK函数** 在SQL Server 2005及以上版本中，我们可以利用RANK()窗口函数对每个员工的晋升日期进行排序，并为每个日期分配一个排名。然后，仅选择排名为1和2的记录，即最近两次的晋升日期和工资。 ```sql SELECT s1.name, MAX(CASE WHEN rn=1 THEN sal_date ELSE NULL END) AS curr_date, MAX(CASE WHEN rn=1 THEN salary ELSE NULL END) AS curr_salary, MAX(CASE WHEN rn=2 THEN sal_date ELSE NULL END) AS prev_date, MAX(CASE WHEN rn=2 THEN salary ELSE NULL END) AS prev_salary FROM ( SELECT name, sal_date, salary, RANK() OVER(PARTITION BY name ORDER BY sal_date DESC) AS rn FROM salaries ) s1 WHERE rn < 3 GROUP BY s1.name ``` **方法三：使用Common Table Expression (CTE)和ROW_NUMBER函数** 同样适用于SQL Server 2005及以后版本，我们可以创建一个CTE，然后利用ROW_NUMBER()窗口函数对每个员工的晋升日期进行排序并分配行号。之后，选择行号为1和2的记录。 ```sql WITH cte(name, sal_date, sal_amt, rn) AS ( SELECT name, sal_date, salary, ROW_NUMBER() OVER(PARTITION BY name ORDER BY sal_date DESC) FROM salaries ) SELECT name, MAX(CASE WHEN rn=1 THEN sal_date ELSE NULL END) AS curr_date, MAX(CASE WHEN rn=1 THEN sal_amt ELSE NULL END) AS curr_salary, MAX(CASE WHEN rn=2 THEN sal_date ELSE NULL END) AS prev_date, MAX(CASE WHEN rn=2 THEN sal_amt ELSE NULL END) AS prev_salary FROM cte WHERE rn <= 2 GROUP BY name ``` 这三种方法的核心都是通过对员工的晋升记录进行分组和排序，然后挑选出最近两次晋升的相关信息。它们展示了SQL在处理时间序列数据和聚合数据时的灵活性和强大功能。根据实际的数据库版本和性能需求，可以选择合适的方法来实现这个查询。