SQL Server 2012:如何通过多个表连接获取计数组

There's an invoice table, with the person who has created the invoice. A person can belong to multiple offices, only one main office per person but same person can have multiple roles per office.

有一个发票表格，上面有开发发票的人。一个人可以属于多个办公室，每个人只有一个主办公室，但每个办公室可以有多个角色。

declare @person table (personid int)
declare @office table (officeid int, officename varchar(10))
declare @personoffice table (personid int, officeid int, mainoffice bit, personrole varchar(10))
declare @invoice table (personid int)

insert into @person values (1), (2), (3), (4)
insert into @office values (1, 'office1'), (2, 'office2'), (3, 'office3'), (4, 'office4')
insert into @personoffice values (1, 1, 1, 'role1'), (1, 1, 1, 'role2'), (1, 2, 0, 'role1'), (1, 3, 0, 'rolex'), (2, 2, 1, 'role1'), (2, 2, 1, 'role2'), (2, 3, 0, 'rolex'), (3, 3, 1, 'role1'), (3, 4, 0, 'role2')
insert into @invoice values (1), (1), (1), (2), (2), (3), (3), (3), (3), (3)

So for this example we have 3 persons, they belong to multiple offices but only one main office each but some persons have multiple roles per office. They have each created multiple invoices.

在这个例子中，我们有3个人，他们属于多个办公室但每个只有一个主办公室但是有些人每个办公室有多个角色。他们各自创建了多个发票。

I can get the number of invoices per person with:

我可以得到每个人的发票号码:

select 
    i.personid, 
    count(*) InvoiceCountByPerson
from 
    @invoice i
inner join 
    @person p on p.personid = i.personid
group by 
    i.personid

which returns:

personid    InvoiceCountByPerson
-------------------------------- 
    1               3
    2               2
    3               5

I need to get number of invoices by main office name. Person1 whose main office is office1 created 3 invoices, Person2 whose main office is office2 created 2 invoices, and Person3 whose main office is office3 created 5 invoices so expected result:

我需要以主要办公室的名字取得发票号码。个人1的主要办公地点是office1创建了3张发票，个人2的主要办公地点是office2创建了2张发票，个人3的主要办公地点是office3创建了5张发票。

officename  InvoiceCountByOfficeName 
------------------------------------
office1              3
office2              2
office3              5

This doesn't work:

这并不工作:

select 
    o.officename,
    count(*) InvoiceCountByOfficeName
from 
    @invoice i
inner join 
    @person p on p.personid = i.personid
inner join 
    @personoffice po on po.personid = p.personid AND po.mainoffice = 1
inner join 
    @office o on o.officeid = po.officeid
group by 
    o.officename

as it returns:

它返回:

officename  InvoiceCountByOfficeName 
-------------------------------------
office1                 6
office2                 4
office3                 5

As the same person has multiple mainoffice = 1 records with different roles, I need to have some sort of distinct on the @personoffice join. Millions of invoices too so need to take performance into consideration.

由于同一个人有多个mainoffice = 1记录，且角色不同，我需要在@personoffice连接上有某种不同的记录。数以百万计的发票也需要考虑性能。

2 个解决方案

#1

You are so close... All you had to do is use a derived table instead of using the @personoffice table directly:

你是如此接近……您只需使用派生表，而不是直接使用@personoffice表:

select 
    o.officename,
    count(*) InvoiceCountByOfficeName
from 
    @invoice i
inner join 
    @person p on p.personid = i.personid
inner join 
    (
        select distinct personid, officeid
        from @personoffice
        where mainoffice = 1
    )
     po on po.personid = p.personid 
inner join 
    @office o on o.officeid = po.officeid
group by 
    o.officename

Results:

结果:

officename InvoiceCountByOfficeName
---------- ------------------------
office1    3
office2    2
office3    5

#2

    select 
        o.officename,
        count(*) InvoiceCountByOfficeName
    from 
        @invoice i
    inner join 
        @person p on p.personid = i.personid
    inner join 
        (
        select distinct personid,officeid,mainoffice from @personoffice
        ) po on po.personid = p.personid AND po.mainoffice = 1
    inner join 
        @office o on o.officeid = po.officeid
    group by 
        o.officename

Thanks

谢谢

#1