求SQL文(难啊...)

[painAndhappy]

表结构如下
id updDate updId amount
有以下数据
**************************************************************
001 2004/09/29 A 100 ---------------1
001 2004/09/30 B 110 ---------------2
001 2004/10/02 B 140 ---------------3
001 2004/10/02 A 110 ---------------4
001 2004/10/03 A 110 ---------------5

002 2004/10/01 B 200 ---------------6
002 2004/10/02 A 200 ---------------7
002 2004/10/02 A 200 ---------------8
002 2004/10/03 B 200 ---------------9

003 2004/09/29 B 300 ---------------10
003 2004/10/02 A 320 ---------------11
003 2004/10/02 A 310 ---------------12
003 2004/10/02 A 320 ---------------13
003 2004/10/03 B 320 ---------------14
**************************************************************
当传入日期为"2004/10/01"时,先取得用来比较的数据:即传入日期之前(包括传入日期)的同一id的最后日期的数据

(一)对于id为"001"的数据,其用来比较的数据是
----->001 2004/09/30 B 110 ---------------2
该数据和该id的最后一条数据,即
----->001 2004/10/03 A 110 ---------------5
比较,两者amount相同,但由于在所传入的日期("2004/10/01")之后有过更新操作,即
----->001 2004/10/02 B 140 ---------------3
所以对于id为"001"的数据,所需要抽出的数据是
----->001 2004/10/02 A 110 ---------------4

(二)对于id为"002"的数据,其用来比较的数据是
----->002 2004/10/01 B 200 ---------------6
该数据和该id的最后一条数据,即
----->002 2004/10/03 B 200 ---------------9
比较,两者amount相同,而且在所传入的日期("2004/10/01")之后有过更新操作,所以对于id为"002"的数据没有需要抽出的数据

(三)对于id为"003"的数据,其用来比较的数据是
----->003 2004/09/29 B 300 ---------------10
该数据和该id的最后一条数据,即
----->003 2004/10/03 B 320 ---------------14
比较,两者amount不同,所以对于id为"003"的数据,所需要抽出的数据是
----->003 2004/10/02 A 320 ---------------13
(*并非是编号为14的数据)

综上,对于传入日期为"2004/10/01"的情况,
所需抽出的数据是
001 2004/10/02 A 110 ---------------4
003 2004/10/02 A 320 ---------------13

逻辑上还有其他的处理方法,最终目的就是从表中抽出符合条件的数据(对于上述例子来说是数据4和13).
请怎样用SQL文来实现?(是在存储过程中进行处理)
(另:真实数据表中的数据,没有经过排序)

[张海霖]

这种问题往往是我还没看懂，老大已经给出答案了。

[playyuer]

declare @ datatime
set @ = '2004-10-01'
select *
from T a
where updDate = (select max(updDate) from T where updDate > @ and id = a.id)

[painAndhappy]

老大,不对啊...
(1)"id"没有传入,传入的只有日期:"2004/10/01"
(2)对于相同"id"的数据,也不一定是取updDate > @datatime的,而是要根据情况而定
a)updDate > @datatime的数据中,对同一"id"没有amount变更的,不需要抽出
b)有amount变更的
----如果该id的最后那条数据的amount和用来比较的数据的amount相同,则抽出该id的最后一次和用来比较的数据的amount"不"相同的"下"一条数据.
既对于id为001来说,编号为4的数据
----如果该id的最后那条数据的amount和用来比较的数据的amount"不"相同,则抽出和该id的最后那条数据amount相同的,且一直是相同的最先更新的那条数据.
既对于id为003来说,编号为13的数据.


问题比较复杂,可能我没说清楚.请再花点时间帮我想想.

[8992026]

select * from T b
where updDate=(
select max(updDate)
from T a
where updDate < (select max(updDate) from T where updDate > @ and id = b.id)
and a.id=b.id
)
and (
(
select amount
from T c
where c.id=b.id and updDate = (select max(updDate) from T where updDate > @ and id = c.id)
)<>(
select amount
from T d
where d.id=b.id and updDate = (select max(updDate) from T where updDate <= @ and id = d.id)
)
or
exists (
select 1 from T where updDate > @ and amount=b.amount
)
)

没测试，按照逻辑写，不知道对不对

[8992026]

逻辑写错了，改改：

