SQL从列中获取值并拆分为列。(SQL Take values from columns and split to columns. Thereafter combine with another t

SQL从列中获取值并拆分为列。(SQL Take values from columns and split to columns. Thereafter combine with another table)

我有两张桌子。 第一个表有一个列有很多不同的值，我想在显示结果时分成几列。 然后我想将这些结果与表二结合起来：

请注意，表1具有每小时值，而表2仅具有时间等于00:00:00日期。

表格1

+---------------------+------+----+ | DateAndTime | Area | V1 | +---------------------+------+----+ | 2012-01-01 00:00:00 | A1 | 3 | | 2012-01-01 00:01:00 | A1 | 4 | | 2012-01-01 00:00:00 | A2 | 4 | | 2012-01-01 00:01:00 | A2 | 2 | +---------------------+------+----+

我想将区域列分成几列，然后根据日期与表2结合。

表2

+---------------------+----+ | DateAndTime | V2 | +---------------------+----+ | 2012-01-01 00:00:00 | 3 | | 2012-01-02 00:00:00 | 4 | +---------------------+----+

我希望将最终结果打印成这样：

+---------------------+----+----+----+ | DateAndTime | A1 | A2 | V2 | +---------------------+----+----+----+ | 2012-01-01 00:00:00 | 3 | 4 | 3 | | 2012-01-01 00:01:00 | 4 | 2 | 4 | +---------------------+----+----+----+

可以有几个区域值（ A1 ， A2 ， A3 ，...），上面的数据被简化，以表明我的观点:)

我希望我不是双重发帖，有人可以帮助我。

I have two tables. The first table has a column with a lot of different values that I want to break up into several columns when showing the result. Then I would like to combine these results with table two:

Notice that table one has hourly values while table two only has dates whith time equal to 00:00:00.

Table 1

+---------------------+------+----+ | DateAndTime | Area | V1 | +---------------------+------+----+ | 2012-01-01 00:00:00 | A1 | 3 | | 2012-01-01 00:01:00 | A1 | 4 | | 2012-01-01 00:00:00 | A2 | 4 | | 2012-01-01 00:01:00 | A2 | 2 | +---------------------+------+----+

I would like to split the area columns into several columns, and thereafter combine with table 2 based on date.

Table 2

+---------------------+----+ | DateAndTime | V2 | +---------------------+----+ | 2012-01-01 00:00:00 | 3 | | 2012-01-02 00:00:00 | 4 | +---------------------+----+

I would like to have the end result to be printed like this:

+---------------------+----+----+----+ | DateAndTime | A1 | A2 | V2 | +---------------------+----+----+----+ | 2012-01-01 00:00:00 | 3 | 4 | 3 | | 2012-01-01 00:01:00 | 4 | 2 | 4 | +---------------------+----+----+----+

There can be several area values (A1, A2, A3, ...) and the data above is simplified to make my point :)

I hope I am not double posting and that someone can help me.

最满意答案

你在这里想要实现的是区域的不同值的转换。

它是可能的，固定和有限的数量，但如果这个数字没有限制，那么你根本无法在MySQL中做到这一点，将不得不依靠你的应用程序层来做到这一点:(

如果你不是一个关于如何可能的理论示例，请发表评论:)

What you are trying to achieve here is a transposition of the different values of Area.

It's decently possible with a fixed and limited number of them but if this number is not bounded then you simply can't do it in MySQL and will have to rely on your application layer to do it :(

Drop a comment if you wan't a theorical example on how it is possible :)