选择一行到多行，行有另一个计算

Question

我有一个表Allowance (名称，价值，日期，津贴)

我有许多行值，用一个“名称”，我选择两个名称

我写信给下面的脚本

select s.name, coalesce(v.name, s.name) AS 'Name1'
from Allowance s left join
     (values ('A', 'Allowance1'),
             ('A', 'Allowance2')
     ) v(old_name, name)
     on v.old_name = s.name

和结果：

 Name|  Name1       | Value    | Date
-----+--------------+----------+---------
 A   |  Allowance1  |          | 2019-01-01
 A   |  Allowance2  |          | 2019-01-01

对于列Name1的值有其他计算(Value列) Ex：

With Allowance1: I have a script
select sum(value) from Allowance where Allowance='1' group by Name
Result: 145

With Allowance2: I have a script
select sum(value) from Allowance where Allowance='1' or Allowance='2' group by Name

Result: 123

如何选择下面的结果？

 Name|  Name1       | Value    | Date
-----+--------------+----------+---------
 A   |  Allowance1  |    145   | 2019-01-01
 A   |  Allowance2  |    123   | 2019-01-01

请帮我解决问题。

Answer 1

我想你想要这样的东西：

select s.name, coalesce(v.name, s.name) AS Name1, v.value
from (select name,
             sum(case when Allowance in (1) then value end) as value_1,
             sum(case when Allowance in (1, 2) then value end) as value_2
      from Allowance s
      group by name
     ) s left join
     (values ('A', 'Allowance1', value_1),
             ('A', 'Allowance2', value_2)
     ) v(old_name, name, value)
     on v.old_name = s.name