MySQL显示整个组，在组内按字母顺序排序

I have the following query:

我有以下查询:

SELECT * 

FROM vendors_parts INNER JOIN 

vendors ON vendors_parts.vendor = vendors.vendor_id

WHERE (vendors_parts.vendor = @vendor_id) AND (vendors_parts.active = @active) OR

(vendors_parts.vendor = @vendor_id) AND (vendors_parts.active = @inactive)

ORDER BY vendors_parts.old_id, vendors_parts.vendor_part, date_start DESC, vendors_parts.active DESC

Which returns data in the following format:

以下列格式返回数据:

vendor_part      active       old_id
b                1            1
a                0            1
a                0            1
b                0            1
x                1            3
z                1            5
c                1            20
c                0            20

My question is how do I keep the old_id together while sorting the vendor_part ascending and active descending? The output I would like is:

我的问题是如何在排序vendor_part升序和活动降序时将old_id保持在一起?我想要的输出是:

vendor_part      active       old_id
b                1            1
a                0            1
a                0            1
b                0            1
c                1            20
c                0            20
x                1            3
z                1            5

I've also tried a sub query that groups on old_id, but this limits me to only one unique old_id:

我还尝试了一个在old_id上分组的子查询,但这限制了我只有一个唯一的old_id:

SELECT *

FROM 
     (
     SELECT * FROM vendors_parts INNER JOIN vendors ON vendors_parts.vendor = vendors.vendor_id
     WHERE (vendors_parts.vendor = @vendor_id) AND (vendors_parts.active = @active) OR (vendors_parts.vendor = @vendor_id) AND (vendors_parts.active = @inactive)
     GROUP BY vendors_parts.old_id
     ) temp_table
ORDER BY vendor_part

How do I show all of the old_ids grouped together, and sorted alphabetically by vendor_part within their respective groups?

如何将所有old_ids组合在一起,并按其各自组中的vendor_part按字母顺序排序?

thank you

1 个解决方案

#1

Here is a possible answer. Please note in this answer the table tbl is a placeholder for your actual (rather complex) query (I dislike resorting on that but you might have to use a temporary table to keep the query syntax manageable):

这是一个可能的答案。请注意,在这个答案中,表tbl是一个占位符,用于实际(相当复杂)的查询(我不喜欢使用它,但您可能必须使用临时表来保持查询语法的可管理性):

SELECT tbl.* FROM tbl 
  JOIN (SELECT MIN(vendor_part) as mvp, old_id FROM tbl GROUP BY old_id) as driver
  USING (old_id)
  ORDER BY driver.mvp, tbl.old_id, active DESC, tbl.vendor_part

See http://sqlfiddle.com/#!2/c53a9/4 for the live demo. As you will see, using the example you gave in your question, it produces the expected result.

有关现场演示,请参见http://sqlfiddle.com/#!2/c53a9/4。正如您将看到的,使用您在问题中提供的示例,它会产生预期结果。