使用不同的值作为列创建MySQL视图 [英] Create MySQL view using distinct values as columns

问题描述

我搜索了一段时间，但找不到我的问题的答案.我有一个看起来像这样的表:

I have searched a while and can't find an answer to my problem. I have a table that looks like this:

date      name   status  
2011-01-01  m1   online  
2011-01-01  m2   offline  
2011-01-01  m3   online  
2011-01-02  m1   offline  
2011-01-02  m2   offline  
2011-01-02  m3   online  

我想创建一个如下所示的视图:

I want to create a view that will looks like the following:

date         m1     m2       m3  
2011-01-01  online  offline  online  
2011-01-02  offline offline  online  

名称"列下的值是不同的且具有固定数量的值，例如20个不同的值(m1 ... m20)，但可以随时间增加.是否可以创建这样的视图?如果是，怎么办?

Where values under the the 'name' column are distinct and has a fixed number of values, say 20 different values (m1 ... m20), but can be increased as times goes. Is it possible to create a view like this? if yes, how?

非常感谢.

推荐答案

列数可变的结果集通常是个坏主意，但是如果您真的想要这样做，则可以使用GROUP BY， MAX 和 IF :

Result sets with a variable number of columns are usually a bad idea, however if you really want to do it then you can use a combination of GROUP BY, MAX and IF:

CREATE VIEW yourview AS
SELECT
    date,
    MAX(IF(name = 'm1', status, NULL)) m1,
    MAX(IF(name = 'm2', status, NULL)) m2,
    MAX(IF(name = 'm3', status, NULL)) m3
FROM yourtable
GROUP BY date;

该视图将根据您的需要为您提供此数据:

The view will give you this data, as you wanted:

date        m1       m2       m3    
2011-01-01  online   offline  online
2011-01-02  offline  offline  online

请注意，无法创建具有可变列数的视图，因此每次列数更改时都必须重新创建视图.

Note that it is not possible to create a view with a variable number of columns so you will have to recreate the view every time the number of columns changes.

这篇关于使用不同的值作为列创建MySQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！