使用子查詢選擇最小值

我需要編寫一個查詢來從每個商家那裡獲取最低價格並輸出價格連結（p_link）和一些其他資訊，例如商家名稱+評級。

我們有一個價格表 (tgmp_prices)、一個商家表 (tgmp_merchants) 和一個附屬產品 ID 表 (tgmp_affiliates)

我已經得到查詢來獲取我需要的所有價格和數據：

SELECT p_id, p_m_id, p_prod_label, a_platform, p_link, p_price, m_title, m_link, m_avg_rating 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
   ON ga.a_code = p.p_gtin 
       AND ga.a_code != '' 
JOIN tgmp_merchants m 
   ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
   AND p.site_id = '34' 
   AND ga.a_parent = '25573' 
   AND p_type = 'games' 
   AND m.m_hide = 0 
ORDER BY p.p_price ASC

我只需要按商家 ID（p.p_m_id 或 m.m_id）對價格進行分組，然後為每個商家選擇最低價格。

SELECT p_id, p_m_id, p_prod_label, a_platform, p_link, p_price, m_title, m_link, m_avg_rating 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
   ON ga.a_code = p.p_gtin 
       AND ga.a_code != '' 
JOIN tgmp_merchants m 
   ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
   AND p.site_id = '34' 
   AND ga.a_parent = '25573' 
   AND p_type = 'games' 
   AND m.m_hide = 0 
GROUP BY m.m_id
ORDER BY p.p_price ASC

如果我在 ORDER BY 之前添加 GROUP BY m.m_id 我會得到 16 個結果，每個商家 1 個。我只需要使這個價格最低。所以我嘗試添加一個子查詢，其中價格表（tgmp_prices）是這樣連接的：

SELECT p_id, p_m_id, p_prod_label, a_platform, p_link, p_price, m_title, m_link, m_avg_rating 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
   ON ga.a_code = p.p_gtin 
       AND ga.a_code != '' 
       AND p.p_price = ( 
           SELECT MIN(p.p_price) 
           FROM tgmp_prices 
           WHERE p.p_m_id = m.m_id
       ) 
JOIN tgmp_merchants m 
   ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
   AND p.site_id = '34' 
   AND ga.a_parent = '25573' 
   AND p_type = 'games' 
   AND m.m_hide = 0 
GROUP BY m.m_id
ORDER BY p.p_price ASC

這並沒有給我正確的結果 - 我認為問題可能是執行子查詢時 m.m_id 不可用，所以我嘗試在子查詢中加入商家表，但我仍然結束具有相同的結果集。

DDL如下：

