表值參數沒有為我的 sp 提取正確的數據？

http://sqlfiddle.com/#!3/b2643/5/1

http://sqlfiddle.com/#!3/b2643/5/0

上面的這個連結有我在 SQLfiddle 上創建的範例數據庫表。

我的儲存過程中有 3 個主表：

TransmittalSheet,DocumentInfo和TransmittalSiteLead. 每個 TS 可以有許多隨它一起發布的文件，並且每個 Transmittal 也可以有許多站點領導人員在不同的時間實施。我們的使用者通過使用 VB 應用程序執行報告，但它通過儲存過程從後端的 SQL Server 中提取數據。目前，他們可以單獨/單獨提取每個設施實施的所有文件，有時這些文件在同一日期在多個設施中共享或實施。

我的目標是在特定日期範圍內為一個部門（西部）一次為所有設施（其中 5 個）實施所有文件，然後最後我想處理重複項，或者我的意思是顯示相同的共享文件記錄一次。

在 Aaron Bertrand 的幫助下，他向我展示了表值參數的用法，現在我得到了部分結果，而當我執行原始單個儲存過程時看到的所有文件都有一個變數指向一個設施時間，所以我不確定我的查詢中缺少什麼，請指教

CREATE TYPE dbo.Facilities AS TABLE
(
 FacilityID TINYINT PRIMARY KEY
);
go


declare @F dbo.Facilities;
Insert @F Values (1), (2), (3), (4), (5), (7);

– 設施 6 被認為是分區去

create PROCEDURE [dbo].[GetImpDocsThree]
 @facilities dbo.Facilities ReadOnly,
 @facilitydivisionID tinyint = 6,
 @startdate smalldatetime = '06/16/2005',
 @enddate smalldatetime = '04/28/14'
AS
BEGIN
 SET NOCOUNT ON; 

 ;WITH f AS
 (
   SELECT FacilityID FROM @facilities
   UNION ALL SELECT @facilitydivisionID
 )

 SELECT  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,      f.FacilityID,tsl.TransmittalSiteLeadFacility, 
ts.TransmittalNumber, ts.TransmittalVersion,
FacilityImpDate = COALESCE(tsl.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
  FROM dbo.DocumentInfo AS di
  INNER JOIN dbo.TransmittalSheet AS ts 
    ON di.DocumentTransmittalImp = ts.TransmittalID
    LEFT OUTER JOIN dbo.TransmittalSiteLead AS tsl ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
  INNER JOIN f 
    ON tsl.TransmittalSiteLeadFacility = f.FacilityID

-- TSSiteLeadImpDate sometimes have different dates than TransmittalImpDate

-- Select documents implementing within the date range  

       Where transmittalSiteLeadImpDate 
   BETWEEN  @startdate AND @enddate
    order by di.DocumentNumber

   END


-- Above runs successfully but it returns partial data as I need something like this   for the last part in the date range query as it was working for the individual sp:

/*
-- Select documents implementing within the date range
           where ((SELECT dbo.TransmittalSiteLead.TransmittalSiteLeadImpDate 
           FROM dbo.TransmittalSiteLead
           --WHERE dbo.DocumentInfo.DocumentTransmittalImp = dbo.TransmittalSiteLead.TransmittalSiteLeadTSID
           where (dbo.TransmittalSiteLead.TransmittalSiteLeadFacility = f.facilityID OR 
                   dbo.TransmittalSiteLead.TransmittalSiteLeadFacility = @facilitydivisionID))
                    BETWEEN  @startdate AND @enddate)

order by di.DocumentNumber

             END
*/

我將建議分兩個階段解決問題。第一部分是確保返回您需要的所有數據。第二部分是以您想要的方式組合行。

我也無法讓 SqlFiddle 處理表類型，所以我將直接處理查詢。這是上面的基本查詢：

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
)
SELECT
 di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,tsl.TransmittalSiteLeadFacility, 
 ts.TransmittalNumber, ts.TransmittalVersion,
 FacilityImpDate = COALESCE(tsl.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
FROM
 TransmittalSiteLead tsl
 INNER JOIN TransmittalSheet as ts
   ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
 INNER JOIN documentInfo as di 
   ON ts.transmittalID = di.documentTransmittalImp
 INNER JOIN f
   ON tsl.TransmittalSiteLeadFacility = f.FacilityId
WHERE
 transmittalSiteLeadImpDate 
   BETWEEN  @startdate AND @enddate

即使有重複或額外的資訊，您要返回的所有數據也應該在那裡。如果不是，請查看JOINsorWHERE子句以了解如何包含該資訊。

問題的第二部分是壓縮或限制返回的行。有很多方法可以做到這一點，最好的方法取決於很多因素。以下是我確定的部分列表：

• SELECT DISTINCT限制結果
• GROUP BY包含MIN有關相關表的資訊（見下文）
• EXISTS限制行返回
• ROW_NUMBER()根據排序選擇一行（見下文）
• APPLYwithTOP從連接表中獲取一些資訊

所有這一切都是說有很多方法可以做到這一點，所以做對你的情況有意義的事情。我在下面給出了兩種方法，這些方法對於性能關鍵的情況來說並不是最好的，但更容易理解。

此查詢選擇文件資訊和實施它的人員的人員 ID。

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
)
SELECT
 di.DocumentNumber
 ,di.DocumentVersion
 ,di.DocumentTitle
 ,MIN(tsl.TransmittalSiteLeadStaffId) AS TransmittalSiteLeadStaffId
FROM
 TransmittalSiteLead tsl
 INNER JOIN TransmittalSheet as ts
   ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
 INNER JOIN documentInfo as di 
   ON ts.transmittalID = di.documentTransmittalImp
 INNER JOIN f
   ON tsl.TransmittalSiteLeadFacility = f.FacilityId
WHERE
 transmittalSiteLeadImpDate 
   BETWEEN  @startdate AND @enddate
GROUP BY
   di.DocumentNumber
   ,di.DocumentVersion
   ,di.DocumentTitle
ORDER BY
   di.DocumentNumber

另一種方法是使用ROW_NUMBER. 這種方式非常靈活，但可能更難以建構和理解。

此查詢選擇匹配的文件以及第一個實施它的設施和聯繫人。PARTITION設置您希望如何劃分行並ORDER BY設置您希望如何選擇要使用的匹配行。

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
),
FullData AS (
   SELECT
     di.DocumentNumber
     ,di.DocumentVersion
     ,di.DocumentTitle
     ,tsl.TransmittalSiteLeadFacility
     ,tsl.TransmittalSiteLeadStaffId
     ,ROW_NUMBER() OVER (PARTITION BY di.DocumentNumber ORDER BY tsl.TransmittalSiteLeadImpDate) AS RowNum
   FROM
     TransmittalSiteLead tsl
     INNER JOIN TransmittalSheet as ts
       ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
     INNER JOIN documentInfo as di 
       ON ts.transmittalID = di.documentTransmittalImp
     INNER JOIN f
       ON tsl.TransmittalSiteLeadFacility = f.FacilityId
   WHERE
     transmittalSiteLeadImpDate 
       BETWEEN  @startdate AND @enddate
)
SELECT
   DocumentNumber
   ,DocumentVersion
   ,DocumentTitle
   ,TransmittalSiteLeadFacility
   ,TransmittalSiteLeadStaffId
FROM
   FullData
WHERE
   RowNum = 1
ORDER BY
   DocumentNumber

引用自：https://dba.stackexchange.com/questions/64610