mysql - 表连接列名到列值

Question

我看到了很多类似的问题，但我似乎找不到符合我需要做的问题......

我有以下 2 个表:

表 1(ambro_awards_categories):

id  cat_name    category
1   cat1        Would most likely know how to fondue an ant
2   cat2        Most Likely to survive a nuclear winter
3   cat3        Most Likely to survive a nuclear winter
4   cat4        Category 4
5   cat5        Category 5
6   cat6        Category 6
7   cat7        Category 7
8   cat8        Category 8
9   cat9        Category 9

表 2(ambro_awards):

id  voter_name          cat1                cat2                cat>>>      date
1   Cavey Charlesworth  Cavey Charlesworth  Cavey Charlesworth  ...         2015-01-18 00:00:00
2   Lynn Wylder         Lynn Wylder         Lynn Wylder         ...         2015-01-18 00:00:00
3   Lynn Wylder         Peter Parker        Batman              ...         2015-01-18 00:00:00
etc

我想做的(并且我完全接受可能有更好的方法)是将所有内容连接到一个大表中，连接到 cat1、cat2 等。这样我就可以修改将类别名称改为更长、更具描述性的名称，而不延长第二个表。

这意味着对表 1 的 cat_name 值到表 2 中的 cat1、cat2 列名称执行此操作。

所需的结果类似于:

Would most likely know how to fondue an ant (ie "Category 1")
     name 1
     name 2
     name 3
     name 4... etc

Most Likely to survive a nuclear winter (ie "Category 2")
     name 1
     name 2
     name 3
     name 4... etc

Most likeley to succeed (ie "Category 3")
     name 1
     name 2
     name 3
     name 4... etc

诸如此类的事情...

如果将多个名称添加到 1 个表中很简单，我会这样做，但认为这可能有点错误...希望执行上述操作，但后来遇到了问题...

Pj。

Answer 1

至少可以说，这种设计远非最佳，但为了回答你的问题，据我所知，你无法连接第二个表的列名是列值的表(并且你当然不能便携)。但是，我认为您所追求的内容可以用当前的架构这样编写:

CREATE VIEW ambro_full_data AS
SELECT a.id as aid, c.id as cid, c.cat_name,
    CASE c.cat_name
        WHEN 'cat1' THEN a.cat1
        WHEN 'cat2' THEN a.cat2
        WHEN 'cat3' THEN a.cat3
        WHEN 'cat4' THEN a.cat4
        WHEN 'cat5' THEN a.cat5
        WHEN 'cat6' THEN a.cat6
        WHEN 'cat7' THEN a.cat7
        WHEN 'cat8' THEN a.cat8
        WHEN 'cat9' THEN a.cat9
    END as name,
    c.category,
    a.date
FROM ambro_awards_categories c
  CROSS JOIN ambro_awards a;

例如:

mysql> SELECT name FROM ambro_full_data WHERE cid = 1;
+--------------------+
| name               |
+--------------------+
| Cavey Charlesworth |
| Lynn Wylder        |
| Peter Parker       |
+--------------------+
3 rows in set (0.00 sec)

或

mysql> SELECT category,name FROM ambro_full_data ORDER BY cid;
+---------------------------------------------+--------------------+
| category                                    | name               |
+---------------------------------------------+--------------------+
| Would most likely know how to fondue an ant | Cavey Charlesworth |
| Would most likely know how to fondue an ant | Lynn Wylder        |
| Would most likely know how to fondue an ant | Peter Parker       |
| Most Likely to survive a nuclear winter     | Lynn Wylder        |
| Most Likely to survive a nuclear winter     | Batman             |
| Most Likely to survive a nuclear winter     | Cavey Charlesworth |
| Most Likely to survive a nuclear winter     | Lynn Wylder        |
| Most Likely to survive a nuclear winter     | Cavey Charlesworth |
| Most Likely to survive a nuclear winter     | Lynn Wylder        |
| Category 4                                  | Cavey Charlesworth |
| Category 4                                  | Lynn Wylder        |
| Category 4                                  | Lynn Wylder        |
| Category 5                                  | Lynn Wylder        |
| Category 5                                  | Lynn Wylder        |
| Category 5                                  | Cavey Charlesworth |
| Category 6                                  | Lynn Wylder        |
| Category 6                                  | Cavey Charlesworth |
| Category 6                                  | Lynn Wylder        |
| Category 7                                  | Cavey Charlesworth |
| Category 7                                  | Lynn Wylder        |
| Category 7                                  | Lynn Wylder        |
| Category 8                                  | Lynn Wylder        |
| Category 8                                  | Lynn Wylder        |
| Category 8                                  | Cavey Charlesworth |
| Category 9                                  | Cavey Charlesworth |
| Category 9                                  | Lynn Wylder        |
| Category 9                                  | Lynn Wylder        |
+---------------------------------------------+--------------------+
27 rows in set (0.00 sec)

但是当你添加列时，你需要相应地更新 View 。

但是，更好的设计是使用这样的表格:

CREATE TABLE ambro_awards (id ..., voter_name ..., date ...); (注意没有 catN 列)

CREATE TABLE ambro_awards_categories (id ..., category ...); (注意不需要 cat_name 列)

CREATE TABLE ambro_awards_and_categories (a_id INTEGER NOT NULL, c_id INTEGER NOT NULL, name VARCHAR(<suitable length here>) NOT NULL,
PRIMARY KEY (a_id, c_id),
FOREIGN KEY (a_id) REFERENCES ambro_awards (id), FOREIGN_KEY (c_id) REFERENCES ambro_awards_categories (id));

根据您的示例数据，后一个表格将如下所示:

a_id    c_id    name
1       1       Cavey Charlesworth
1       2       Cavey Charlesworth
1       3       Cavey Charlesworth
2       1       Lynn Wylder
2       2       Lynn Wylder
2       3       Lynn Wylder
3       1       Lynn Wylder
3       2       Peter Parker
3       3       Batman

这样您就可以拥有任意数量的类别，而无需为每个类别创建一列。然后您可以执行适当的 JOIN 来提取与上面相同的数据，例如

SELECT c.category, ac.name FROM ambro_awards_and_categories ac JOIN ambro_awards_categories c ON c.id = ac.c_id;

类似的连接可用于从其他表中提取其他数据。例如，您的原始 ambro_awards 第 1 行可以使用类似的内容提取到多行

SELECT a.id, a.voter_name, a.date, ac.name FROM ambro_awards a JOIN ambro_awards_and_categories ac ON ac.a_id = a.id WHERE a.id = 1;

这是 SQL 中相当标准的做法，如果使用得当，也应该可以解决您的问题。