MySQL将行值合并为单行 [英] MySQL Merge Row Values into single Row

问题描述

我需要为一个表创建一个选择查询，该表将'Number'列上每组的行合并，以获取包含所有可用的最新(By ID)列的单行.

I need to create a select query for a table which merges the rows per group on the 'Number' column to get a single row with all available yet latest (By ID) columns.

这就是我的意思，我需要像这样摆一张桌子:

Here's what I mean, I need to take a table like this:

ID | Number |   Date 1   |   Date 2   |   Date 3   |
----------------------------------------------------
1  |   1    | 2011-10-01 |    NULL    |    NULL    |
2  |   1    |    NULL    | 2011-10-25 |    NULL    |
3  |   1    |    NULL    |    NULL    | 2011-11-13 |
4  |   1    | 2011-10-03 |    NULL    | 2011-11-10 |
5  |   2    |    NULL    |    NULL    | 2012-01-01 |
6  |   2    | 2012-03-11 |    NULL    |    NULL    |

并返回此值(与上面的ID列无关的ID列):

And return this (The ID column being unrelated to the above ID column):

ID | Number |   Date 1   |   Date 2   |   Date 3   |
----------------------------------------------------
1  |    1   | 2011-10-03 | 2011-10-25 | 2011-11-10 |
2  |    2   | 2012-03-11 |    NULL    | 2012-01-01 |

因此，对于数字"列中每个组中的所有行，我需要该组中所有行中的所有可用列值，但仅需要每列的最新值.最新值由列中存在值的"ID"的最大值确定. (如果该组的任何行上的列中没有值，那么将使用Null值.)

So for all rows in each Group By the 'Number' column, I need all available column values from all rows in the group, but only the latest value for each column. The latest being determined by the highest value of 'ID' where a value in the column exists. (If no value in the column exists on any row in the group, then a value of Null is used).

谢谢

推荐答案

感谢大家的建议.不幸的是，我无法使任何当前答案对我有用. 不幸的是，我不能依赖于日期的聚合函数，因为它们可能不是最大值或最小值. 每个日期列使用一个子查询非常聪明，但是我无法使查询运行得足够快以适合我的使用. (它需要几乎立即运行，但是尽管索引配置正确，但是却花费了过多的时间.)

Thanks for everyone's suggestions. Unfortunately, I have been unable to make any of the current answers work for me. Unfortunately I cannot rely on the use of aggregate functions for the date as they may not be the max or min values. The use of a subquery per date column was very clever, but I was not been able to make the query run fast enough for my uses. (It needs to run almost instantly, but despite configuring indexes properly, was talking an in-ordinate amount of time.)

我想出的解决方案是用结构保存结果创建一个新表，然后使用许多触发器使该表保持最新状态.有问题的实际表会定期更新，并且表中的插入内容应使我可以使用触发器将单独的表保持最新，从而满足我的需要.

The solution I have come up with, is to create a new table with a structure to hold the results, then keep this table up to date with a number of triggers. The actual table in question is updated periodically, and the inserts into the table should allow me to keep a separate table up to date with triggers, thus giving me what I need.

这篇关于MySQL将行值合并为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！