導讀:1建站知識程序員們在編寫一個雇員報表，他們需要得到每個雇員當前及歷史工資狀態的信息，以便生成報表。報表需要顯示每seo網站優化軟件網站seo優化診斷工具。

復制代碼 代碼如下:

--程序員們在編寫一個雇員報表，他們需要得到每個雇員當前及歷史工資狀態的信息， --以便生成報表。報表需要顯示每個人的晉升日期和工資數目。 --如果將每條工資信息都放在結果集的一行中，并讓宿主程序去格式化它。 --應用程序的程序員都是一幫懶人，他們需要在每個雇員的一行上得到當前 --和歷史工資信息。這樣就可以寫一個非常簡單的循環語句。 ---示例： create table salaries ( name nvarchar(50) not null, sal_date date not null, salary money not null, ) go ALTER TABLE [dbo].salaries ADD CONSTRAINT [PK_salaries] PRIMARY KEY CLUSTERED ( name ,sal_date asc )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ----插入數據 insert into salaries select 'TOM','2010-1-20',2000 union select 'TOM','2010-6-20',2300 union select 'TOM','2010-12-20',3000 union select 'TOM','2011-6-20',4000 union select 'Dick','2011-6-20',2000 union select 'Harry','2010-6-20',2000 union select 'Harry','2011-6-20',2000 go ----方法一、使用left join 連接進行查詢（sql 2000及以上版本） 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 go ----方法二、這個方法是對每個雇員中的行進行編號，然后取出兩個雇用日期最近的日期， ---（sql 2005以上版本） 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 pre建設網站v_date, MAX(case when rn=2 then salary else null end) as curr_salary from (select name,sal_date,salary, RANK() over(partition by name order by sal_date desc) rn from salaries ) s1 where rn<3 group by s1.name go ---方法三、在sql server 2005之后版本可以使用這種方法 ，使用CTE的方式來實現 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) as rn from salaries ) select o.name,o.sal_date AS curr_date,o.sal_amt as curr_amt,i.sal_date as prev_date ,i.sal_amt as prev_amt from cte as o left outer join cte as i on o.name=i.name and i.rn=2 where o.rn=1 go ----方法四、使用視圖，將問題分為兩種情況 ---1.只有一次工資變動的雇員 ---2.有兩次或多次工資變動的雇員 create view v_salaries as select a.name,a.sal_date,MAX(a.salary) as salary from salaries as a ,salaries as b where a.sal_date<=b.sal_date and a.name=b.name group by a.name,a.sal_date having Cseo網站優化軟件OUNT(*)<=2 go select a.name,a.sal_date, a.salary,b.sal_date,b.salary from v_salaries a ,v_salaries b where a.name=b.name and a.sal_date>b.sal_date union all select name,max(sal_date),max(salary),cast(null as date),cast(null as decimal(8,2)) from v_salaries group by name having count(*)=1 go drop table salaries go drop view v_sala建設網站公司ries

關鍵詞標簽: SQL 語句 兩次