t - sql集团/和查询问题

It's been a crappy Monday AM and I can't think straight. Can someone help me figure out how to group/sum the rows returned so that there is only ONE instance of the AssessorParcelNumber?

这是一个糟糕的星期一早上，我无法清醒地思考。是否有人可以帮助我找到如何对返回的行进行分组/求和，这样就只有一个评估对象的实例了?

So, instead of the following result set:

因此，而不是下面的结果集:

140-31-715-164  3545    2004-09-14 00:00:00.000 1665.00 0.00    0.00    1665.00
140-31-715-164  3545    2004-09-14 00:00:00.000 0.00    534.00  0.00    534.00
140-31-715-037  3546    2004-03-11 00:00:00.000 120.00  0.00    0.00    120.00
140-31-715-037  3546    2004-03-11 00:00:00.000 0.00    0.00    0.00    0.00

I get this instead:

取代它可得到:

140-31-715-164  3545    2004-09-14 00:00:00.000 1665.00 534.00  0.00    2199.00
140-31-715-037  3546    2004-03-11 00:00:00.000 120.00  0.00    0.00    120.00

Help! Thanks!

的帮助!谢谢!

select
      u.AssessorParcelNumber,
      c.CollectionKey AS [r_number],
      c.Closed,
      CASE cd.Name1 WHEN 'Association'
            THEN CONVERT(dec(18,2),sum(t.Amount - t.AppliedAmount)) ELSE 0 END AS [assoc_balance],
      CASE cd.Name1 WHEN 'RRFS' 
            THEN CONVERT(dec(18,2),sum(t.Amount - t.AppliedAmount)) ELSE 0 END AS [rr_balance],
      CASE cd.Name1 WHEN 'RRFS' THEN 0 WHEN 'Association' THEN 0
            ELSE CONVERT(dec(18,2),sum(t.Amount - t.AppliedAmount)) END AS [_balance],
      CONVERT(dec(18,2),SUM(t.amount - t.AppliedAmount)) AS [balance]
from
      Unit u with(nolock)
      left outer join [collection] c with(nolock) on u.UnitKey = c.UnitKey
      left outer join TransactionDetail t with(nolock) on c.CollectionKey=t.CollectionKey
      left outer join TypeCode tc with(nolock) on t.PostType = tc.PostType
      left outer join CodeData cd with(nolock) on tc.Category = cd.Code2 and Code1=5
where
      t.Credit = 0 -- is a charge
      and t.Voided = 0 -- is not voided
      -- and u.AssessorParcelNumber = '140-31-715-164'
group by
      u.AssessorParcelNumber, c.CollectionKey, c.closed, cd.Name1
order by
      c.CollectionKey,
      cd.Name1;

5 个解决方案

#1

Looks like you'd want to SUM your various balance columns.

看起来你想要对各种平衡列求和。

SELECT
      t.AssessorParcelNumber,
      t.[r_number],
      t.Closed,
      SUM([assoc_balance]),
      SUM([rr_balance]),
      SUM([_balance]),
      SUM([balance])
    FROM (/* Insert your original query here */) t
    GROUP BY t.AssessorParcelNumber, t.r_number, t.Closed

#2

Assuming SQL Server 2005 or better:

假设SQL Server 2005或更好:

I'd use your current query as a CTE then query/group by that. I.e.:

我将使用您当前的查询作为CTE然后查询/组。例如:

;With CTE AS(


select
      u.AssessorParcelNumber,
      c.CollectionKey AS [r_number],
      c.Closed,
      CASE cd.Name1 WHEN 'Association'
            THEN CONVERT(dec(18,2),sum(t.Amount - t.AppliedAmount)) ELSE 0 END AS [assoc_balance],
      CASE cd.Name1 WHEN 'RRFS' 
            THEN CONVERT(dec(18,2),sum(t.Amount - t.AppliedAmount)) ELSE 0 END AS [rr_balance],
      CASE cd.Name1 WHEN 'RRFS' THEN 0 WHEN 'Association' THEN 0
            ELSE CONVERT(dec(18,2),sum(t.Amount - t.AppliedAmount)) END AS [_balance],
      CONVERT(dec(18,2),SUM(t.amount - t.AppliedAmount)) AS [balance]
from
      Unit u with(nolock)
      left outer join [collection] c with(nolock) on u.UnitKey = c.UnitKey
      left outer join TransactionDetail t with(nolock) on c.CollectionKey=t.CollectionKey
      left outer join TypeCode tc with(nolock) on t.PostType = tc.PostType
      left outer join CodeData cd with(nolock) on tc.Category = cd.Code2 and Code1=5
where
      t.Credit = 0 -- is a charge
      and t.Voided = 0 -- is not voided
      -- and u.AssessorParcelNumber = '140-31-715-164'
group by
      u.AssessorParcelNumber, c.CollectionKey, c.closed, cd.Name1
order by
      c.CollectionKey,
      cd.Name1)


