Sql-Server-2005

根據偏好將多條記錄限制為一條?

  • April 10, 2015

下面是我用來在幾個分銷商的定價表中提取最佳 Price1 的查詢。

SELECT 
   products.UPC, 
   T2.Price1, 
   vProducts_PriceQty_Union.Price2, 
   vProducts_PriceQty_Union.DBID AS PricingDBID, 
   T2.VendorCount
FROM         
   vProducts_PriceQty_Union 
INNER JOIN
   products ON 
       vProducts_PriceQty_Union.UPC = products.UPC 
INNER JOIN
   (
   SELECT 
       UPC, 
       MIN(Price1) AS Price1, 
       SUM(Quantity) AS Quantity, 
       COUNT(DBID) AS VendorCount
   FROM 
       vProducts_PriceQty_Union
   GROUP BY 
       UPC
   ) AS T2 ON 
       T2.UPC = vProducts_PriceQty_Union.UPC 
       AND 
       vProducts_PriceQty_Union.Price1 = T2.Price1 
WHERE
   vProducts_PriceQty_Union.UPC = '082442818986'
ORDER BY 
   products.UPC

“vProducts_PriceQty_Union”是一個包含三個表的 UNION ALL 的視圖,如下所示:

SELECT     UPC, Price1, Price2, Quantity, DBID FROM Table1
   UNION ALL
SELECT     UPC, Price1, Price2, Quantity, DBID FROM Table2
   UNION ALL
SELECT     UPC, Price1, Price2, Quantity, DBID FROM Table3

筆記:

  1. 我意識到列名“DBID”是一個保留名稱,但是在遊戲中更改為時已晚,並且似乎不會以不利的方式影響任何查詢結果
  2. DBID 只是對包含數據的表的數字引用。
  3. 在聯合查詢中引用的任何表中都沒有重複的 UPC,只有在聯合時重複 UPC
  4. WHERE 子句僅在第一個查詢中顯示以返回範例結果,而不在生產查詢中使用

通常,上面顯示的第一個查詢為每個 UPC 返回一行,顯示三個可用的 Price1 中的最低值。但是,在多個表中 Price1 相同的情況下,查詢會為給定的 UPC 返回多行,如下所示:

UPC             Price1      Price2      Quantity    Pricing DBID    VendorCount
082442818986    597.0000    569.0000    103.00      1               2
082442818986    597.0000    569.0000    103.00      2               2

我的問題是:

當其中一個結果包含 PricingDBID = 1 時,如何僅列出此類實例的一行(返回多個),並優先使用 PricingDBID = 1(主商店,而不是異地倉庫)。如果一個的記錄不包含 PricingDBID = 1,然後僅選擇返回的第一個(或隨機的,如果您願意的話)(所有高於 1 的 PricingDBID 都是異地倉庫,我們不在乎選擇哪個,只需選一個)。

我已經用 CASE 語句嘗試過這個,但我一生都無法弄清楚。我懷疑它與“T2.UPC = Products_PriceQty_Union.UPC AND vProducts_PriceQty_Union.Price1 = T2.Price1”綁定有關,但不完全確定實現這一點的語法可能是什麼。對此進行了 20 多個小時,到目前為止還沒有解決方案,所以我正在與社區聯繫。任何幫助是極大的讚賞!

後續問題…:

不知道這是否是這個補充問題應該去的地方。如果這應該作為新問題發布,請告訴我(就發布而言,我是這個組的新手)。

當我嘗試將工作查詢(請參閱 ypercube 的答案)粘貼到 SSMS 中的視圖設計器並嘗試查看結果(ctrl+r)時,我得到“sqlwb.exe 中發生未處理的 win32 異常

$$ 14240 $$”。然後,當我調試時,我看到對話框“SqlWb.exe 中發生緩衝區溢出,它損壞了程序的內部狀態。按 Break 進行調試”。按“Break”時,違規行是“0x7813BD93 = 2014559635 和 dword ptr ds:$$ 781C4920h $$,0" (如果這對任何人都意味著什麼)。在 Visual Studio 調試器中停止程式碼時,SSMS 將關閉,我必須重新開始。我已經廣泛搜尋了這個問題的解決方案。但是,當我在查詢視窗(不是建構器),查詢執行得很好! 一種解決方法是使用 Create view 編寫視圖創建腳本,這對於查詢非常有效。只要我不想在視圖設計器中編輯查詢,它就可以正常工作。詭異的。

有沒有人對此有任何意見或經驗,粘貼到視圖設計器中的查詢會導致程序崩潰?啊,微軟,它總是和你在一起……

我認為這會得到你想要的。就效率而言,它可能不是最好的(由於工會的觀點):

WITH LowestPrices AS
   ( SELECT 
         UPC, 
         Price1, 
         SUM(Quantity) OVER (PARTITION BY UPC) AS Quantity, 
         DBID AS PricingDBID, 
         COUNT(*) OVER (PARTITION BY UPC) AS VendorCount,
         ROW_NUMBER() OVER (PARTITION BY UPC 
                            ORDER BY Price1,
                                     DBID         -- to resolve ties
                           ) AS Rn
     FROM         
         vProducts_PriceQty_Union  
    -- WHERE
    --     UPC = '082442818986'
   )
SELECT 
   UPC,
   Price1,
   Quantity,
   PricingDBID,
   VendorCount
FROM
   LowestPrices
WHERE
   Rn = 1
ORDER BY 
   UPC ;

如果Products確實需要連接,您可以在 CTE 內部或外部查詢中添加它。

引用自:https://dba.stackexchange.com/questions/97343