使用子查询选择最小值

Question

我需要编写一个查询来获取每个商家的最低价格，并输出价格链接（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 

这给结果的正确数量，他们都非常接近正确的结果集但我仍然没有得到每个商家的最低价格。

Answer 1

您使用子查询作为标准，而不是将其作为附加的记录来源添加。这里有一个好办法：

SELECT p_id, p_m_id, p_prod_label, a_platform 
     , p_link, p_price, m_title, m_link, m_avg_rating, MinPrice 
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 tp.p_m_id as lp_m_id, MIN(tp.p_price) AS MinPrice FROM tgmp_prices tp 
      WHERE tp.p_parent = '25573' 
      GROUP BY tp.p_m_id 
    ) LowestPriceForEachMerchant 
    ON m.m_id = LowestPriceForEachMerchant.lp_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 

Answer 2

可能：

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 
    AND p.p_price = (SELECT MIN(p2.p_price) 
        FROM tgmp_prices p2 
        WHERE p2.p_m_id = m.m_id) 
ORDER BY p.p_price ASC