sql - 链接 0 个或多个缺失数据字段更新

Question

我有 2 个表格，我拥有的办公室列表和分配给办公室的收入列表

create table #Income(City varchar(50),Office varchar(50),YearsBudget money)
insert #Income
select 'London', null,     5000 UNION
select 'Paris',  null,     6000 UNION
select null,    'Sales',   7000 UNION
select 'London','Support',10000

create table #Offices(City varchar(50),Office varchar(50),Ratio float)
insert #Offices
select 'London','Research Lab'     ,.15 UNION
select 'London','Customer Services',.45 UNION
select 'London','Sales'            ,.05 UNION
select 'London','Admin'            ,.19 UNION
select 'London','Support'          ,.17 UNION
select 'Paris' ,'Sales'            ,.15 UNION
select 'Paris' ,'Admin'            ,.45 UNION
select 'Paris' ,'Support'          ,.05 UNION
select 'Madrid','Sales'            ,.45 UNION
select 'Madrid','Research Lab'     ,.25 

例如，在我来自#Income 的数据的第一行中，我有 5000 英镑，我必须将其分配给所有已知的伦敦办事处，在另一行中，我有 6000 英镑，我需要将其分配给所有已知的巴黎办事处

这可以在下面的SQL中实现

select o.City,o.Office, convert(money,i.YearsBudget/DATA.RSum*o.Ratio) as ThisYearsBudget
from #Income as i
Left Join #Offices as O
on i.City=o.City
Left join (select child.City,SUM(child.Ratio) as RSum from #Offices as child group by child.City ) DATA
ON i.city=DATA.City 
where i.Office is null

要将 7000 英镑分配给所有销售办事处，将 10000 英镑分配给伦敦支持办事处，将需要另外 2 个选择语句和一个进一步的选择语句来对 3 个分配 View 的结果进行求和和分组。我可以在一个简单的 View 中实现这一点吗？

Answer 1

示例数据:

create table Income(City varchar(50),Office varchar(50),YearsBudget money)
insert Income
select 'London', null,     5000 UNION
select 'Paris',  null,     6000 UNION
select null,    'Sales',   7000 UNION
select 'London','Support',10000;

create table Offices(City varchar(50),Office varchar(50),Ratio float)
insert Offices
select 'London','Research Lab'     ,.15 UNION
select 'London','Customer Services',.45 UNION
select 'London','Sales'            ,.05 UNION
select 'London','Admin'            ,.19 UNION
select 'London','Support'          ,.17 UNION
select 'Paris' ,'Sales'            ,.15 UNION
select 'Paris' ,'Admin'            ,.45 UNION
select 'Paris' ,'Support'          ,.05 UNION
select 'Madrid','Sales'            ,.45 UNION
select 'Madrid','Research Lab'     ,.25;

查询:

   select o.City,
          o.Office,
          ThisYearsBudget = SUM(convert(money,sq.YearsBudget*o.Ratio/sq.totalratio))
     from Offices o
     join (
          select i.city, i.office, i.yearsbudget, totalratio = sum(o.ratio)
            from income i
            join offices o on isnull(i.city,o.city)=o.city
                          and isnull(i.office,o.office)=o.office
        group by i.city, i.office, i.yearsbudget
          ) sq on isnull(sq.city,o.city)=o.city
              and isnull(sq.office,o.office)=o.office
 group by grouping sets ((o.City, o.Office)
                         --,() -- uncomment this line to see grand total
                        )
 order by City, Office;

结果:

|   CITY |            OFFICE | THISYEARSBUDGET |
------------------------------------------------
| London |             Admin |        940.5941 |
| London | Customer Services |       2227.7228 |
| London |      Research Lab |        742.5743 |
| London |             Sales |        785.9863 |
| London |           Support |      10841.5842 |
| Madrid |             Sales |       4846.1538 |
|  Paris |             Admin |       4153.8462 |
|  Paris |             Sales |            3000 |
|  Paris |           Support |        461.5385 |

SQL Fiddle Demo