最高的返回空

Question

我在下面具有以下视图。当我使用顶部（1）子句时，第二个嵌套选择始终返回零，但是当我删除此条款时，它会按预期返回数据，而行的行比所需的行还多。有人看到任何可以解释的吗？

    SELECT TOP (100) PERCENT
    a.ITEMID AS Model
   ,id.CONFIGID
   ,id.INVENTSITEID AS SiteId
   ,id.INVENTSERIALID AS Serial
   ,it.ITEMNAME AS Description
   ,CASE WHEN it.DIMGROUPID LIKE '%LR-Y' THEN 'Y'
         ELSE 'N'
    END AS SerialNumberReqd
   ,ISNULL(it.PRIMARYVENDORID, N'') AS Vendor
   ,ISNULL(vt.NAME, N'') AS VendorName
   ,id.INVENTLOCATIONID AS Warehouse
   ,id.WMSLOCATIONID AS Bin
   ,ISNULL(CONVERT(varchar(12), CASE WHEN C.DatePhysical < '1901-01-01'
                                     THEN NULL
                                     ELSE C.DatePhysical
                                END, 101), N' ') AS DeliveryDate
   ,CASE WHEN (a.RESERVPHYSICAL > 0
               OR C.StatusIssue = 1)
              AND c.TransType = 0 THEN C.PONumber
         ELSE ''
    END AS SoNumber
   ,'' AS SoDetail
   ,ISNULL(C.PONumber, N'') AS RefNumber
   ,ISNULL(CONVERT(varchar(12), CASE WHEN ins.ProdDate < '1901-01-01'
                                     THEN NULL
                                     ELSE ins.PRODDATE
                                END, 101), N' ') AS DateReceived
   ,it.STKSTORISGROUPID AS ProdGroup
   ,ISNULL(CONVERT(varchar(12), CASE WHEN ins.ProdDate < '1901-01-01'
                                     THEN NULL
                                     ELSE ins.PRODDATE
                                END, 101), N' ') AS ProductionDate
   ,it.ITEMGROUPID
   ,it.STKSTORISGROUPID AS MerchandisingGroup
   ,CASE WHEN a.postedValue = 0
         THEN (CASE WHEN D.CostAmtPosted = 0 THEN D.CostAmtPhysical
                    ELSE D.CostAmtPosted
               END)
         ELSE a.POSTEDVALUE
    END AS Cost
   ,CASE WHEN a.PHYSICALINVENT = 0 THEN a.Picked
         ELSE a.PhysicalInvent
    END AS PhysicalOnHand
   ,ins.STKRUGSQFT AS RugSqFt
   ,ins.STKRUGVENDSERIAL AS RugVendSerial
   ,ins.STKRUGVENDDESIGN AS RugVendDesign
   ,ins.STKRUGEXACTSIZE AS RugExactSize
   ,ins.STKRUGCOUNTRYOFORIGIN AS RugCountryOfOrigin
   ,ins.STKRUGQUALITYID AS RugQualityId
   ,ins.STKRUGCOLORID AS RugColorId
   ,ins.STKRUGDESIGNID AS RugDesignId
   ,ins.STKRUGSHAPEID AS RugShapeId
   ,CASE WHEN (a.AVAILPHYSICAL > 0) THEN 'Available'
         WHEN (id.WMSLOCATIONID = 'NIL') THEN 'Nil'
         WHEN (a.RESERVPHYSICAL > 0)
              AND (c.TransType = 0) THEN 'Committed'
         WHEN (a.RESERVPHYSICAL > 0) THEN 'Reserved'
         WHEN (id.WMSLOCATIONID LIKE '%-Q') THEN 'Damaged'
         WHEN (a.Picked > 0) THEN 'Picked'
         ELSE 'UNKNOWN'
    END AS Status
   ,'' AS ReasonCode
   ,'' AS BaseModel
   ,ISNULL(CAST(ins.STKSTORISCONFIGINFO AS nvarchar(1000)), N'') AS StorisConfigInfo
   ,ISNULL(C.ConfigSummary, N'') AS ConfigSummary
FROM
    dbo.INVENTSUM AS a WITH (NOLOCK)
INNER JOIN dbo.INVENTDIM AS id WITH (NOLOCK)
    ON id.DATAAREAID = a.DATAAREAID
       AND id.INVENTDIMID = a.INVENTDIMID
LEFT OUTER JOIN dbo.INVENTTABLE AS it WITH (NOLOCK)
    ON it.DATAAREAID = a.DATAAREAID
       AND it.ITEMID = a.ITEMID
