mysql - 如何在mysql中只选择2行相同的水果类型？

Question

从下表中如何仅选择 2 行相同的水果类型？

id     fruitname      fruittype              1      mango          organic                                         2      apple          nonorgonic                 3      mango          organic              4      banana         organic           5      mango          organic               6      apple          nonorganic       the outputid     fruitname      fruittype1      mango          organic2      apple          nonorganic3      mango          organic4      apple          nonorganic

这里香蕉只出现过一次所以不想芒果的有机出现了3次所以只要2次

Answer 1

嗯..试试这个

 SELECT @a := @a + 1 as id, fruitname, fruittype
FROM  (SELECT @a := 0)  r JOIN (
SELECT fb1.fruitname,fb1.fruittype
    FROM testdb.fruit fb1
     GROUP by fb1.fruitname,fb1.fruittype
   HAVING count(fb1.fruitname) >=2
  UNION ALL
    SELECT fb1.fruitname,fb1.fruittype
      FROM testdb.fruit fb1
     GROUP by fb1.fruitname,fb1.fruittype
    HAVING count(fb1.fruitname) >=2) b
     ORDER BY fruitname,fruittype;

看看这是否是您所需要的。

谢谢