选择 b.[count] 从 b 其中 b.时间戳 > a.timestamp 对于每个唯一值？

Question

解决方案在底部。

我有两张表:一张是手工盘点，另一张是收货盘点。

库存盘点表:

units_counted13765102

Answer 1

我使用row_number()函数为每个项目的每个时间戳生成行号，然后过滤最上面的1。我将该查询放在一个可以重用的公共表表达式(CTE)中。

WITH inv_count AS (
SELECT  ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY [timestamp] desc) number, 
[timestamp], item_id, units_counted
FROM    inventory_count
)
SELECT  [timestamp], item_id, units_counted units
FROM    inv_count
WHERE   number = 1
UNION
SELECT  r.[timestamp], r.item_id, r.units_received units
FROM    inventory_received r
inner join inv_count c on r.item_id = c.item_id
WHERE   r.[timestamp] > c.[timestamp]
and c.number = 1

这里ROW_NUMBER()函数的详细说明

Answer 2

有点难看，我相信有更有效的方法，但非常肯定这将得到您想要的结果(可能需要额外处理计数和接收表之间匹配的时间戳以解决冲突):

select
timestamp,
item_id,
units_received as units
from
inventory_count
where
(item_id, timestamo) in (
select
combined.item_id,
max(combined.timestamp)
from
(
select
item_id,
timestamp
from
inventory_count
union
select
item_id,
from
inventory_received
) combined
group by
combined.item_id
)
union
select
timestamp,
item_id,
units_received as units
from
inventory_received
where
(item_id, timestamp) in (
select
combined.item_id,
max(combined.timestamp)
from
(
select
item_id,
timestamp
from
inventory_count
union
select
item_id,
from
inventory_received
) combined
group by
combined.item_id
)
order by item_id, timestamp;

Answer 3

表'count'中的项目具有相同的id但不同的日期和'count number'，您只需要每个id的最新一个。关键是:

SELECT *
FROM (
SELECT timestamp, item_id, units_counted
FROM `count`
ORDER BY timestamp DESC
)
GROUP BY item_id

上面的代码只会为每个id和留下最新的时间

在MySQL中工作，不确定是否可以在SQL Server中工作。