时间戳

首页 > 解决方案 > SQL：显示最新条目

sql - SQL：显示最新条目

问题描述

我不是真正的开发人员，我今年才开始自学 VBA for Excel，两天前才开始使用 SQL...

也就是说：我正在尝试编写一个查询，该查询仅显示基于 ITTRANDATE 的每个 INVBTICKETNO 的最新条目，但我似乎无法弄清楚。

SELECT
  WHSNAME.WHSNNAME AS [Warehouse Name]
 ,VALID_BIN.BIN_TYPE AS T
 ,INVENTORY_BIN.INVBBINNO AS [Bin Number]
 ,INVENTORY_BIN.INVBITEMNO AS [Item Number]
 ,INVENTORY_ITEM_TAB.DESCR AS [Item Description]
 ,PRODUCT_CODE.DESCR AS [Product Code & Description]
 ,CUSTOMER.CSNAME AS [Customer Name]
 ,ORDERS.ORDER_NO AS [Oder No]
 ,INVENTORY_BIN.DATE_OF_INVENTORY AS [Date of Inventory]
 ,INVENTORY_BIN.INVBTICKETNO AS [Ticket No]
 ,INVENTORY_BIN.INVBQTYONHAND AS [Qty on Hand]
 ,ORDERS.QTY_ORDERED AS [Qty Ordered]
 ,INVENTORY_ITEM_TAB.SQUARE_FEET_UNIT AS MSF
 ,INVENTORY_BIN.EXTENDED_CST AS [Valuation?]
 ,INVENTORY_TRAN.ITSOURCE AS Source
 ,INVENTORY_TRAN.ITCOMMENT AS Comment
 ,MAX(INVENTORY_TRAN.ITTRANDATE) AS [Trans Date]
 ,ORDERS.FOR_INVT_FLG AS Flag
 ,SALESAGENT.SANAME AS [Sales Agent]
 ,ORDERS.CUST_PO_NO AS [Customer PO No]
FROM dbo.INVENTORY_BIN INVENTORY_BIN
INNER JOIN dbo.INVENTORY_ITEM_TAB INVENTORY_ITEM_TAB
  ON INVENTORY_BIN.INVBITEMNO = INVENTORY_ITEM_TAB.ITEM_NO
INNER JOIN dbo.WHSNAME WHSNAME
  ON INVENTORY_BIN.INVBWHSNCODE = WHSNAME.WHSNCODE
INNER JOIN dbo.VALID_BIN VALID_BIN
  ON INVENTORY_BIN.INVBBINNO = VALID_BIN.BIN_ID
    AND INVENTORY_BIN.INVBWHSNCODE = VALID_BIN.WHSNCODE
LEFT OUTER JOIN dbo.INVENTORY_TRAN INVENTORY_TRAN
  ON INVENTORY_BIN.INVBTICKETNO = INVENTORY_TRAN.ITTICKETNO
    AND INVENTORY_BIN.DATE_LST_ACTIVITY < INVENTORY_TRAN.ITTRANDATE
LEFT OUTER JOIN dbo.CUSTOMER CUSTOMER
  ON INVENTORY_BIN.CSCODE = CUSTOMER.CSCODE
LEFT OUTER JOIN dbo.ORDERS ORDERS
  ON INVENTORY_BIN.ORDER_NO = ORDERS.ORDER_NO
INNER JOIN dbo.PRODUCT_CODE PRODUCT_CODE
  ON INVENTORY_ITEM_TAB.PROD_CD = PRODUCT_CODE.PROD_CD
LEFT OUTER JOIN dbo.SALESAGENT SALESAGENT
  ON CUSTOMER.SACODE = SALESAGENT.SACODE
WHERE ORDERS.FOR_INVT_FLG = 'R'
GROUP BY INVENTORY_BIN.INVBITEMNO
        ,INVENTORY_BIN.INVBBINNO
        ,INVENTORY_BIN.INVBQTYONHAND
        ,INVENTORY_BIN.DATE_OF_INVENTORY
        ,INVENTORY_BIN.EXTENDED_CST
        ,INVENTORY_ITEM_TAB.DESCR
        ,INVENTORY_ITEM_TAB.SQUARE_FEET_UNIT
        ,WHSNAME.WHSNNAME
        ,VALID_BIN.BIN_TYPE
        ,INVENTORY_TRAN.ITSOURCE
        ,CUSTOMER.CSNAME
        ,ORDERS.ORDER_NO
        ,ORDERS.CUST_PO_NO
        ,ORDERS.QTY_ORDERED
        ,PRODUCT_CODE.DESCR
        ,SALESAGENT.SANAME
        ,ORDERS.FOR_INVT_FLG
        ,INVENTORY_BIN.INVBTICKETNO
        ,INVENTORY_TRAN.ITCOMMENT
