php - 查看数据透视表中的数据(垂直mysql表)

我真的对透视表很困惑，请看我有什么和我需要实现什么：
这是我的mysql表结构

id  meta_value            field_id   item_id

1   Ankur                 Fname      1
2   Gupta                 Lname      1
3   AAS Holdings          Company    1
4   Christie              Fname      2
5   Trojillo              Lname      2
6   AWS                   Company    2
7   Devadanam             Fname      3
8   Pulukuri              Lname      3
9   AAS Holdings          Company    3
10  Radha                 Fname      4
11  Neupane               Lname      4
......> There is no "Company" for that person
12  Marites               Fname      5
13  Torsar                Lname      5
14  AAS Holdings          Company    5
15  Muthanna              Fname      6
16  Shareef               Lname      6
17  GFTC                  Company    6

我需要能够以这种方式使用php mysql在HTML表中显示数据

First Name       Last Name       Company

Ankur            Gupta           ASS Holdings
Christie         Trojillo        AWS

例如，我还需要能够按公司对表格进行排序。
下面是我试图做的，但我知道这是完全错误的，因为数据库可能只有Fname和Lname，而没有公司，这将导致转义和使用下一个值并破坏表，而且数据可以按不同的顺序存储

$sql="SELECT * FROM system_frm_item_metas ORDER BY item_id";
$result=mysqli_query($con,$sql);
if ($result->num_rows > 0) {

echo "<table><tr><th>First Name</th><th>Last Name</th><th>Company</th></tr>";
while($row = $result->fetch_assoc()) {
   if ($row["field_id"] == "Fname") { echo "<tr><td>".$row["meta_value"]."</td>";}
   if ($row["field_id"] == "Lname") { echo "<td>".$row["meta_value"]."</td>";}
   if ($row["field_id"] == "Company") { echo "<td>".$row["meta_value"]."</td></tr>";}
}
echo "</table>";

任何帮助都将不胜感激

最佳答案

DROP TABLE IF EXISTS eav_hell;

CREATE TABLE eav_hell
(entity INT NOT NULL
,attribute VARCHAR(12) NOT NULL
,value VARCHAR(30) NOT NULL
,PRIMARY KEY(entity,attribute)
);

INSERT INTO eav_hell VALUES
(1 ,'Fname','Ankur'),
(1 ,'Lname','Gupta'),
(1 ,'Company','AAS Holdings'),
(2 ,'Fname','Christie'),
(2 ,'Lname','Trojillo'),
(2 ,'Company','AWS'),
(3 ,'Fname','Devadanam'),
(3 ,'Lname','Pulukuri'),
(3 ,'Company','AAS Holdings'),
(4 ,'Fname','Radha'),
(4 ,'Lname','Neupane'),
(5 ,'Fname','Marites'),
(5 ,'Lname','Torsar'),
(5 ,'Company','AAS Holdings'),
(6 ,'Fname','Muthanna'),
(6 ,'Lname','Shareef'),
(6 ,'Company','GFTC');

SELECT entity
     , MAX(CASE WHEN attribute = 'Fname' THEN value END) Fname
     , MAX(CASE WHEN attribute = 'Lname' THEN value END) Lname
     , MAX(CASE WHEN attribute = 'Company' THEN value END) Company
  FROM eav_hell
 GROUP
    BY entity;

+--------+-----------+----------+--------------+
| entity | Fname     | Lname    | Company      |
+--------+-----------+----------+--------------+
|      1 | Ankur     | Gupta    | AAS Holdings |
|      2 | Christie  | Trojillo | AWS          |
|      3 | Devadanam | Pulukuri | AAS Holdings |
|      4 | Radha     | Neupane  | NULL         |
|      5 | Marites   | Torsar   | AAS Holdings |
|      6 | Muthanna  | Shareef  | GFTC         |
+--------+-----------+----------+--------------+

或者（更快但不那么优雅）。。。

SELECT DISTINCT e.entity
              , f.value fname
              , l.value lname
              , c.value company
           FROM eav_hell e
           LEFT
           JOIN eav_hell f
             ON f.entity = e.entity
            AND f.attribute = 'fname'
           LEFT
           JOIN eav_hell l
             ON l.entity = e.entity
            AND l.attribute = 'lname'
           LEFT
           JOIN eav_hell c
             ON c.entity = e.entity
            AND c.attribute = 'company';

关于php - 查看数据透视表中的数据(垂直mysql表)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27820670/