使用多个表的计数

I want to count the number of different sheep, and I want it in one table.

我想要数不同绵羊的数量，我想把它放在一张桌子上。

Like this;

像这样;

Ewes | Rams | Lambs
 8   |   5  |  12

The query I try is this, but it doesn't work;

我尝试的查询是这样的，但它不起作用;

SELECT COUNT(e.EweID) AS 'Ewe', COUNT(r.RamID) AS 'Ram', COUNT(l.LambID) AS 'Lamb' 
FROM Sheep s 
    INNER JOIN Ewe e ON s.SheepID = e.EweID 
    INNER JOIN Ram r ON s.SheepID = r.RamID 
    INNER JOIN Lamb l ON s.SheepID = l.LambID 
WHERE s.FarmerID = '123'

I don't get what I'm doing wrong, this is my database ERD;

我不知道我做错了什么，这是我的数据库ERD;

4 个解决方案

#1

I don't think you need a FROM here at all:

我认为你根本不需要a:

select
  (select count(*) from Ram where Famerid = 123) as RamCount,
  (select count(*) from Ewe where Famerid = 123) as Count,
  (select count(*) from Lamb where Famerid = 123) as LambCount

(There is no relationship between the rows you are counting, do don't try and create one. Instead count each separately, wrapping it all in an outer select keeps everything in a single result row.)

(您正在计数的行之间没有关系，不要尝试创建一个。相反，要分别计数，将它们全部封装在一个外部选择中，将所有内容保存在一个结果行中。

#2

I think that the problem here is that you don't need an INNER JOIN but an OUTER JOIN ...

我认为这里的问题是你不需要内部连接而是外部连接……

SELECT COUNT(CASE WHEN e.EweID IN NOT NULL THEN e.EweID ELSE 0 END) AS 'Ewe', COUNT(r.RamID) AS 'Ram', COUNT(l.LambID) AS 'Lamb' 
FROM Sheep s 
    LEFT OUTER JOIN Ewe e ON s.SheepID = e.EweID 
    LEFT OUTER JOIN Ram r ON s.SheepID = r.RamID 
    LEFT OUTER JOIN Lamb l ON s.SheepID = l.LambID 
WHERE s.FarmerID = '123'

Take a look even at the case statement that I've added inside the first count(Ewe), to see a way to handle nulls in the count .

看看我在第一个count(Ewe)中添加的case语句，看看如何处理count中的nulls。

The Left Outer Join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.

左外部连接逻辑运算符返回满足第一个(顶部)输入与第二个(底部)输入的连接的每一行。它还返回第一个输入中没有匹配行的任何行。第二个输入中的不匹配行作为空值返回。如果参数列中不存在连接谓词，则每一行都是匹配行。

#3

Use correlated sub-selects to do the counting:

使用相关子选择进行计数:

SELECT (select COUNT(*) from Ewe e where s.SheepID = e.EweID) AS 'Ewe',
       (select COUNT(*) from Ram r where s.SheepID = r.RamID) AS 'Ram',
       (select COUNT(*) from Lamb l where s.SheepID = l.LambID) AS 'Lamb'
FROM Sheep s 

WHERE s.FarmerID = '123'

And you can also simply remove the WHERE clause to get all farms' counts.

您还可以简单地删除WHERE子句以获取所有农场的计数。

#4

DECLARE @Count1 INT;

SELECT  @Count1 = COUNT(*)
FROM    dbo.Ewe;

DECLARE @Count2 INT;

SELECT  @Count2 = COUNT(*)
FROM    dbo.Ram;

DECLARE @Count3 INT;

SELECT  @Count3 = COUNT(*)
FROM    dbo.Lamb;

SELECT  @Count1 AS 'Ewe' ,
        @Count2 AS 'Ram' , 
        @Count3 AS 'Lamb'

#1