优雅的mysql选择、分组、合并一个表中的多个行

Here is a simplified version of my table:

这是我的表格的简化版本:

group price spec
a     1     .
a     2     ..
b     1     ...
b     2
c     .
.     .
.     .

I'd like to produce a result like this: (I'll refer to this as result_table)

我想生成这样的结果:(我将其称为result_table)

price_a |spec_a |price_b |spec_b |price_c ...|total_cost
1       |.      |1       |..     |...        |
(min)            (min)                        =1+1+...

Basically I want to:

主要我想:

select the rows containing the min price within each group
选择每个组中包含最小价格的行
combine columns into a single row
将列合并为一行

I know this can be done using several queries and/or combined with some non-sql processing on the results, but I suspect that there maybe better solutions.

我知道可以使用几个查询和/或结合一些非sql处理的结果来实现这一点，但是我怀疑可能有更好的解决方案。

The reason that I want to do task 2 (combine columns into a single row) is because I want to do something like the following with the result_table:

我想要执行task 2(将列合并成单个行)的原因是，我希望使用result_table完成如下操作:

select *,
(result_table.total_cost + table1.price + table.2.price) as total_combined_cost
from result_table 
right join table1
right join table2

This may be too much to ask for, so here is some other thoughts on the problem:

这可能要求太多了，所以这里有一些关于这个问题的其他想法:

Instead of trying to combine multiple rows(task 2), store them in a temporary table (which would be easier to calculate the total_cost using sum)

与其尝试合并多个行(task 2)，不如将它们存储在一个临时表中(使用sum计算total_cost会更容易)

Feel free to drop any thoughts, don't have to be complete answer, I feel it's brilliant enough if you have an elegant way to do task 1 !

不要有任何想法，不要有完整的答案，我觉得如果你有一个优雅的方式来完成任务1就足够了!

==Edited/Added 6 Feb 2012==

2012年2月= =编辑/添加6 = =

The goal of my program is to identify best combinations of items with minimal cost (and preferably possess higher utilitarian value at the same time).

我的项目的目标是找出成本最低的项目的最佳组合(最好同时具有更高的实用价值)。

Consider @ypercube's comment about large number of groups, temporary table seems to be the only feasible solution. And it is also pointed out there is no pivoting function in MySQL (although it can be implemented, it's not necessary to perform such operation).

考虑到@ypercube对大量组的评论，临时表似乎是唯一可行的解决方案。并指出在MySQL中没有旋转功能(虽然可以实现，但不需要执行这种操作)。

Okay, after study @Johan's answer, I'm thinking about something like this for task 1:

好了，在研究了@Johan的答案后，我想到了一个类似于任务1的东西:

select * from
(
    select * from
    result_table
    order by price asc
) as ordered_table
group by group
;

Although looks dodgy, it seems to work.

虽然看起来不太可靠，但似乎很有效。

==Edited/Added 7 Feb 2012==

2012年2月= =编辑/添加7 = =

Since there could be more than one combination may produce the same min value, I have modified my answer :

由于可能有多个组合可能产生相同的最小值，所以我修改了我的答案:

select result_table.* from  
(
    select * from
    (
        select * from
        result_table
        order by price asc
    ) as ordered_table
    group by group
) as single_min_table
inner join result_table
on result_table.group = single_min_table.group
and result_table.price = single_min_table.price 
;

However, I have just realised that there is another problem I need to deal with: I can not ignore all the spec, since there is a provider property, items from different providers may or may not be able to be assembled together, so to be safe (and to simplify my problem) I decide to combine items from the same provider only, so the problem becomes:

然而,我刚刚意识到,我还有另一个问题需要处理:我不能忽视所有的规范,因为有一个提供者属性,物品来自不同提供者可能是也可能不是能够聚集在一起,所以是安全的(为了简化我的问题),我决定将物品从相同的供应商,那么问题就来了:

For example if I have an initial table like this(with only 2 groups and 2 providers):

例如，如果我有一个像这样的初始表(只有两个组和两个提供者):

id group price spec provider
1  a     1     .    x
2  a     2     ..   y
3  a     3     ...  y
4  b     1     ...  y
5  b     2          x
6  b     3          z

I need to combine

我需要把

id group price spec provider
1  a     1     .    x
5  b     2          x

and

和

2  a     2     ..   y
4  b     1     ...  y

record (id 6) can be eliminated from the choices since it dose not have all the groups available.

记录(id 6)可以从选项中删除，因为它没有所有可用的组。

So it's not necessarily to select only the min of each group, rather it's to select one from each group so that for each provider I have a minimal combined cost.

所以不一定只选择每个组的最小值，而是从每个组中选择一个这样对于每个提供者我有最小的组合成本。

2 个解决方案

#1

Producing the total_cost only:

生产total_cost只有:

SELECT SUM(min_price) AS total_cost
FROM 
    ( SELECT MIN(price) AS min_price
      FROM TableX
      GROUP BY `group`
    ) AS grp

If a result set with the minimum prices returned in row (not in column) per group is fine, then your problem is of the gretaest-n-per-group type. There are various methods to solve it. Here's one:

如果每个组返回行(而不是列)的最小价格的结果集是可以的，那么您的问题是每个组返回的最小值类型。有很多方法可以解决这个问题。这里有一个:

SELECT tg.grp 
       tm.price AS min_price
       tm.spec 
FROM
      ( SELECT DISTINCT `group` AS grp
        FROM TableX
      ) AS tg 
  JOIN
      TableX AS tm
    ON 
      tm.PK =                             --- the Primary Key of the table
      ( SELECT tmin.PK 
        FROM TableX AS tmin
        WHERE tmin.`group` = tg.grp
        ORDER BY tmin.price ASC
        LIMIT 1
      )

#2

You cannot pivot in MySQL, but you can group results together.
The GROUP_CONCAT function will give you a result like this:

不能在MySQL中使用pivot，但是可以将结果分组在一起。GROUP_CONCAT函数将给出如下结果:

column A        column B        column c      column d
groups          specs           prices        sum(price)
a,b,c           some,list,xyz   1,5,7         13

Here's a sample query:
(The query assumes you have a primary (or unique) key called id defined on the target table).

下面是一个示例查询(查询假定您在目标表上定义了一个名为id的主(或惟一)键)。

SELECT
  GROUP_CONCAT(a.`group`) as groups
  ,GROUP_CONCAT(a.spec) as specs 
  ,GROUP_CONCAT(a.min_price) as prices
  ,SUM(a.min_prices) as total_of_min_prices
FROM
  ( SELECT price, spec, `group` FROM table1
    WHERE id IN 
      (SELECT MIN(id) as id FROM table1 GROUP BY `group` HAVING price = MIN(price))
  ) AS a

See: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

参见:http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

#1