计算列返回值 [英] Count a column returned value

问题描述

我有以下SQL代码。我想计算其中返回值的数量。列名是wo.Number AS WorkOrderNumber。



以下是我的代码：

Hi, I have the following SQL codes. I want to count the number of returned value in it. The column name is wo.Number AS WorkOrderNumber.

Below are my codes:

DECLARE @ContainerHeaderId AS BIGINT = 1000037196 ; --added to deal with possible parameter sniffing issue
DECLARE @p_CultureName AS VARCHAR(10) = 'en-US'
DECLARE @count as int = 0

SELECT
     pch.ContainerHeaderId
    ,wo.Number AS WorkOrderNumber -<--- this is the column i want to count
    ,pch.FocusedFactoryId
    ,pcd.ContainerHeaderIdParent
    ,pcd.ContainerDetailId
    ,pcc.UnitHeaderId
    ,pcc.ContainerContentId
    ,pch.QuantityIntendingToPackage
    ,so.Number As SalesOrderNumber
    ,so.NumberPurchaseOrder --As PurchaseOrderNumber
    ,rc.NumberInternal
    ,rc.NumberInternal AS PartNumberInternal
    ,rc.RevisionCustomer
    ,rc.RevisionCustomerId  As RevisionCustomerID
    ,rc.FamilyId
    ,pcc.QuantityItem
    ,pcc.SalesOrderGroupVersionId AS SalesOrderGroupVersionID
    ,pcd.Width
    ,pcd.Length
    ,pcd.Height
    ,pcd.Weight
    ,pcd.Location
    ,ucs.Value AS ContainerStatus
    ,ucst.Value AS ContainerState
    ,uct.Value AS ContainerType
    ,ud.Value AS UomDimensional
    ,uw.Value As UomWeight
    ,ff.Site AS SiteName
    ,ff.Plant AS Plant
    ,ff.SiteId
    ,uvw.ProductVersionId
    ,uvw.SerialNumberCustomer
    ,uvw.SerialNumberInternal
    ,hsh.DateJdeTransferEnd
    ,rc.ItemHeaderId
    ,pch.DateCreated
FROM
    ProductionControl.ContainerHeader AS pch
    LEFT JOIN ProductionControl.ContainerContent AS pcc
          ON pcc.ContainerHeaderId = pch.ContainerHeaderId
          AND pcc.DateEffectiveOut = '99991231 23:59:59.999'
    INNER JOIN ProductionControl.ContainerDetail AS pcd
          ON pcd.ContainerHeaderId = pch.ContainerHeaderId
          AND pcd.DateEffectiveOut = '99991231 23:59:59.999'
    LEFT JOIN ProductionControl.HandshakeDetail AS hsd
          ON hsd.ContainerHeaderId = pch.ContainerHeaderId
          AND hsd.DateEffectiveOut = '99991231 23:59:59.999'
    LEFT JOIN ProductionControl.HandshakeHeader AS hsh
          ON hsh.HandshakeHeaderId= hsd.HandShakeHeaderId
    LEFT JOIN UnitSetup.SalesOrderGroupVersion AS sogv

          ON sogv.SalesOrderGroupVersionId = pcc.SalesOrderGroupVersionId
    LEFT JOIN UnitSetup.SalesOrderGroup AS sog
          ON sog.SalesOrderGroupId = sogv.SalesOrderGroupId
    LEFT JOIn UnitSetup.SalesOrder AS so
          ON so.SalesOrderId = sog.SalesOrderId
    LEFT JOIN utf_LocalizedReferenceBySelector(@p_CultureName, 'ContainerStatus') AS ucs
          ON ucs.ReferenceId = pcd.ReferenceIdContainerStatus
    LEFT JOIN utf_LocalizedReferenceBySelector(@p_CultureName, 'ContainerState') AS ucst
          ON ucst.ReferenceId = pcd.ReferenceIdContainerState
    LEFT JOIN utf_LocalizedReferenceBySelector(@p_CultureName, 'ContainerType') AS uct
          ON uct.ReferenceId = pch.ReferenceIdContainerType
    LEFT JOIN utf_LocalizedReferenceBySelector(@p_CultureName, 'UomDimensional') AS ud
          ON ud.ReferenceId = pcd.ReferenceIdUomDimensional
    LEFT JOIN utf_LocalizedReferenceBySelector(@p_CultureName, 'UomWeight') AS uw
          ON uw.ReferenceId = pcd.ReferenceIdUomWeight
    LEFT JOIN UnitSetup.UnitIdentity AS uvw
          ON uvw.UnitHeaderId=pcc.UnitHeaderId
          AND uvw.DateEffectiveOut = CAST('99991231 23:59:59.999' AS DATETIME2(3))
    LEFT JOIN UnitSetup.WorkOrderHeader AS wo
          ON wo.WorkOrderHeaderId=uvw.WorkOrderHeaderId
    LEFT JOIN dbo.vw_Item AS rc
          ON RC.RevisionCustomerId = pcc.RevisionCustomerId
    LEFT JOIN dbo.vw_SiteFocusedFactory AS ff
          ON FF.FocusedFactoryId = pch.FocusedFactoryId
WHERE
        pch.containerHeaderId = @ContainerHeaderId

推荐答案

一般来说，您可以执行以下操作它被称为子查询：

Generally you can do the following which is called a subquery:

select count(WorkOrderNumber) from ( your query here )

这篇关于计算列返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！