代码之家  ›  专栏  ›  技术社区  ›  Pritamkumar Ras C-kay sKatle

如何在mysql中删除内部连接中的重复行

  •  -1
  • Pritamkumar Ras C-kay sKatle  · 技术社区  · 6 年前

    我想展示不同的卖家产品。我的SQL如下:

    SELECT `tblproducts`.`product_id`   ,
    `tbluserproducts`.`userproduct_id`, 
     `tblproducts`.`product_name`, 
     `tblproducts`.`category_id`,
     `tbluserproducts`.`sku`, 
     `tbluserproducts`.`min_qty`,  
     `tbluserproducts`.`offer_price`, 
     MIN(tbluserproducts.offer_price) AS minimum, 
     `tbluserproducts`.`from_date`, 
     `tbluserproducts`.`to_date`, 
     `tbluserproducts`.`stock`
        FROM `tblproducts` 
         JOIN `tbluserproducts` ON tblproducts.product_id = tbluserproducts.product_id 
         WHERE (`tbluserproducts`.`status`=1) AND (`tblproducts`.`status`=1) 
         AND (`tblproducts`.`category_id`='132') 
         GROUP BY `tbluserproducts`.`userproduct_id` 
        ORDER BY product_id DESC LIMIT 5
    

    我得到这样的输出是因为 offer_price 在里面 tbluserproducts 表在中相同 product_id '9281'

    enter image description here

    但我想得到这样的输出

    enter image description here

    我的tblproducts表结构如下

      Column        Type             
      product_id    int(10) unsigned  Auto Increment 
      category_id   int(11) unsigned 
      product_name  varchar(255) 
    

    我的tbluserproducts表结构如下

      Column    Type  Comment
      userproduct_id  int(11) unsigned Auto Increment 
      sku varchar(255)  
      product_id  int(11) unsigned  
      min_qty int(11) unsigned  
      max_qty int(11) unsigned 
      offer_price double unsigned 
      from_date date  
      to_date date  
      stock int(11) 
      insert_date datetime   
      status  tinyint(3) NULL      0-inactive,1-active,2-disabled
    
    1 回复  |  直到 6 年前
        1
  •  0
  •   EZZINE Amal    6 年前

    由于您所发布的问题不够清楚,我将回答这个问题,假设您希望始终显示第一个不同的列。

        SELECT DISTINCT ON (`tblproducts`.`product_id`) product_id,
        `tbluserproducts`.`userproduct_id`, 
        `tblproducts`.`product_name`, 
        `tblproducts`.`category_id`,
        `tbluserproducts`.`sku`, 
        `tbluserproducts`.`min_qty`,  
        `tbluserproducts`.`offer_price`, 
        MIN(tbluserproducts.offer_price) AS minimum, 
        `tbluserproducts`.`from_date`, 
        `tbluserproducts`.`to_date`, 
        `tbluserproducts`.`stock`
        FROM `tblproducts` 
        JOIN `tbluserproducts` ON tblproducts.product_id = tbluserproducts.product_id 
        WHERE (`tbluserproducts`.`status`=1) AND (`tblproducts`.`status`=1) 
        AND (`tblproducts`.`category_id`='132') 
        GROUP BY `tbluserproducts`.`userproduct_id` 
        ORDER BY product_id DESC LIMIT 5