Mysql排序连接值

Question

我有3个mysql表(动物、类别和animal_2_category)。每种动物都可以有0-n类(例如鱼、宠物、昆虫、食肉动物、“会飞”等)。

表“动物”(id，name)

1, dog
2, cat
3, bee
...

表“类别”(id，name)

1, pet
2, insect
3, mammal
4, fish
5, can fly
...

表"animal_2_category“(animal_id，category_id)

1, 1
1, 3
2, 1
2, 3
3, 2
3, 5
...

我现在需要的是一个所有类别组合的列表。以下查询起作用：

SELECT CONCAT_WS("-", x.name, c.name)
FROM animal_2_category a2c1 
    JOIN animal_2_category a2c2 ON a2c1.animal_id = a2c2.animal_id
    JOIN category c ON c.id = a2c2.category_id´
    JOIN categories x
GROUP BY a2c2.category_id

此查询将返回以下内容：

• 宠物哺乳动物
• 哺乳动物宠物
• 昆虫能飞

问题是，我得到双份条目“宠物-哺乳动物”和“哺乳动物-宠物”。如何修改查询，以便只获得其中一个查询，例如：

• 宠物哺乳动物
• 昆虫能飞

Answer 1

您还可以将查询重写为

SELECT  DISTINCT GREATEST(CONCAT_WS("-", x.name, c.name),CONCAT_WS("-", c.name, x.name)) col
FROM animal_2_category a2c1 
    JOIN animal_2_category a2c2 ON a2c1.animal_id = a2c2.animal_id
    JOIN categories c ON c.id = a2c2.`category_id`
    JOIN categories `x` ON a2c1.category_id = x.id
    WHERE x.name <> c.name

DEMO

Answer 2

避免重复对的一种方法是确保您的对总是按照相同的顺序创建。我们可以通过在if语句中组装您的对子来做到这一点，这将有效地为我们排序这两个值。然后我们就抓住不同的行。

select distinct if(c1.name > c2.name, concat_ws('-', c1.name, c2.name), concat_ws('-', c2.name, c1.name)) pairing
  from animal_2_category a1
    inner join animal_2_category a2
      on a1.animal_id = a2.animal_id
        and a1.category_id <> a2.category_id
    inner join categories c1
      on a1.category_id = c1.id
    inner join categories c2
      on a2.category_id = c2.id;

或者，在联接条件中要有更多的选择性，这样他们就只能按照特定的顺序加入。这避免了if，但我们仍然需要获取不同的值。

 select distinct concat_ws('-', c1.name, c2.name) pairing
  from animal_2_category a1
    inner join animal_2_category a2
      on a1.animal_id = a2.animal_id
        and a1.category_id < a2.category_id
    inner join categories c1
      on a1.category_id = c1.id
    inner join categories c2
      on a2.category_id = c2.id;

这里的演示

不管怎样，结果都是一样的。

Answer 3

SELECT DISTINCT combos
FROM (
  SELECT animal_id
  , GROUP_CONCAT(c.name ORDER BY c.name SERPERATOR '-') AS combos  
  FROM animal_2_category a2c
  JOIN category c ON c.id = a2c.category_id 
  GROUP BY animal_id
) AS ilv