需要SQL SERVER 2008 DB的SQL查询帮助

Please refer the image for our table structure and expected output. I need all the rows from table 1 along with a new column that gives the count of plan number for each batch id from the second table.

请参考图像我们的表结构和预期输出。我需要表1中的所有行以及一个新列，该列给出第二个表中每个批ID的计划编号计数。

2 个解决方案

#1

It looks like a pretty simple GROUP BY:

它看起来像一个非常简单的GROUP BY：

SELECT T1.[Batch Id], BatchName, Date, Status, COUNT(PlanNumber) AS PLANCOUNT
FROM Table1 T1
JOIN Table2 T2 ON T1.[Batch Id] = T2.[Batch Id]
GROUP BY T1.[Batch Id], BatchName, Date, Status

#2

You need to Join the tables (left join is better because it count miss rows from 1 where you have no plans), followed by a group

您需要加入表格（左连接更好，因为它计算从没有计划的1开始的错过行），然后是一个组

SELECT 
   T1.[Batch Id], 
   T1.[Batch Name], 
   T1.[Date], 
   T1.[Status], 
   COUNT(T2.PlanNUmber) AS PLANCOUNT
FROM
  Table1 T1
  LEFT JOIN Table2 T2 ON T1.[Batch Id] = T2.[Batch Id]
GROUP BY
   T1.[Batch Id], 
   T1.[Batch Name], 
   T1.[Date], 
   T1.[Status] 

As I stated if you do a LEFT JOIN then it will pick batches without Plans, but if you know for certain that there are no rows in Batches in T1 that have no rows in T2 then you are ok with an INNER JOIN.

正如我所说，如果你进行LEFT JOIN，那么它将选择没有计划的批次，但是如果你确定T1中的批次中没有T2中没有行的行，那么你可以使用INNER JOIN。

Personally I prefer the first as it's covers both cases nicely.

我个人更喜欢第一个，因为它很好地涵盖了两个案例。