当一行中的几列相同但其他列不同时，如何覆盖一行？ [英] How Can I Overwrite A Row When Few Columns Of A Row Are Same But Other Are Different.?

问题描述

这是一个表数据： -

this is a table data:-

empcode dsg_code    dsg_name             date               PAY_YEAR
1305    0013    DIVISION SALES MANAGER  2011-04-30 00:00:00 2011
1305    00104   REGIONAL SALES MANAGER  2011-11-30 00:00:00 2011
1305    00104   REGIONAL SALES MANAGER  2012-01-31 00:00:00 2012
1305    00104   REGIONAL SALES MANAGER  2013-01-31 00:00:00 2013
1305    0013    DIVISION SALES MANAGER  2013-03-31 00:00:00 2013
1305    0013    DIVISION SALES MANAGER  2014-01-31 00:00:00 2014
1305    00211   DIVISIONAL     MANAGER  2014-08-31 00:00:00 2014
1305    00104   REGIONAL SALES MANAGER  2015-01-31 00:00:00 2015

我想要： -

and i want to like:-

empcode dsg_code    dsg_name            date                PAY_YEAR
1305    00104   REGIONAL SALES MANAGER  2011-11-30 00:00:00 2011

1305    0013    DIVISION SALES MANAGER  2011-04-30 00:00:00 2011

1305    0013    DIVISION SALES MANAGER  2013-03-31 00:00:00 2013

1305    00211   DIVISIONAL     MANAGER  2014-08-31 00:00:00 2014
1305    00104   REGIONAL SALES MANAGER  2015-01-31 00:00:00 2015

我怎么能回复这个...这是我数据的一小部分，这只适用于一名员工，但我有数千名员工。

how can i retrive this ..this is a very small part of my data this is only for one employee but i have thousands employee.

推荐答案

如果我得到你的要求。

我相信领先/滞后分析c函数将是你正在寻找的。



但是看到你在SQL-server-2008R2上，这对你没有帮助，它是SQLServer2012中的一个功能。



所以这里有一种在SQL-server-2008R2中为你的场景做一个领先/滞后的可能方法：

If I get what you are asking.
I believe the Lead/Lag analytic functions would be what you are looking for.

But seeing you are on SQL-server-2008R2, that will not help you out and it is a feature in SQLServer2012.

So here is a possible way of doing a lead/lag in SQL-server-2008R2 for your scenario:

with Emp as (
--setup test dummy data
	select 
		1305 empcode,
		'0013' dsg_code,
		'DIVISION SALESMANAGER' dsg_name,
		convert(datetime, '2011 apr 30 00:00:00') date,
		2011 PAY_YEAR
		union all select 1305, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2011 nov 30 00:00:00'), 2011
		union all select 1305, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2012 jan 31 00:00:00'), 2012
		union all select 1305, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2013 jan 31 00:00:00'), 2013
		union all select 1305, '0013', 'DIVISION SALES MANAGER', convert(datetime, '2013 mar 31 00:00:00'), 2013
		union all select 1305, '0013', 'DIVISION SALES MANAGER', convert(datetime, '2014 jan 31 00:00:00'), 2014
		union all select 1305, '00211', 'DIVISIONAL MANAGER', convert(datetime, '2014 aug 31 00:00:00'), 2014
		union all select 1305, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2015 jan 31 00:00:00'), 2015

		--addition data added
		union all select 1355, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2011 nov 30 00:00:00'), 2011
		union all select 1355, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2012 jan 31 00:00:00'), 2012
		union all select 1355, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2013 jan 31 00:00:00'), 2013

), EmpRowId as (
	select
		row_number() over(partition by Emp.empcode order by Emp.empcode, Emp.date) rowid
		, *
	from Emp
)
, EmpLag as (
	select
		e.*,
		isnull(Lag.dsg_code, '') Lagdsg_code,
		isnull(Lag.dsg_name, '') Lagdsg_name
	from EmpRowId e
	left join EmpRowId Lag
		on e.empcode = Lag.empcode
			and e.rowid = Lag.rowid + 1
)
select
	* 
from EmpLag
where dsg_code <> Lagdsg_code
order by 
	empcode, 
	date
;

希望能帮到你。



以下是Lead / Lag示例的链接：

http://www.rafael-salas.com/2008/05/t-sql-lead-and-lag-functions.html [ ^ ]

这篇关于当一行中的几列相同但其他列不同时，如何覆盖一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！