SELECTb.[count]FROMbWHEREb.timestamp>a.timestamp對于每個唯一值？

底部的解決方案。

我有兩張表：一張記錄手動庫存盤點，另一張記錄庫存接收盤點。

庫存盤點表：

庫存收貨表：

我正在嘗試創建一個視圖，該視圖將顯示“計數”表中每個 item_id 的最新記錄。

然后，我嘗試將這些與“已接收”表中的記錄合并，其中它們的時間戳大于相同 item_id 的最新“計數”記錄。

期望的輸出：

目前，我匯總了以下查詢：

SELECT
  a.[timestamp], a.sales_item_id, a.sales_item_name, a.unit_cost, a.unit_count, a.unit_cost_total, b.sales_subcat_name, b.sales_category_name, b.sales_department_name
FROM
  [DB].[dbo].[inventory_count] as a
  LEFT JOIN [DB].[dbo].[Inventory_Categorized] as b
  ON a.sales_item_id = b.sales_item_id
WHERE
  [timestamp] = (SELECT MAX([timestamp]) FROM inventory_count i WHERE i.sales_item_id = a.sales_item_id)

UNION ALL

SELECT
  a.[timestamp], a.sales_item_id, a.sales_item_name, a.unit_cost, a.units_received, a.unit_cost_total, b.sales_subcat_name, b.sales_category_name, b.sales_department_name
FROM
  inventory_count as c,
  inventory_received as a
  LEFT JOIN [DB].[dbo].[Inventory_Categorized] as b
  ON a.sales_item_id = b.sales_item_id
WHERE
  a.[timestamp] > (SELECT MAX([timestamp]) FROM inventory_count i WHERE i.sales_item_id = a.sales_item_id)

它從計數中獲取單個最新時間戳，而不是每個 item_id 的最新時間戳。我希望社區可以為我指明正確的方向。

感謝大家。

編輯：感謝@CaptainPsycho，我能夠朝著正確的方向前進并得出一個實用的解決方案，如下所示：

WITH cte_count AS (
    SELECT [timestamp],
           sales_item_id,
           unit_count AS units,
           sales_item_name,
           ROW_NUMBER() OVER (PARTITION BY sales_item_id
                              ORDER BY [timestamp] DESC)
                        AS row_number
    FROM inventory_count
) SELECT [timestamp], sales_item_id, units, sales_item_name
FROM cte_count
WHERE row_number = 1

UNION

SELECT r.[timestamp], r.sales_item_id, r.units_received AS units, r.sales_item_name
FROM inventory_received AS r, cte_count AS c
WHERE r.[timestamp] > c.[timestamp] and c.row_number = 1

UNION

SELECT r.[timestamp], r.sales_item_id, r.units_received AS units, r.sales_item_name
FROM inventory_received r
LEFT JOIN cte_count c ON c.sales_item_id = r.sales_item_id
WHERE c.sales_item_id IS NULL

它可能不是很好，但它正在發揮作用，我學到了一些東西！

也感謝其他所有人的時間、精力和考慮。多么了不起的社區！

繼續下一個任務；）

uj5u.com熱心網友回復：

我使用該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()函式的詳細解釋here

uj5u.com熱心網友回復：

有點難看，我敢肯定有一種更有效的方法，但很確定這會給你帶來想要的結果（可能需要額外處理與你的計數和接收到的表之間匹配的時間戳以解決沖突）：

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;

uj5u.com熱心網友回復：

'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中是否有效。

標籤：sql sql服务器

上一篇：對自身進行表遞回查找

下一篇：將秘密用戶名和密碼添加到資料工廠中的SoapAPI呼叫