Question

我有一个交易表:

CREATE TABLE [dbo].[Trans](
[ID] [char](6) NOT NULL,
[PersonID] [char](6) NULL,
[TransCode] [char](2) NULL,
[TransDesc] [char](45) NULL,
[TransDate] [datetime] NULL,
[TransAmount] [numeric](18, 2) NULL)

我想的是每个人最近付款的日期和数额。付款由《TransCode》确定(见下文《WHERE条款》)。

在任何一天都可以有多项交易,因此,我想到最近一天的交易总额。此外,还可以进行“逆向”交易,使最近一天的交易总额达到0(零)——我不想在结果中看到这些交易(见下面的“HAVING(SUM(TransAmount)”和“0”)。

这里是我最近的努力,这并非奏效,但我不知道为什么!

SELECT  PersonID, MAX(TransDate) AS LastPaymentDate, TotalAmount  
FROM    (SELECT   PersonID, TransDate, SUM(TransAmount) AS TotalAmount
         FROM     Trans
         WHERE    (TransCode IN ( 11 ,  12 ,  13 ,  14 ,  18 ,  19 ,  61 ,  63 ,  68 ,  70 ,  71 ,  72 ,  73 ,  74 ,  75 ,  76 ,  78 ,  79 ,  80 ,  81 ,  94 ,  P2 ))
         GROUP BY PersonID, TransDate
         HAVING   (SUM(TransAmount) > 0)
         ORDER BY PersonID, TransDate) AS TotalAmount
GROUP BY PersonID, TotalAmount
ORDER BY PersonID

当我处理内部质询时,我会收到一份按日期分列的每人数额清单。但是,出于某种原因,外部争.正在归还每个人的多行。它只应归还每个人的一行:

Can someone one tell me what I am doing wrong?

请允许我知道,是否有任何补充资料需要更好地了解这一问题。

Thank you in advance for any help you can provide,

页: 1

Answer 1

SELECT  LastDate.PersonID, LastDate.TransDate AS LastPaymentDate, TotalAmount.TotalAmount  
FROM    (SELECT   PersonID, Max(TransDate) as TransDate
         FROM     Trans
         WHERE    (TransCode IN ( 11 ,  12 ,  13 ,  14 ,  18 ,  19 ,  61 ,  63 ,  68 ,  70 ,  71 ,  72 ,  73 ,  74 ,  75 ,  76 ,  78 ,  79 ,  80 ,  81 ,  94 ,  P2 ))
         GROUP BY PersonID
         HAVING   (SUM(TransAmount) > 0)
         ORDER BY PersonID, TransDate) AS LastDate
INNER JOIN (SELECT PersonID, Transdate, SUM(TransAMount) as TotalAmount 
         FROM     Trans
         WHERE    (TransCode IN ( 11 ,  12 ,  13 ,  14 ,  18 ,  19 ,  61 ,  63 ,  68 ,  70 ,  71 ,  72 ,  73 ,  74 ,  75 ,  76 ,  78 ,  79 ,  80 ,  81 ,  94 ,  P2 ))
         GROUP BY PersonID
         HAVING   (SUM(TransAmount) > 0)
         ORDER BY PersonID, TransDate) AS TotalAmount 
ON TotalAmount.PersonID = LastDate.PersonID AND TotalAmount.TransDate = LastDate.TransDate
ORDER BY LastDate.PersonID

类似工作

Answer 2

您可以尝试更高效的窗口功能, 这 work了工作。

select personid,trandate,SUM(transamount) as total 
from(
select personid,
CAST(transdate as DATE) as Trandate,transamount,
RANK() over(partition by personid order by
 CAST(transdate as DATE) desc) as rnk
 from trans
 ) as x
 where rnk=1
 group by PersonID,trandate

Answer 3

自你重新组合个人和总数以来,你又接过了多行,而且有多个日期,总金额相同。

仅简单总结一下你在另一选择中 entire取“彩票”(LastPaymentDate):

SELECT PersonID, LastPaymentDate, TotalAmount  FROM (
SELECT  PersonID, MAX(TransDate) AS LastPaymentDate, TotalAmount  
FROM    (SELECT   PersonID, TransDate, SUM(TransAmount) AS TotalAmount
         FROM     Trans
         WHERE    (TransCode IN ( 11 ,  12 ,  13 ,  14 ,  18 ,  19 ,  61 ,  63 , 
 68 ,  70 ,  71 ,  72 ,  73 ,  74 ,  75 ,  76 ,  78 ,  79 ,  80 ,  81 ,  94 ,  P2 ))
         GROUP BY PersonID, TransDate
         HAVING   (SUM(TransAmount) > 0)
         ORDER BY PersonID, TransDate) AS InnerSelect
GROUP BY PersonID, TotalAmount
ORDER BY PersonID
) 
GROUP BY PersonID, LastPaymentDate, TotalAmount  
HAVING LastPaymentDate = MAX(LastPaymentDate)

Answer 4

我认为,这将起到 n的作用(但我并没有对此进行检验)。

WITH PersonDateSum AS
(
  SELECT   PersonID, TransDate, SUM(TransAmount) AS TotalAmount
         FROM     Trans
         WHERE    (TransCode IN ( 11 ,  12 ,  13 ,  14 ,  18 ,  19 ,  61 ,  63 ,  68 ,  70 ,  71 ,  72 ,  73 ,  74 ,  75 ,  76 ,  78 ,  79 ,  80 ,  81 ,  94 ,  P2 ))
         GROUP BY PersonID, TransDate
         HAVING   (SUM(TransAmount) > 0)
         ORDER BY PersonID, TransDate
) 
SELECT PersonID, LastPaymentDate, TotalAmount
FROM PersonDateSum
WHERE ROW_NUMBER() OVER  (PARTITION BY PersonID, LastPaymentDate ORDER BY PersonID, LastPaymentDate DESC) = 1
ORDER BY PersonID

如果你正在处理速度问题,我建议你在表格中选取贵国的代号,并加入其中,其速度将快于使用本表。

友情链接