如何在多个表中使用T-SQL Group By？

I have a table of suppliers, and tables Computers, Cameras, Displays all containing the SupplierID field.

我有一个供应商表,并且表格Computers,Cameras,Displays都包含SupplierID字段。

I am trying to do a T-SQL that will list all the suppliers, with a count of all rows. I can do them one at a time with:

我正在尝试做一个将列出所有供应商的T-SQL,其中包含所有行的计数。我可以一次做一个:

SELECT SupplierID, COUNT(dbo.Computers.ComputerID) as Computers 
FROM Supplier INNER JOIN
    Computers ON Supplier.SupplierID = Computers.SupplierID
GROUP BY SupplierID

How can I change this to include the other tables - like cameras, displays etc...

如何更改此项以包括其他表格 - 如相机,显示器等...

4 个解决方案

#1

Assuming that a suppliers may not supply each type of product, you will want to use outer joins. If you use inner joins you will only get back suppliers that supply at least one of each production. You also want to count the distinct ProductId for each product type. Otherwise you will get a mulitplication affect. (For example Supplier 1 provides Computers 1 & 2 and Displays 10 & 11, you will get back four rows of Computer 1 Display 10, Computer 1 Display 11, Computer 4 and Display 11.)

假设供应商可能不提供每种类型的产品,您将需要使用外部连接。如果您使用内部联接,您将只返回至少提供每个生产中的一个的供应商。您还需要为每种产品类型计算不同的ProductId。否则你会受到多重影响。 (例如,供应商1提供计算机1和2以及显示器10和11,您将获得四行计算机1显示器10,计算机1显示器11,计算机4和显示器11)。

Building on gbn's answer:

以gbn的答案为基础:

SELECT
    Supplier.SupplierID,
    COUNT(distinct Computers.ComputerID) as Computers,
    COUNT(distinct Displays.DisplayID) as Displays,
    COUNT(distinct Foos.FooID) as Foos,
    COUNT(distinct Bars.BarID) as Bars
FROM Supplier
LEFT OUTER JOIN Computers 
    ON Supplier.SupplierID = Computers.SupplierID
LEFT OUTER JOIN Displays 
    ON Supplier.SupplierID = Displays.SupplierID
LEFT OUTER JOIN Foos 
    ON Supplier.SupplierID = Foos.SupplierID
LEFT OUTER JOIN Bars 
    ON Supplier.SupplierID = Bars.SupplierID
GROUP BY
    Supplier.SupplierID

#2

I don't want to repeat with others have posted, but I agree that you join all the tables and do the counts.

我不想重复与其他人发布的内容,但我同意你加入所有表并进行计数。

the thing you need to consider (and this is a individual schema consideration) is that you may need to left join the tables. If you inner join them, you may lose rows if you have NULLs in other fields. These fields would be optional fields to the table.

你需要考虑的事情(这是一个单独的模式考虑)是你可能需要离开加入表。如果你内部加入它们,如果你在其他字段中有NULL,则可能会丢失行。这些字段是表的可选字段。

#3

Because you have multiple SupplierID columns, I'm surprised your original query worked... The DB engine does not know to use SupplierID from the Supplier or Computers table...

因为您有多个SupplierID列,所以我很惊讶您的原始查询工作...数据库引擎不知道使用Supplier或Computers表中的SupplierID ...

Edit: corrected for OUTER JOINs!

编辑:更正了OUTER JOIN!

SELECT
    Supplier.SupplierID,
    COUNT(Computers.ComputerID) as Computers,
    COUNT(Displays.DisplayID) as Displays,
    COUNT(Foos.FooID) as Foos,
    COUNT(Bars.BarID) as Bars
FROM
    Supplier
    LEFT JOIN
    Computers ON Supplier.SupplierID = Computers.SupplierID
    LEFT JOIN
    Displays ON Supplier.SupplierID = Displays.SupplierID
    LEFT JOIN
    Foos ON Supplier.SupplierID = Foos.SupplierID
    LEFT JOIN
    Bars ON Supplier.SupplierID = Bars.SupplierID
GROUP BY
    Supplier.SupplierID

#4


SELECT 
  SupplierID, COUNT(dbo.Computers.ComputerID), COUNT(dbo.Cameras.SupplierID) as Computers 
FROM Supplier 
INNER JOIN Computers ON Supplier.SupplierID = Computers.SupplierID 
INNER JOIN Cameras ON Supplier.SupplierID = Cameras.SupplierID
GROUP BY SupplierID

#1