php - 我们如何使用group by子句选择匹配度最高的单个值

This question already has answers here:

Get records with max value for each group of grouped SQL results
                                
                                    （18个回答）
                                
                        
                                3年前关闭。
            
                    
第一个表：“ wp_pc_products”

feed_product_name   feed_product_image   price   deeplink    id_merchant
sony xperia c3      abc.png              12000   abc.html     fkt
sony xperia         xyz.png              11000   aaa.html     snd
sony xperia M       pqr.png              10000   bbb.html     fkt
sony xperia z       abc.png              12000   abc.html     fkt
sony xperia z3      xyz.png              11000   aaa.html     snd
sony xperia k       pqr.png              10000   bbb.html     fkt

第二个表：“ wp_pc_products_merchants”

slug     image
fkt      logo1.png
snd      logo2.png

我现有的查询

SELECT p.feed_product_name,
       p.feed_product_image,
       p.price,
       p.deeplink,
       p.id_merchant,
       m.image
FROM wp_pc_products p
JOIN wp_pc_products_merchants m ON m.slug=p.id_merchant
AND (feed_product_name LIKE'%sony%'
     OR feed_product_name LIKE'%xperia%'
     OR feed_product_name LIKE'%c3%')
AND price BETWEEN "11829.3" AND "21968.7"
GROUP BY id_merchant

上面的查询不适用于group by子句。
我如何在上述查询中使用分组

最佳答案

将查询更改为

select distinct(m.image),p.feed_product_name,p.feed_product_image,p.price,p.deeplink from wp_pc_products p JOIN wp_pc_products_merchants m ON m.slug=p.id_merchant and (p.feed_product_name like'%sony%' or p.feed_product_name like'%xperia%' or p.feed_product_name like'%c3%' ) and p.price BETWEEN "11829.3" and "21968.7" group by p.id_merchant

要么

select distinct(m.image),p.feed_product_name,p.feed_product_image,p.price,p.deeplink from wp_pc_products p JOIN wp_pc_products_merchants m ON m.slug=p.id_merchant where (p.feed_product_name like'%sony%' or p.feed_product_name like'%xperia%' or p.feed_product_name like'%c3%' ) and p.price BETWEEN "11829.3" and "21968.7" group by p.id_merchant