mysql - 根据 ID 按字母顺序对所有行进行分组

Question

卡在这个...
我需要类似 GROUP BY 的东西，但也会根据 id(例如 prod_id)影响 cat_name 列中所有其他行的分组结果。示例表:

 +------------------+--------+---------+-------------+--------------+
 | cat_name         | cat_id | prod_id | prod_name   | prod_img     |
 +------------------+--------+---------+-------------+--------------
 | Foo Category     |     54 |     755 | product a   | prod_a_img_1 |
 | A Category       |     22 |     755 | product a   | prod_a_img_3 |
 | Bar Category     |     59 |     755 | product a   | prod_a_img_2 |
 | Category three   |     85 |     767 | product b   | prod_b_img_1 |
 +------------------+--------+---------+-------------+--------------+

我想要的:

+------------------+--------+---------+-------------+--------------+
| cat_name         | cat_id | prod_id | prod_name   | prod_img     |
+------------------+--------+---------+-------------+--------------
| A Category       |     54 |     755 | product a   | prod_a_img_1 |
| A Category       |     22 |     755 | product a   | prod_a_img_3 |
| A Category       |     59 |     755 | product a   | prod_a_img_2 |
| Category three   |     85 |     767 | product b   | prod_b_img_1 |
+------------------+--------+---------+-------------+--------------+

类别具有唯一 ID，但我只需要选择每行中的第一个类别(按字母顺序)。

我的完整查询:

select categories.name as cat_name,
   categories.slug as cat_uri,
   categories.term_id as cat_id,
   object_id,
   term_taxonomy_id,
   ID as prod_id,
   post_title as prod_name,
   img as prod_img
   from
   wp_posts,
   wp_term_relationships,
   (select term_id,
   name,
   slug
   from
   wp_terms group by name) as categories
   where post_type = "product" and
   object_id = ID and
   term_taxonomy_id = term_id

Answer 1

我觉得你看起来像这样:

SELECT (
        SELECT ti.cat_name 
        FROM yourTable ti 
        WHERE ti.prod_id = t.prod_id
        ORDER BY ti.cat_name
        LIMIT 1) As cat_name, 
    t.cat_id, 
    t.prod_id, 
    t.prod_name, 
    t.prod_img
FROM 
    yourTable t