LEFT OUTER JOIN dbo.VENDTABLE AS vt WITH (NOLOCK)
    ON vt.DATAAREAID = it.DATAAREAID
       AND vt.ACCOUNTNUM = it.PRIMARYVENDORID
LEFT OUTER JOIN dbo.INVENTSERIAL AS ins WITH (NOLOCK)
    ON ins.DATAAREAID = id.DATAAREAID
       AND ins.INVENTSERIALID = id.INVENTSERIALID
LEFT OUTER JOIN (SELECT TOP (1)
                    itt.ITEMID
                   ,invt.INVENTSERIALID
                   ,itt.DATEPHYSICAL AS DatePhysical
                   ,itt.TRANSREFID AS PONumber
                   ,itt.TRANSTYPE AS TransType
                   ,itt.STATUSISSUE AS StatusIssue
                   ,dbo.stkRowsToColumn(itt.INVENTTRANSID, 'STI') AS ConfigSummary
                   ,itt.RECID
                 FROM
                    dbo.INVENTTRANS AS itt WITH (NOLOCK)
                 INNER JOIN dbo.INVENTDIM AS invt WITH (NOLOCK)
                    ON invt.DATAAREAID = itt.DATAAREAID
                       AND invt.INVENTDIMID = itt.INVENTDIMID
                 WHERE
                    (itt.DATAAREAID = 'STI')
                    AND (itt.TRANSTYPE IN (0, 2, 3, 8))
                    AND (invt.INVENTSERIALID <> '')
                 ORDER BY
                    itt.RECID DESC) AS C
    ON C.ITEMID = a.ITEMID
       AND C.INVENTSERIALID = id.INVENTSERIALID
LEFT OUTER JOIN (SELECT TOP (1)
                    itt2.ITEMID
                   ,invt2.INVENTSERIALID
                   ,itt2.COSTAMOUNTPOSTED AS CostAmtPosted
                   ,itt2.COSTAMOUNTPHYSICAL + itt2.COSTAMOUNTADJUSTMENT AS CostAmtPhysical
                   ,itt2.RECID
                 FROM
                    dbo.INVENTTRANS AS itt2 WITH (NOLOCK)
                 INNER JOIN dbo.INVENTDIM AS invt2 WITH (NOLOCK)
                    ON invt2.DATAAREAID = itt2.DATAAREAID
                       AND invt2.INVENTDIMID = itt2.INVENTDIMID
                 WHERE
                    (itt2.DATAAREAID = 'STI')
                    AND (itt2.TRANSTYPE IN (0, 2, 3, 4, 6, 8))
                    AND (invt2.INVENTSERIALID <> '')
                 ORDER BY
                    itt2.RECID DESC) AS D
    ON D.ITEMID = a.ITEMID
       AND D.INVENTSERIALID = id.INVENTSERIALID
WHERE
    (a.DATAAREAID = 'STI')
    AND (a.CLOSED = 0)
    AND (a.PHYSICALINVENT > 0)
    AND (it.ITEMGROUPID LIKE 'FG-%'
         OR it.ITEMGROUPID = 'MULTISHIP')
ORDER BY
    SiteId
   ,Warehouse

Answer 1

大概，子查询中的最高值不符合后续的join条件。也就是说，无法满足这种情况：

D.ITEMID = a.ITEMID AND D.INVENTSERIALID = id.INVENTSERIALID

您使用的是left outer join，因此NULL值已填充。

编辑：

重新征服。当您使用top 1运行它时，没有值（至少对于两个变量的某些组合）。因此，对于这些值，NULL将被填充。毕竟，top 1（带有或不带括号的带有或不带括号）仅返回一排。

运行它以返回多行时，大概有匹配项。对于匹配的行，将相应的值放置。这是left outer join的工作方式。

Answer 2

戈登的答案是正确的，为什么我在删除顶部时会得到几行而没有。有问题的子查询是返回Invent Trans表中的所有行（500万 ），因此当我使用TOP时，它只是获得了没有任何东西的第一行。我意识到，当我尝试在顶部子句中尝试随机高值（例如50000）时。

最终的修复是更改C和D子征服上的左外部连接以进行交叉应用，然后更改在哪里筛选表格以更好地过滤表（例如itt.itemid = a.Itemid = a.itemid and Invt1.inventserialid = id。Inventserialid）。使用它，我能够按预期使用顶部1。