select * from T b
where updDate=(
select max(updDate)
from T a
where updDate < (select max(updDate) from T where updDate > @ and id = a.id)
and a.id=b.id
)
and (
(
select amount
from T c
where c.id=b.id and updDate = (select max(updDate) from T where updDate > @ and id = c.id)
)<>(
select amount
from T d
where d.id=b.id and updDate = (select max(updDate) from T where updDate <= @ and id = d.id)
)
or
exists (
select 1 from T where updDate > @ and amount<>b.amount
)
)

[8992026]

效率没考虑
updId 好想没用

...

[painAndhappy]

updId在这个举的例子中确实没用,要在以后的处理中使用,一不小心写上去了...

SQL文好象还是不对 :(
(1)对于传入日期之后最后那条数据的amount,和传入日期之前最后那条数据的amount"不"相等的情况,并不一定是取传入日期之后最后数据的前面一个日期的数据,而是取同一id的,传入日期之后的,和最后那条数据amount相同,且连续相同的最先更新的那条数据

例如:
id updDate updId amount
--------------------------------------------------------------
004 2004/09/29 A 400 ---------------14
004 2004/10/02 B 410 ---------------15
004 2004/10/03 B 400 ---------------16
004 2004/10/03 A 410 ---------------17
004 2004/10/04 A 420 ---------------18
004 2004/10/05 A 420 ---------------19
004 2004/10/06 A 420 ---------------20
004 2004/10/06 A 420 ---------------21
上述数据中,当传入日期也是"2004/10/01"时,用来比较的数据是
----->004 2004/09/29 A 400 ---------------14
所抽出的数据是
----->004 2004/10/04 A 420 ---------------18
(数据从最后往前看)

(2)对于or exists之后的部分,既
"select 1 from T where updDate > @ and amount<>b.amount"
感觉比较奇怪,这样一来岂不是要把传入日期之后,只要amount不相等(而且抛开了id)的,再加上"and"之前的条件的数据都抽出了么?
而且,即使加上id判断,变成
"select 1 from T where updDate > @ and amount<>b.amount and id = b.id"
对于id为"004"的数据来说,编号15--21的7条数据是符合条件的,再加上"and"之前的判断,也会把15--19的5条数据抽出.

...

[8992026]

问题是：
004 2004/10/06 A 420 ---------------20
004 2004/10/06 A 420 ---------------21
说明你的表没有主键，不能唯一确定记录，那就必须用临时表了

declare @ datatime
set @ = '2004-10-01'
select IDENTITY(int,1,1) as id1,*
into #t
from T
order by id,updDate


select * from #T b
where id1=(
select top 1 id1
from (
select top 2 id1
from #T a
where updDate > @
and a.id=b.id
order by id1 desc
) as t
order by id1
)
and (
(
select amount
from #T c
where c.id=b.id and id1 = (select top 1 id1 from #T where updDate > @ and id = c.id order by id1 desc)
)<>(
select amount
from #T d
where d.id=b.id and id1 = (select top 1 id1 from T where updDate <= @ and id = d.id order by id1 desc)
)
or
exists (
select 1 from #T where updDate > @ and amount<>b.amount
)
)

[8992026]

在後加

drop table #t

[painAndhappy]

谢谢各位的回答,问题解决了.
关于这个问题,我从其中抽出一部分,开了另外一个贴子.
http://community.csdn.net/Expert/topic/3430/3430555.xml?temp=.5207941

另外,还有其他一种思路:
select IDENTITY(int,1,1) as seq, *
into #t
from T a
where a.updId > @
and exists (
select 1
from T b
where b.id = a.id
and b.updId > @
and b.amount <> (
select top 1 amount
from T c
where c.id = b.id
and c.updId <= @
order by
c.updId desc
)
)
order by
id, updDate



select a.id
,a.updDate
,a.updId
,a.amount
from #t a
where a.seq = (
select min(b.seq)
from #t b
where b.id = a.id
and b.amount = (
select top 1 c.amount
from #t c
where c.id = b.id
order by
c.seq desc
) and not exists (
select 1
from #t d
where d.id = b.id
and d.seq between a.seq and (
select top 1 e.seq
from #t e
where e.id = d.id
order by
e.seq desc
)
and d.amount <> (
select top 1 f.amount
from #t f
where f.id = d.id
order by
f.seq desc

)
)
)
order by
a.id