SELECT AssessorParcelNumber, 
r_number, 
Closed, 
SUM(Assoc_balance) AS 'Assoc_Balance',
SUM(rr_balance) AS 'rr_balance',
SUM(_balance) AS '_balance',
SUM(balance) AS 'balance'
FROM CTE
GROUP BY AssessorParcelNumber, r_number, Closed

#3

select
      u.AssessorParcelNumber,
      c.CollectionKey AS [r_number],
      c.Closed,
      [assoc_balance]=CONVERT(dec(18,2),SUM(CASE WHEN cd.Name1='Association' THEN t.Amount - t.AppliedAmount ELSE 0 END)),
      [rr_balance]=CONVERT(dec(18,2),SUM(CASE WHEN cd.Name1='RRFS' THEN t.Amount - t.AppliedAmount ELSE 0 END)),
      [_balance]=CONVERT(dec(18,2),SUM(CASE WHEN cd.Name1='RRFS' THEN t.Amount - t.AppliedAmount ELSE 0 END)),
      CONVERT(dec(18,2),SUM(t.amount - t.AppliedAmount)) AS [balance]
from
      Unit u with(nolock)
      left outer join [collection] c with(nolock) on u.UnitKey = c.UnitKey
      left outer join TransactionDetail t with(nolock) on c.CollectionKey=t.CollectionKey
      left outer join TypeCode tc with(nolock) on t.PostType = tc.PostType
      left outer join CodeData cd with(nolock) on tc.Category = cd.Code2 and Code1=5
where
      t.Credit = 0 -- is a charge
      and t.Voided = 0 -- is not voided
      -- and u.AssessorParcelNumber = '140-31-715-164'
group by
      u.AssessorParcelNumber, c.CollectionKey, c.closed, cd.Name1
order by
      c.CollectionKey,
      cd.Name1;

#4

The problem stems from including cd.Name1 in the GROUP BY clause. In the results set you listed (and I am making some assumptions here), you've got a row each for cd.Name = "Association" and "RRFS", and the group by churns that into two rows. Take that column out of the GROUP BY and move it into the case statement, something like:

这个问题源于在GROUP BY子句中包含cd.Name1。在您列出的结果集中(我在这里做了一些假设)，您有一个c . name = "Association"和"RRFS"的行，组将它们分成两行。把这个列从组中移出来放到case语句中，比如:

select 
      u.AssessorParcelNumber, 
      c.CollectionKey AS [r_number], 
      c.Closed, 
      CONVERT(dec(18,2), sum(CASE cd.Name1 WHEN 'Association' THEN t.Amount - t.AppliedAmount ELSE 0 END)) AS [assoc_balance], 
      CONVERT(dec(18,2), sum(CASE cd.Name1 WHEN 'RRFS'        THEN t.Amount - t.AppliedAmount ELSE 0 END)) AS [rr_balance], 
      CONVERT(dec(18,2), sum(CASE cd.Name1 WHEN 'RRFS' THEN 0 WHEN 'Association' THEN 0 ELSE t.Amount - t.AppliedAmount END)) AS [_balance], 
      CONVERT(dec(18,2),SUM(t.amount - t.AppliedAmount)) AS [balance] 
from 
      Unit u with(nolock) 
      left outer join [collection] c with(nolock) on u.UnitKey = c.UnitKey 
      left outer join TransactionDetail t with(nolock) on c.CollectionKey=t.CollectionKey 
      left outer join TypeCode tc with(nolock) on t.PostType = tc.PostType 
      left outer join CodeData cd with(nolock) on tc.Category = cd.Code2 and Code1=5 
where 
      t.Credit = 0 -- is a charge 
      and t.Voided = 0 -- is not voided 
      -- and u.AssessorParcelNumber = '140-31-715-164' 
group by 
      u.AssessorParcelNumber, c.CollectionKey, c.closed
order by 
      c.CollectionKey, 
      cd.Name1;

#5

It's hard to tell given the formatting of your result sets and lack of column headers, but my first guess would be that your GROUP BY needs to eliminate the use of cd.Name1 and use SUM(CASE...) in your columns.

考虑到您的结果集的格式和缺少列标题，很难说清楚，但我的第一个猜测是，您的组需要消除对cd.Name1的使用，并在您的列中使用SUM(CASE…)。

For your column list, you can try this:

对于你的专栏列表，你可以尝试以下方法:

CAST(SUM(CASE cd.Name1
             WHEN 'Association' THEN t.Amount - t.AppliedAmount
             ELSE 0
         END) AS DECIMAL(18, 2)) AS [assoc_balance],
CAST(SUM(CASE cd.Name1
             WHEN 'RRFS' THEN t.Amount - t.AppliedAmount
             ELSE 0
         END) AS DECIMAL(18, 2)) AS [rr_balance],

CAST(SUM(CASE cd.Name1
             WHEN 'RRFS' THEN 0
             WHEN 'Association' THEN 0
             ELSE t.Amount - t.AppliedAmount
         END) AS DECIMAL(18, 2)) AS [_balance],

Also, you'll need to remove the name from the ORDER BY as well.

此外，还需要从订单中删除名称。