我有一张表 (SPG),上面有个人 (DonorID)、捐赠日期 (DonateDate) 和捐赠地点 (Center)。我想找到捐赠的日期和地点(简单),然后是下一次捐赠的日期和地点,以及两个日期之间的差距。
我使用了一个 SELECT 子查询,它给了我下一个日期(NextDate),但我无法获得匹配的中心:
SELECT SPG.DonorID,
SPG.DonateDate,
SPG.Center AS Center1,
(SELECT Min(DonateDate) as MinDate
FROM SPG As A
WHERE SPG.DonorID = A.DonorID
AND A.DonateDate > SPG.DonateDate) AS NextDate,
[NextDate]-[DonateDate] AS Gap
FROM SPG
GROUP BY SPG.DonorID, SPG.DonateDate, SPG.Center
HAVING ((((SELECT Min(DonateDate) as MinDate
FROM SPG As A
WHERE SPG.DonorID = A.DonorID
AND A.DonateDate > SPG.DonateDate))<>""))
ORDER BY SPG.DonorID, SPG.DonateDate;
我有一个不同的 INNER JOIN 查询,它为我提供了中心,但它返回所有未来的捐赠,而不仅仅是下一个:
SELECT A.DonorID,
A.DonateDate,
A.Center,
Min(B.DonateDate) AS NextDate,
B.Center,
[B].[DonateDate]-[A].[DonateDate] AS Gap
FROM SPG AS A INNER JOIN SPG AS B ON A.DonorID = B.DonorID
WHERE B.DonateDate > A.DonateDate
GROUP BY A.DonorID,
A.DonateDate,
A.Center,
B.Center,
B.DonateDate-A.DonateDate;
基本上,我似乎无法将子查询中的 Min() 命令与从 JOIN 返回多个字段结合起来。
(请原谅不同的符号 - 大量复制和粘贴试用代码)
这里的一位 SQL 大神可能知道一种更简洁的方法,但这就是我目前所拥有的:
select
s1.donorid,
s1.donatedate,
s1.center as center1,
(
select min(s2.donatedate)
from spg s2
where s1.donatedate < s2.donatedate and s1.donorid = s2.donorid
) as nextdate,
(
select s2.center
from spg s2
where
s1.donorid = s2.donorid and
s2.donatedate =
(
select min(s3.donatedate)
from spg s3
where s1.donatedate < s3.donatedate and s1.donorid = s3.donorid
)
) as center2,
nextdate - s1.donatedate as gap
from
spg s1
order by
s1.donorid,
s1.donatedate
如果你只关注那些有“下一个”捐赠日期的记录,也许:
select
s1.donorid,
s1.donatedate,
s1.center as center1,
(
select min(s2.donatedate)
from spg s2
where s1.donatedate < s2.donatedate and s1.donorid = s2.donorid
) as nextdate,
(
select s2.center
from spg s2
where
s1.donorid = s2.donorid and
s2.donatedate =
(
select min(s3.donatedate)
from spg s3
where s1.donatedate < s3.donatedate and s1.donorid = s3.donorid
)
) as center2,
nextdate - s1.donatedate as gap
from
spg s1
where exists
(select 1 from spg s2 where s1.donorid = s2.donorid and s1.donatedate < s2.donatedate)
order by
s1.donorid,
s1.donatedate
我听说你喜欢相关的子查询...
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句