SQL Server查询问题（计数可能？）

Question

我有以下查询：

SELECT A.shipment_id 
     ,B.box_id 
     ,A.shipment_status 
FROM shipments A 
join boxes B on A.shipment_id = B.shipment_id 
where A.shipment_status = 2 
Group by B.box_id, A.shipment_id, A.shipment_status 

返回的结果集，看起来像这样：

shipment_id，box_id，shipment_status
101，boxA，2
101，boxB，2
101，boxC，2
102，box101，2
102，box102，2
103，boxA1，2
103，boxA2，2

我想是这样返回的东西代替（示出的每一批盒的总量）：

shipment_id，箱计数，shipment_status
101，3个，2
102，2,2
103,2 2

我该如何实现这个目标？

谢谢！

Answer 1

SELECT A.shipment_id 
     ,COUNT(*) AS boxcount 
     ,A.shipment_status 
FROM shipments A 
join boxes B on A.shipment_id = B.shipment_id 
where A.shipment_status = 2 
Group by A.shipment_id, A.shipment_status 

只是需要从GROUP BY删除box_id，并使用COUNT，正如你在标题说。

Answer 2

试试这个：

SELECT A.shipment_id 
    , count(1) 
    , A.shipment_status 
    FROM shipments A 
    join boxes B on A.shipment_id = B.shipment_id 
where A.shipment_status = 2 
Group by A.shipment_id, A.shipment_status