從聯合表中僅選擇具有最小值的行

SQL 伺服器 2005

以下是來自兩個表（名為“vProducts_All”）的並集的數據樣本：

DBID UPC 價格 1 價格 2
1 023614025559 689.0000 649.0000
2 023614025559 632.0000 602.0000
1 011457891333 252.0000 232.0000
2 011457891333 284.0000 254.0000
1 123451234512 199.0000 159.0000
2 123451234512 195.0000 162.0000

除了所有其他沒有重複的記錄之外，我只想返回顯示 Price1 欄位最小值的行，例如：

DBID UPC 價格 1 價格 2
2 023614025559 632.0000 602.0000
1 011457891333 252.0000 232.0000
2 123451234512 195.0000 162.0000
+ UPC 欄位中沒有重複的所有其他記錄

嘗試使用連接到派生表是徒勞的，但沒有任何效果。任何幫助表示讚賞

授予的 DBID 和 UPC 不能保證對於 Price1 和 Price2 相同，您的程式碼應該與此類似：

SELECT
(SELECT TOP 1 DBID FROM sub WHERE Price1=(SELECT MIN(Price1) FROM sub)) AS DBID1
,(SELECT TOP 1  UPC FROM sub WHERE Price1=(SELECT MIN(Price1) FROM sub)) AS UPC1
,(SELECT MIN(Price1) FROM sub) as Price1
,(SELECT TOP 1 DBID FROM sub WHERE Price2=(SELECT MIN(Price2) FROM sub)) AS DBID2
,(SELECT TOP 1 UPC FROM sub WHERE Price2=(SELECT MIN(Price2) FROM sub)) AS UPC2
,(SELECT MIN(Price2) FROM sub) AS Price2

其中sub是兩個表並集的結果。

更新：您可以通過減少聚合的數量來加快查詢速度：

SELECT
   (SELECT TOP 1 DBID FROM sub WHERE Price1=a1.Price1) AS DBID1
,   (SELECT TOP 1 UPC FROM sub WHERE Price1=a1.Price1) AS UPC1
,   Price1
,   (SELECT TOP 1 DBID FROM sub WHERE Price2=a1.Price2) AS DBID2
,   (SELECT TOP 1 UPC FROM sub WHERE Price2=a1.Price2) AS UPC2
,   Price2 
FROM 
(
SELECT (SELECT MIN(Price1) FROM sub) as Price1
,(SELECT MIN(Price2) FROM sub) AS Price2
) a1

Update2：我只能假設-您希望在同一 UPC 中使用最低限度。對於不同的情況，您可以有相同的最小值

$$ DBID $$s。考慮到這一點，生成了以下程式碼。請注意$$ DBID $$s 隨機產生，以防有關係。

SELECT 
a1.Price1
, (SELECT TOP 1 [DBID] FROM sub WHERE Price1=a1.Price1 and UPC=a1.UPC) AS [DBID]
, a1.Price2
, (SELECT TOP 1 [DBID] FROM sub WHERE Price2=a1.Price2 and UPC=a1.UPC) AS [DBID]
, a1.UPC
FROM (SELECT MIN(Price1) AS Price1, MIN(Price2) AS Price2, UPC FROM sub group by UPC) a1

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