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

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

join 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'

但我想得到这样的输出

我的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 年前

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