如何用单条SQL语句统计指定物品最后盘点后的拣选次数？

可以通过**CTE（公共表表达式）**或子查询的方式，将两步操作合并为单条SQL语句，直接得到包含Item、LastCountDate、AmountofPicks字段的结果。以下是两种常用实现方式：

方式一：使用CTE（可读性更强） #

WITH LatestCounts AS (
    -- 获取指定物品的最新盘点日期
    SELECT 
        Item,
        MAX(CountDate) AS LastCountDate
    FROM Count
    WHERE Item IN (1234, 234, 345)
    GROUP BY Item
)
-- 关联拣选表统计最新盘点后的拣选次数
SELECT
    lc.Item,
    lc.LastCountDate,
    COUNT(p.Item) AS AmountofPicks
FROM LatestCounts lc
LEFT JOIN Pick p 
    ON lc.Item = p.Item 
    AND p.PickDate > lc.LastCountDate
GROUP BY lc.Item, lc.LastCountDate;

方式二：直接子查询关联 #

SELECT
    c.Item,
    MAX(c.CountDate) AS LastCountDate,
    COUNT(p.Item) AS AmountofPicks
FROM Count c
LEFT JOIN Pick p 
    ON c.Item = p.Item 
    AND p.PickDate > (SELECT MAX(CountDate) FROM Count WHERE Item = c.Item)
WHERE c.Item IN (1234, 234, 345)
GROUP BY c.Item;

关键说明 #

• 用LEFT JOIN而非INNER JOIN：确保物品在最新盘点后无拣选记录时，依然会出现在结果中，此时AmountofPicks显示为0。
• 日期过滤写在JOIN子句：避免WHERE子句过滤掉无拣选记录的物品。

内容的提问来源于stack exchange，提问作者Ukkie