SELECT [ProductionTasks].[ptID]
,[ProductionTasks].[wsID]
,[ptStyleNo]
,[ptStyle]
,[ptAmount]
,[ptDueDate]
,[ptCreatorID]
,[ptModifyID]
,[ptStatus]
,[ptCreationDate]
,Processes.*
,ProductionTaskItems.*
,Count(ProductionTaskItems.ptiID) as ItemCount
FROM [ProductionTasks]
LEFT JOIN ProductionTaskItems
ON [ProductionTasks].ptID=ProductionTaskItems.ptID
LEFT JOIN Processes
ON Processes.pID=ProductionTaskItems.ptID
,[ProductionTasks].[wsID]
,[ptStyleNo]
,[ptStyle]
,[ptAmount]
,[ptDueDate]
,[ptCreatorID]
,[ptModifyID]
,[ptStatus]
,[ptCreationDate]
,Processes.*
,ProductionTaskItems.*
,Count(ProductionTaskItems.ptiID) as ItemCount
FROM [ProductionTasks]
LEFT JOIN ProductionTaskItems
ON [ProductionTasks].ptID=ProductionTaskItems.ptID
LEFT JOIN Processes
ON Processes.pID=ProductionTaskItems.ptID
建议:把其它没有count的列加上不影响取值的聚合函数,比如说min,max,因为你查出来也是固定的,加上也没影响。或者就把没有count的列写到group by里。
,[ProductionTasks].[wsID]
,[ptStyleNo]
,[ptStyle]
,[ptAmount]
,[ptDueDate]
,[ptCreatorID]
,[ptModifyID]
,[ptStatus]
,[ptCreationDate]
,Processes.*
,ProductionTaskItems.*
,Count(ProductionTaskItems.ptiID) as ItemCount
FROM [ProductionTasks]
LEFT JOIN ProductionTaskItems
ON [ProductionTasks].ptID=ProductionTaskItems.ptID
LEFT JOIN Processes
ON Processes.pID=ProductionTaskItems.ptID
group by [ProductionTasks].[ptID]
,[ProductionTasks].[wsID]
,[ptStyleNo]
,[ptStyle]
,[ptAmount]
,[ptDueDate]
,[ptCreatorID]
,[ptModifyID]
,[ptStatus]
,[ptCreationDate]
,Processes.*
,ProductionTaskItems.*(*都要填满)
,[ProductionTasks].[wsID]
,[ptStyleNo]
,[ptStyle]
,[ptAmount]
,[ptDueDate]
,[ptCreatorID]
,[ptModifyID]
,[ptStatus]
,[ptCreationDate]
,Processes.*
,ProductionTaskItems.*
,(select Count(ProductionTaskItems.ptiID) FROM [ProductionTasks]
LEFT JOIN ProductionTaskItems
ON [ProductionTasks].ptID=ProductionTaskItems.ptID
LEFT JOIN Processes
ON Processes.pID=ProductionTaskItems.ptID) as ItemCountFROM [ProductionTasks]
LEFT JOIN ProductionTaskItems
ON [ProductionTasks].ptID=ProductionTaskItems.ptID
LEFT JOIN Processes
ON Processes.pID=ProductionTaskItems.ptID
SELECT [ProductionTasks].[ptID]
,[ProductionTasks].[wsID]
,[ptStyleNo]
,[ptStyle]
,[ptAmount]
,[ptDueDate]
,[ptCreatorID]
,[ptModifyID]
,[ptStatus]
,[ptCreationDate]
,Processes.*
,ProductionTaskItems.*
,Count(ProductionTaskItems.ptiID) as ItemCount
FROM [ProductionTasks]
LEFT JOIN ProductionTaskItems
ON [ProductionTasks].ptID=ProductionTaskItems.ptID
LEFT JOIN Processes
ON Processes.pID=ProductionTaskItems.ptID group by ItemCount