我有两个联合在一起的表,我想对这些值求和,而不是让两个不同的行具有相同的ID。这是我目前拥有的:
SELECT
d.ID,
SUM(d.balance) AS Balance
FROM
d
WHERE
d.status != 'closed'
GROUP BY
d.ID
UNION ALL
SELECT
t.ID,
SUM(t.balance) AS Balance
FROM
t
WHERE
t.status != 'closed'
GROUP BY
t.ID这给了我一个包含所有d值的列表,然后是所有t值,我希望所有具有匹配I的行都在一行中
发布于 2019-01-24 05:11:16
在当前查询周围放置一个外部查询,并再次分组,如下所示:
select id, sum(Balance)
from (
SELECT d.ID, SUM(d.balance) AS Balance
FROM d
WHERE d.[status] != 'closed'
GROUP BY d.ID
UNION ALL
SELECT t.ID, SUM(t.balance) AS Balance
FROM t
WHERE t.[status] != 'closed'
GROUP BY t.ID
) X
group by id实际上,我们并不需要内部的group by,所以下面的代码应该仍然有效,并且更加清晰:
select id, sum(Balance)
from (
SELECT d.ID, d.balance
FROM d
WHERE d.[status] != 'closed'
UNION ALL
SELECT t.ID, t.balance
FROM t
WHERE t.[status] != 'closed'
) X
group by id发布于 2019-01-24 05:23:04
希望这能对你有所帮助
DECLARE @DataSource TABLE
(
[id] INT
,[Balance] Float
,status Varchar(50)
);
INSERT INTO @DataSource ([ID], [Balance],[Status])
VALUES (1, 100 ,'Closed')
,(2, 200,'Open' )
,(3, 300 ,'Onhold')
,(4, 400 ,'Open')
DECLARE @DataSource1 TABLE
(
[id] INT
,[Balance] Float
,status Varchar(50)
);
INSERT INTO @DataSource1 ([ID], [Balance],[Status])
VALUES (1, 3 ,'Open')
,(2, 3 ,'Open')
,(3, 55 ,'Onhold')
,(4, 6 ,'Closed')
SELECT d.ID,sum((Case When d.status!='Closed' Then d.Balance Else 0 End )+(Case When t.status!='Closed' Then t.Balance Else 0 End))
FROM @DataSource d
inner Join @DataSource1 t
on t.ID= d.ID
Group By d.ID, t.ID
enter code herehttps://stackoverflow.com/questions/54335681
复制相似问题