我有个表ht
有字段hth(合同号), gcts(购车台数),dkje(贷款金额),dwdm(单位代码),现有一组模拟数据 hth, gcts, dkje, dwdm, bgsj
s039201 5 5000000 039201 2007.07.21
s039201*01 5 3000000 039201 2007.07.21
s039201*02 4 2000000 039201 2007.07.21
s039201*03 4 2000000 039201 2007.07.25
s059101 5 5000000 059101 2007.08.05
s059101*01 5 4000000 059101 2007.07.01
s059101*02 4 3000000 059101 2007.08.05
s062140 5 3000000 062140 2007.07.10
s062140*01 4 4000000 062140 2007.07.10
没有带*的数据是原始数据。带*的是变更后的数据 。我想到两个数据之间的差值。
*01是第一次变更。*02是第2次变更。*03是第3次变更。
得到的结果:07月变更的数据是:
hth, gcts, dkje, dwdm, bgsj
s039201 1 -3000000 039201 2007.07.25
s059101 0 -1000000 059101 2007.07.01
s062140 1 1000000 062140 2007.07.10
08月变更的数据是:
hth, gcts, dkje, dwdm, bgsj
s059101 1 -2000000 059101 2007.08.05
怎么样得到以上结果。谢谢大家
有字段hth(合同号), gcts(购车台数),dkje(贷款金额),dwdm(单位代码),现有一组模拟数据 hth, gcts, dkje, dwdm, bgsj
s039201 5 5000000 039201 2007.07.21
s039201*01 5 3000000 039201 2007.07.21
s039201*02 4 2000000 039201 2007.07.21
s039201*03 4 2000000 039201 2007.07.25
s059101 5 5000000 059101 2007.08.05
s059101*01 5 4000000 059101 2007.07.01
s059101*02 4 3000000 059101 2007.08.05
s062140 5 3000000 062140 2007.07.10
s062140*01 4 4000000 062140 2007.07.10
没有带*的数据是原始数据。带*的是变更后的数据 。我想到两个数据之间的差值。
*01是第一次变更。*02是第2次变更。*03是第3次变更。
得到的结果:07月变更的数据是:
hth, gcts, dkje, dwdm, bgsj
s039201 1 -3000000 039201 2007.07.25
s059101 0 -1000000 059101 2007.07.01
s062140 1 1000000 062140 2007.07.10
08月变更的数据是:
hth, gcts, dkje, dwdm, bgsj
s059101 1 -2000000 059101 2007.08.05
怎么样得到以上结果。谢谢大家
s059101*01 2007.07.01 在2007.07.01发生了变更
s059101*02 2007.08.05 在2007.08.05又 发生了变更
s059101 2007.08.05 最新的一次变更日期
s059101*01 2007.07.01 在2007.07.01发生了变更
s059101*02 2007.08.05 在2007.08.05又 发生了变更
那这么说下面一行的日期该是2007.07.25咯:s039201 5 5000000 039201 2007.07.21
……
s059101 2007.08.05 最新的一次变更日期
s059101*01 2007.07.01 在2007.07.01发生了变更
s059101*02 2007.08.05 在2007.08.05又 发生了变更
-------------------------
凭什么第一条是最新变更,而不是第三条,又凭什么不带*的又变成变更数据了。我实在是不明白,帮不上忙,撤退,抱歉!
insert into tb values('s039201' ,5,5000000,'039201','2007.07.25')
insert into tb values('s039201*01',5,3000000,'039201','2007.07.21')
insert into tb values('s039201*02',4,2000000,'039201','2007.07.21')
insert into tb values('s039201*03',4,2000000,'039201','2007.07.25')
insert into tb values('s059101' ,5,5000000,'059101','2007.08.05')
insert into tb values('s059101*01',5,4000000,'059101','2007.07.01')
insert into tb values('s059101*02',4,3000000,'059101','2007.08.05')
insert into tb values('s062140' ,5,3000000,'062140','2007.07.10')
insert into tb values('s062140*01',4,4000000,'062140','2007.07.10')
godeclare @month as int
set @month = 7select t1.hth , t1.gcts - t2.gcts gcts , t2.dkje - t1.dkje dkje , t1.dwdm , convert(varchar(10),t1.bgsj,120) bgsj from
tb t1,
(
select a.* from
(
select left(hth , charindex('*',hth)-1) hth , cast(substring(hth , charindex('*',hth)+1 , len(hth)) as int) hth_cs, gcts,dkje,dwdm,bgsj from tb where month(bgsj) = @month and charindex('*',hth)> 0
) a,
(
select hth , max(hth_cs) hth_cs from
(
select left(hth , charindex('*',hth)-1) hth , cast(substring(hth , charindex('*',hth)+1 , len(hth)) as int) hth_cs, gcts,dkje,dwdm,bgsj from tb where month(bgsj) = @month and charindex('*',hth)> 0
) t
group by hth
) b
where a.hth = b.hth and a.hth_cs = b.hth_cs
) t2
where t1.hth = t2.hth
drop table tb/*
hth gcts dkje dwdm bgsj
-------------------- ----------- ----------- ---------- ----------
s039201 1 -3000000 039201 2007-07-25
s059101 0 -1000000 059101 2007-08-05
s062140 1 1000000 062140 2007-07-10(所影响的行数为 3 行)
*/
insert into tb values('s039201' ,5,5000000,'039201','2007.07.25')
insert into tb values('s039201*01',5,3000000,'039201','2007.07.21')
insert into tb values('s039201*02',4,2000000,'039201','2007.07.21')
insert into tb values('s039201*03',4,2000000,'039201','2007.07.25')
insert into tb values('s059101' ,5,5000000,'059101','2007.08.05')
insert into tb values('s059101*01',5,4000000,'059101','2007.07.01')
insert into tb values('s059101*02',4,3000000,'059101','2007.08.05')
insert into tb values('s062140' ,5,3000000,'062140','2007.07.10')
insert into tb values('s062140*01',4,4000000,'062140','2007.07.10')
godeclare @month as int
set @month = 8select t1.hth , t1.gcts - t2.gcts gcts , t2.dkje - t1.dkje dkje , t1.dwdm , convert(varchar(10),t1.bgsj,120) bgsj from
tb t1,
(
select a.* from
(
select left(hth , charindex('*',hth)-1) hth , cast(substring(hth , charindex('*',hth)+1 , len(hth)) as int) hth_cs, gcts,dkje,dwdm,bgsj from tb where month(bgsj) = @month and charindex('*',hth)> 0
) a,
(
select hth , max(hth_cs) hth_cs from
(
select left(hth , charindex('*',hth)-1) hth , cast(substring(hth , charindex('*',hth)+1 , len(hth)) as int) hth_cs, gcts,dkje,dwdm,bgsj from tb where month(bgsj) = @month and charindex('*',hth)> 0
) t
group by hth
) b
where a.hth = b.hth and a.hth_cs = b.hth_cs
) t2
where t1.hth = t2.hth
drop table tb/*
hth gcts dkje dwdm bgsj
-------------------- ----------- ----------- ---------- ----------
s059101 1 -2000000 059101 2007-08-05
(所影响的行数为 1 行)
*/
s039201 5 5000000 039201 2007.07.25