CREATE TABLE IF NOT EXISTS `tgmp_affiliates` (
   `a_id` int(8) NOT NULL AUTO_INCREMENT,
   `site_id` int(6) NOT NULL,
   `a_parent` int(8) NOT NULL,
   `a_code` varchar(32) NOT NULL,
   `a_type` varchar(32) NOT NULL,
   `a_platform` varchar(12) NOT NULL,
   PRIMARY KEY (`a_id`),
   UNIQUE KEY `site_id` (`site_id`,`a_parent`,`a_code`,`a_type`,`a_platform`),
   KEY `a_code` (`a_code`),
   KEY `a_parent` (`a_parent`),
   KEY `a_platform` (`a_platform`),
   KEY `a_type` (`a_type`),
   KEY `site_id_2` (`site_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

-- --------------------------------------------------------

--
-- Table structure for table `tgmp_merchants`
--

CREATE TABLE IF NOT EXISTS `tgmp_merchants` (
   `m_id` int(8) NOT NULL AUTO_INCREMENT,
   `site_id` int(6) NOT NULL,
   `m_title` varchar(128) NOT NULL,
   `m_url` text NOT NULL,
   `m_link` varchar(128) NOT NULL,
   `m_favicon` text NOT NULL,
   `m_avg_rating` tinyint(1) NOT NULL,
   `m_hide` tinyint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`m_id`),
   UNIQUE KEY `m_title` (`m_title`,`m_link`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

-- --------------------------------------------------------

--
-- Table structure for table `tgmp_prices`
--

CREATE TABLE IF NOT EXISTS `tgmp_prices` (
   `p_id` int(8) NOT NULL AUTO_INCREMENT,
   `site_id` int(6) NOT NULL,
   `p_m_id` int(8) NOT NULL,
   `p_prod_label` varchar(128) NOT NULL,
   `p_gtin` varchar(64) NOT NULL,
   `p_parent` int(8) NOT NULL,
   `p_link` text NOT NULL,
   `p_price` decimal(8,2) NOT NULL,
   `p_delivery` decimal(8,2) NOT NULL,
   `p_currencey` varchar(10) NOT NULL,
   `p_avail` varchar(32) NOT NULL,
   `p_condition` varchar(32) NOT NULL,
   `p_when` datetime NOT NULL,
   `p_type` varchar(32) NOT NULL,
   `p_hide` tinyint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`p_id`),
   UNIQUE KEY `p_gtin` (`p_gtin`,`p_m_id`,`p_price`,`p_delivery`),
   KEY `p_price` (`p_price`),
   KEY `p_parent` (`p_parent`),
   KEY `site_id` (`site_id`),
   KEY `p_gtin_2` (`p_gtin`),
   KEY `p_type` (`p_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

我現在已將查詢修改為以下內容：

SELECT p_id, p_m_id, p_prod_label, a_platform, p_link, p_price, m_title, m_link, m_avg_rating 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
   ON ga.a_code = p.p_gtin 
       AND ga.a_code != '' 
       AND p.p_price = (
           SELECT MIN(tp.p_price) FROM tgmp_prices tp
               WHERE tp.p_m_id = m.m_id
               AND tp.p_parent = '25573'
   )
JOIN tgmp_merchants m 
   ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
   AND p.site_id = '34' 
   AND ga.a_parent = '25573' 
   AND p_type = 'games' 
   AND m.m_hide = 0 
GROUP BY m.m_id
ORDER BY p.p_price ASC

這給出了正確數量的結果，它們非常接近正確的結果集，但我仍然沒有得到每個商家的最低價格。

稍微修改您的第二個查詢，將為您提供商家 ID 和最低價格（在所有通過條件的產品中 - 我想這就是您想要的）：

SELECT p.p_m_id, MIN(p_price) AS min_p_price 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
   ON ga.a_code = p.p_gtin 
       AND ga.a_code > '' 
JOIN tgmp_merchants m 
   ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
   AND p.site_id = '34' 
   AND ga.a_parent = '25573' 
   AND p.p_type = 'games' 
   AND m.m_hide = 0 
GROUP BY p.p_m_id ;

然後，您可以將其作為派生表加入到您需要從結果中獲取數據的所有表中：

SELECT
   m.*, p.*, ga.*                     -- whatever columns you want  
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
   ON ga.a_code = p.p_gtin 
       AND ga.a_code > '' 
JOIN tgmp_merchants m 
   ON m.m_id = p.p_m_id 
JOIN
     ( SELECT p.p_m_id, MIN(p_price) AS p_price 
       FROM tgmp_affiliates ga 
       JOIN tgmp_prices p 
           ON ga.a_code = p.p_gtin 
               AND ga.a_code > '' 
       JOIN tgmp_merchants m 
           ON m.m_id = p.p_m_id 
       WHERE ga.site_id = '34' 
           AND p.site_id = '34' 
           AND ga.a_parent = '25573' 
           AND p.p_type = 'games' 
           AND m.m_hide = 0 
       GROUP BY p.p_m_id 
     ) AS tmp
   ON  tmp.p_m_id = p.p_m_id 
   AND tmp.p_price = p.p_price
WHERE ga.site_id = '34' 
   AND p.site_id = '34' 
   AND ga.a_parent = '25573' 
   AND p.p_type = 'games' 
ORDER BY p.p_price ;

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