MySQL加入多行

Question

我有包含 2 个表的数据库，其中 1 个用于存储客户 ID 和客户信息。第二个表使用键/值排序，因为我需要存储未定义的值。

表结构

表客户:

=================
    id | status
=================

表 customers_info

=======================================
  id | id_customer | key | value
=======================================

内容示例:

表客户:

=================
    id | status
    1  | 1
    2  | 1
==================

表 customers_info

=======================================
    id | id_customer | key | value
    1  | 1           | name| Doe
    2  | 1           | age | 25
    3  | 1           | city| NY
    4  | 2           | name| Smith
=======================================

如何查询表格以显示所有客户的姓名

示例:

=================
    1 | Doe
    2 | Smith
=================

如果我简单地进行内部连接，我只会得到表的第一个键:

SELECT * FROM customers inner join customers_info on customers.id = customers_info.id_customer

Answer 1

试试这个:

SELECT ci.id_customer AS CustomerId, ci.value AS CustomerName
FROM customers_info ci 
WHERE ci.key = 'name'
ORDER BY ci.id_customer;

检查 SQL FIDDLE DEMO

输出

| CUSTOMERID | CUSTOMERNAME |
|------------|--------------|
|          1 |          Doe |
|          2 |        Smith |

::编辑::

获取客户的所有字段

SELECT ci.id_customer AS CustomerId, 
       MAX(CASE WHEN ci.key = 'name' THEN ci.value ELSE '' END) AS CustomerName, 
       MAX(CASE WHEN ci.key = 'age' THEN ci.value ELSE '' END) AS CustomerAge, 
       MAX(CASE WHEN ci.key = 'city' THEN ci.value ELSE '' END) AS CustomerCity
FROM customers_info ci 
GROUP BY ci.id_customer
ORDER BY ci.id_customer;

检查 SQL FIDDLE DEMO

输出

| CUSTOMERID | CUSTOMERNAME | CUSTOMERAGE | CUSTOMERCITY |
|------------|--------------|-------------|--------------|
|          1 |          Doe |          25 |           NY |
|          2 |        Smith |             |              |

::编辑::

从性别表中获取 GENDER:

SELECT a.CustomerId, a.CustomerName, a.CustomerAge, a.CustomerCity, g.name AS CustomerGender
FROM (SELECT ci.id_customer AS CustomerId, 
             MAX(CASE WHEN ci.key = 'name' THEN ci.value ELSE '' END) AS CustomerName, 
             MAX(CASE WHEN ci.key = 'age' THEN ci.value ELSE '' END) AS CustomerAge, 
             MAX(CASE WHEN ci.key = 'city' THEN ci.value ELSE '' END) AS CustomerCity
      FROM customers_info ci 
      GROUP BY ci.id_customer
     ) AS a
INNER JOIN customers c ON a.CustomerId = c.id 
LEFT OUTER JOIN gender g ON C.genderId = c.id 
ORDER BY a.CustomerId;