mysql - 如何分隔多列查询的结果

Question

我有这张 table

id  bigint(20) unsigned Incrément automatique    
form_id        mediumint(8)      unsigned [0]    
entry_id       bigint(20)        unsigned    
meta_key       varchar(255)      NULL    
meta_value     longtext          NULL

它显示这样的数据:

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg .tg-yw4l{vertical-align:top}
</style>
<table class="tg">
  <tr>
    <th class="tg-031e">id</th>
    <th class="tg-031e">form_id</th>
    <th class="tg-031e">entry_id</th>
    <th class="tg-yw4l">meta_key</th>
    <th class="tg-yw4l">meta_value</th>
  </tr>
  <tr>
    <td class="tg-031e">3</td>
    <td class="tg-031e">1</td>
    <td class="tg-031e">1</td>
    <td class="tg-yw4l">14</td>
    <td class="tg-yw4l">Address 1</td>
  </tr>
  <tr>
    <td class="tg-031e">6</td>
    <td class="tg-031e">1</td>
    <td class="tg-031e">1</td>
    <td class="tg-yw4l">20</td>
    <td class="tg-yw4l">1</td>
  </tr>
  <tr>
    <td class="tg-yw4l">7</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">21</td>
    <td class="tg-yw4l">2018-16-05</td>
  </tr>
  <tr>
    <td class="tg-yw4l">8</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">23</td>
    <td class="tg-yw4l">Product 1</td>
  </tr>
  <tr>
    <td class="tg-yw4l">11</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">2</td>
    <td class="tg-yw4l">14</td>
    <td class="tg-yw4l">Address 2</td>
  </tr>
  <tr>
    <td class="tg-yw4l">14</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">2</td>
    <td class="tg-yw4l">20</td>
    <td class="tg-yw4l">1</td>
  </tr>
  <tr>
    <td class="tg-yw4l">15</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">2</td>
    <td class="tg-yw4l">21</td>
    <td class="tg-yw4l">2018-16-05</td>
  </tr>
  <tr>
    <td class="tg-yw4l">16</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">2</td>
    <td class="tg-yw4l">23</td>
    <td class="tg-yw4l">Product2</td>
  </tr>
  <tr>
    <td class="tg-yw4l">20</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">3</td>
    <td class="tg-yw4l">14</td>
    <td class="tg-yw4l">Address3</td>
  </tr>
  <tr>
    <td class="tg-yw4l">21</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">3</td>
    <td class="tg-yw4l">20</td>
    <td class="tg-yw4l">1</td>
  </tr>
  <tr>
    <td class="tg-yw4l">22</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">3</td>
    <td class="tg-yw4l">21</td>
    <td class="tg-yw4l">2018-16-05</td>
  </tr>
  <tr>
    <td class="tg-yw4l">23</td>
    <td class="tg-yw4l">1</td>
    <td class="tg-yw4l">3</td>
    <td class="tg-yw4l">23</td>
    <td class="tg-yw4l">Prodcut3</td>
  </tr>
</table>

我需要的是像 Select 请求这样的东西:

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
</style>
<table class="tg">
  <tr>
    <th class="tg-031e">meta_value1</th>
    <th class="tg-031e">meta_value2</th>
    <th class="tg-031e">meta_value3</th>
    <th class="tg-031e">meta_value4</th>
    <th class="tg-031e"></th>
  </tr>
  <tr>
    <td class="tg-031e">Address 1</td>
    <td class="tg-031e">1</td>
    <td class="tg-031e">2018-16-05</td>
    <td class="tg-031e">Product1</td>
    <td class="tg-031e"></td>
  </tr>
  <tr>
    <td class="tg-031e">Address 2</td>
    <td class="tg-031e">1</td>
    <td class="tg-031e">2018-16-05</td>
    <td class="tg-031e">Product2</td>
    <td class="tg-031e"></td>
  </tr>
  <tr>
    <td class="tg-031e">Address 3</td>
    <td class="tg-031e">1</td>
    <td class="tg-031e">2018-16-05</td>
    <td class="tg-031e">Prodcut3</td>
    <td class="tg-031e"></td>
  </tr>
  <tr>
    <td class="tg-031e"></td>
    <td class="tg-031e"></td>
    <td class="tg-031e"></td>
    <td class="tg-031e"></td>
    <td class="tg-031e"></td>
  </tr>
</table>

提前谢谢你

Answer 1

一种方法是按条目对元值进行分组，然后将元值连接成一个字符串:

SELECT entry_id, GROUP_CONCAT(meta_value) AS meta_string
FROM your_table
GROUP BY entry_id

这将返回:

| 1 |地址 1, 1, 2018-16-05, 产品 1 |

| 2 |地址 2, 1, 2018-16-05, 产品 2 |等等

然后，您可以分解 meta_string 并构建您的表，或者您可以将 html 作为查询中的分隔符，例如:

GROUP_CONCAT(meta_value ASC SEPARATOR '</td></tr><td>')

这将返回格式如下的元数据:

Address 1</td><td>1</td><td>2018-16-05</td><td>Product 1

事实上，您需要在您的查询(请参阅下一步)或您的代码中为 tr 和第一个/最后一个 td/td 添加包装器。

CONCAT('<tr><td>, GROUP_CONCAT(meta_value ASC SEPARATOR '</td></tr><td>', </td></tr>)) AS full_html_row

如果您需要 th 的 meta_key，请执行相同的操作，但像这样:

SELECT entry_id, GROUP_CONCAT(meta_key) AS keys_string
FROM your_table
WHERE entry_id = 1
GROUP BY entry_id
UNION
SELECT entry_id, GROUP_CONCAT(meta_value) AS meta_string
FROM your_table
GROUP BY entry_id

要在分隔的列中获取结果，另一种方法是使用 CASE。但是，这不如在 PHP 或其他程序中转换原始数据有效。

SELECT entry_id,
  MAX(CASE WHEN meta_key = 14 THEN meta_value ELSE NULL END) AS meta_value1,
  MAX(CASE WHEN meta_key = 20 THEN meta_value ELSE NULL END) AS meta_value2,
  MAX(CASE WHEN meta_key = 21 THEN meta_value ELSE NULL END) AS meta_value3,
  MAX(CASE WHEN meta_key = 23 THEN meta_value ELSE NULL END) AS meta_value4
FROM your_table
GROUP BY entry_id