ORDER BY [Date of Inventory]

标签： sqlgrouping

解决方案

我找到了https://kristiannielsen.livejournal.com/6745.html并实施了他们的“最快”解决方案。

除了看起来不是很快...返回 217 行需要 1 分 12 秒。如果您在远程工作，查询会运行得更慢吗？

SELECT
  WHSNAME.WHSNNAME AS [Warehouse Name]
 ,VALID_BIN.BIN_TYPE AS T
 ,INVENTORY_BIN.INVBBINNO AS [Bin Number]
 ,INVENTORY_BIN.INVBITEMNO AS [Item Number]
 ,INVENTORY_ITEM_TAB.DESCR AS [Item Description]
 ,PRODUCT_CODE.DESCR AS [Product Code & Description]
 ,CUSTOMER.CSNAME AS [Customer Name]
 ,ORDERS.ORDER_NO AS [Order Number]
 ,INVENTORY_BIN.DATE_OF_INVENTORY AS [Date of Inventory]
 ,tra1.ITTICKETNO AS [Ticket Number]
 ,INVENTORY_BIN.INVBQTYONHAND AS [Qty on Hand]
 ,ORDERS.QTY_ORDERED AS [Qty Ordered]
 ,INVENTORY_ITEM_TAB.SQUARE_FEET_UNIT AS MSF
 ,INVENTORY_BIN.EXTENDED_CST AS [Valuation?]
 ,tra1.ITSOURCE AS Source
 ,tra1.ITCOMMENT AS Comment
 ,tra1.ITTRANDATE AS [Trans Date]
 ,ORDERS.FOR_INVT_FLG AS Flag
 ,SALESAGENT.SANAME AS [Sales Agent]
 ,ORDERS.CUST_PO_NO AS [Customer PO Number]
FROM dbo.INVENTORY_TRAN tra1
**INNER JOIN (SELECT
    INVENTORY_TRAN.ITTICKETNO
   ,MAX(INVENTORY_TRAN.ITTRANDATE) AS ITTRANDATE
  FROM dbo.INVENTORY_TRAN
  GROUP BY INVENTORY_TRAN.ITTICKETNO) tra2
  ON tra1.ITTICKETNO = tra2.ITTICKETNO
    AND tra1.ITTRANDATE = tra2.ITTRANDATE**
INNER JOIN dbo.INVENTORY_BIN
  ON tra1.ITTICKETNO = INVENTORY_BIN.INVBTICKETNO
INNER JOIN dbo.INVENTORY_ITEM_TAB
  ON INVENTORY_BIN.INVBITEMNO = INVENTORY_ITEM_TAB.ITEM_NO
INNER JOIN dbo.PRODUCT_CODE
  ON INVENTORY_ITEM_TAB.PROD_CD = PRODUCT_CODE.PROD_CD
INNER JOIN dbo.ORDERS
  ON INVENTORY_BIN.ORDER_NO = ORDERS.ORDER_NO
INNER JOIN dbo.WHSNAME
  ON INVENTORY_BIN.INVBWHSNCODE = WHSNAME.WHSNCODE
INNER JOIN dbo.CUSTOMER
  ON INVENTORY_BIN.CSCODE = CUSTOMER.CSCODE
INNER JOIN dbo.SALESAGENT
  ON CUSTOMER.SACODE = SALESAGENT.SACODE
INNER JOIN dbo.VALID_BIN
  ON INVENTORY_BIN.INVBWHSNCODE = VALID_BIN.WHSNCODE
    AND VALID_BIN.BIN_ID = INVENTORY_BIN.INVBBINNO
WHERE ORDERS.FOR_INVT_FLG = 'R'
ORDER BY [Date of Inventory]

推荐阅读