我有两张table
table
AnimalID | Date表2
AnimalID | ConditionID
``` `AnimalID` 是表的主键，包括动物的出生日期。
表2给出了 `AnimalID` 有什么病吗 `ConditionID` 这只动物已经吃过了。因此 `AnimalID` 可在表2中多次出现。我想知道第79、80、1000和90种情况。SELECT DATEPART(year, Table.Date) AS x_Year,
COUNT(Distinct Table.AnimalID) AS N_Killed,
COUNT(CASE WHEN Table2.ConditionId =79 THEN 1 END) AS Col1,
COUNT(CASE WHEN Table2.ConditionId =80 THEN 1 END) AS Col2,
COUNT(CASE WHEN Table2.ConditionId =1000 THEN 1 END) AS Col3,
COUNT(CASE WHEN Table2.ConditionId =90 THEN 1 END) AS Col4
FROM Table LEFT JOIN
Table2 ON Table.AnimalID = Table2.AnimalID
GROUP BY DATEPART(year, Date)
ORDER BY DATEPART(year, Date)
但有时动物有多种情况，所以我想一个额外的专栏，显示如果动物的数量有任何条件
2条答案
按热度按时间pbgvytdp1#
我希望你用
distinct因为你不想把同一只动物数两次。要计算出任何一种情况下的动物数量，只需使用in```select datepart(year, a.date) as x_year,
count(distinct a.animalid) as n_killed,
count(distinct case when b.conditionid = 79 then a.animalid end) as col1,
count(distinct case when b.conditionid = 80 then a.animalid end) as col2,
count(distinct case when b.conditionid = 1000 then a.animalid end) as col3,
count(distinct case when b.conditionid = 90 then a.animalid end) as col4,
count(distinct case when b.conditionid in ('79','80','1000',90') then a.animalid end) as col5
from table a
left join table2 b on a.animalid = b.animalid
group by datepart(year, a.date)
order by datepart(year, a.date);
wydwbb8l2#
基本上你想做的和你杀死牛的时候做的是一样的，但是只有当动物生病的时候。col4只是键入animalid，因为有一个条件，所以通过使用distinct来计算唯一的animalid，您就得到了所需的信息。