从一个表通过与另一个表的连接来聚合 – T-SQL(总共3个表)

huangapple go评论65阅读模式
英文:

Joining Aggregate from table through join with another table - T-SQL (3 tables in total)

问题

我有3个表:
A:jira_unresolved_sales_issue_v - 我的“主表”
B:jira_causes_links_v - 我的“连接表”
C:jira_invoices_v - 我想要对其进行SUM计算的表
A通过A.id(唯一)连接到B,B通过B.srcID(唯一)连接到C.issueid(唯一)。

所以我想要在表A中添加(左连接)来自表C的发票总和。

表A:

id 其他列
1 sxacdfa
2 xasdafa

表B:

srcID dstID
1 3
1 4
2 5
2 6

表C:

issueid C.Amount
3 3000
4 200
5 100
6 50

我想要实现的表A如下:

A.id C.Amount
1 3200
2 150

我尝试了以下代码:

select  A.id, sum(C.Amount) as amount
from A
LEFT JOIN B on A.id = B.srcID
LEFT JOIN ( Select C.issueid, sum(C.amount) as amount
FROM C
WHERE C.status <> 'Canceled'
GROUP BY C.issueid) as C on B.dstID= C.issueID
GROUP BY A.ID, C.amount (sum of amount)

但这给了我太多行。它实际上没有按我的A.ID进行SUM。我知道我接近了,只是缺少一些东西。

英文:

I have 3 tables:
A : jira_unresolved_sales_issue_v - my 'main table'
B : jira_causes_links_v - my 'connecting table'
C : jira_invoices_v - table where I want to take SUM with
A connects to B with A.id (unique) = B.srcID , B connects to C with B.dstID (unique) = C.issueid (unique)

So I want to add (left join) some sum of invoices from table C to table A.

Table A:

id other col
1 sxacdfa
2 xasdafa

Table B:

srcID dstID
1 3
1 4
2 5
2 6

Table C:

issueid C.Amount
3 3000
4 200
5 100
6 50

What I want to achieve is Table A like this:

A.id C.Amount
1 3200
2 150

I tried code :

select  A.id, sum(C.Amount) as amount
from A
LEFT JOIN B on A.id = B.srcID
LEFT JOIN ( Select C.issueid, sum(C.amount) as amount
FROM C
WHERE C.status <> 'Canceled'
GROUP BY C.issueid) as C on B.dstID= C.issueID
GROUP BY A.ID, C.amount (sum of amount)

But this kind of give me too many rows. It doesn't actually SUM per my A.ID. I know I am close, just missing something.

答案1

得分: 1

你似乎把事情搞得太复杂了,以下应该是你所需要的全部:

select a.Id, s.Amount
from a
outer apply (
    select Sum(amount) Amount
    from c join b on b.dstid = c.issueid
    where b.srcid = a.id
)s;
英文:

You seem to be over-complicating things, the following should be all you need

select a.Id, s.Amount
from a
outer apply (
	select Sum(amount) Amount
	from c join b on b.dstid = c.issueid
	where b.srcid = a.id
)s;

答案2

得分: 1

在像这样提问的时候,提供可重现的 DML/DDL 是非常有帮助的。我个人更喜欢使用表变量,因为清理工作较少。

DECLARE @TableA TABLE (id INT, otherCol NVARCHAR(10));
DECLARE @TableB TABLE (srcID INT, dstID INT);
DECLARE @TableC TABLE (issueid INT, Amount INT, status NVARCHAR(10));

INSERT INTO @TableA (id, otherCol) VALUES (1, 'sxacdfa'), (2, 'xasdafa');
INSERT INTO @TableB (srcID, dstID) VALUES (1,3), (1,4), (2, 5), (2, 6), (2, 7);
INSERT INTO @TableC (issueid, Amount, status) VALUES 
(3, 3000, 'Open'), (4, 200, 'Open'), 
(5, 100, 'Open'), (6, 50, 'Open'), 
(7, 1000, 'Cancelled');

我不确定为什么你觉得这里需要子查询,从你描述的情况来看,只是一对 LEFT OUTER JOIN,然后进行了聚合操作:

SELECT a.id, SUM(c.Amount) AS Amount
  FROM @TableA a
    LEFT OUTER JOIN @TableB b
	  ON a.id = b.srcID
	LEFT OUTER JOIN @TableC c
	  ON b.dstID = c.issueid
	  AND c.status <> 'Cancelled'
 GROUP BY a.id

id Amount
1 3200
2 150

使用 LEFT OUTER JOIN 仍然允许没有匹配的行返回。例如,如果 a.id = b.srcID 的 @TableB 中没有行,它们仍然会被返回,但 NULLSUM 将是 NULL

另外,对于对象名称,保持一致的大小写习惯是个好习惯。有些你用了大写的 ID,而在其他地方则用了小写的 id

英文:

When asking questions like these it's really helpful to provide reproduceable DML/DDL. I favor table variables for this personally, because there's less to clean up.

DECLARE @TableA TABLE (id INT, otherCol NVARCHAR(10));
DECLARE @TableB TABLE (srcID INT, dstID INT);
DECLARE @TableC TABLE (issueid INT, Amount INT, status NVARCHAR(10));

INSERT INTO @TableA (id, otherCol) VALUES (1, &#39;sxacdfa&#39;), (2, &#39;xasdafa&#39;);
INSERT INTO @TableB (srcID, dstID) VALUES (1,3), (1,4), (2, 5), (2, 6), (2, 7);
INSERT INTO @TableC (issueid, Amount, status) VALUES 
(3, 3000, &#39;Open&#39;), (4, 200, &#39;Open&#39;), 
(5, 100, &#39;Open&#39;), (6, 50, &#39;Open&#39;), 
(7, 1000, &#39;Cancelled&#39;);

I'm not sure why you feel there's a need for a sub query here, from what you described it's just a pair of LEFT OUTER JOINs, and then an aggregate:

SELECT a.id, SUM(c.Amount) AS Amount
  FROM @TableA a
    LEFT OUTER JOIN @TableB b
	  ON a.id = b.srcID
	LEFT OUTER JOIN @TableC c
	  ON b.dstID = c.issueid
	  AND c.status &lt;&gt; &#39;Cancelled&#39;
 GROUP BY a.id

id Amount
1 3200
2 150

Using the LEFT OUTER JOIN will still allow rows which do not have a match to be returned, if there were no rows in @TableB for a.id = b.srcID for example, they would still be returned, but the SUM of the NULLs would be NULL.

As an aside, it's a good habit to use consistent casings for your object names. There's some you've named with an upper case ID and others where it's a lower case id.

huangapple
  • 本文由 发表于 2023年3月10日 00:01:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/75687122.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定