不要被透视查询技术吓到。就像GROUPING一样简单，然后调用
MAX()简单的case语句。
询问：
SELECt `post_id`, MAX(CASE WHEN `metar_key` = '_field_14' THEN `meta_value` ELSE NULL END) AS `field 14`, MAX(CASE WHEN `metar_key` = '_field_15' THEN `meta_value` ELSE NULL END) AS `field 15`, MAX(CASE WHEN `metar_key` = '_field_16' THEN `meta_value` ELSE NULL END) AS `field 16`FROM `booking`GROUP BY `post_id`HAVINg field14 IS NOT NULL AND field15 IS NOT NULL AND field16 IS NOT NULLORDER BY `post_id`;
- edit：该
HAVINg
子句忽略了完全由NULL
值组成的生成的行，这是OP所要求的。然后，只需像平常一样处理结果集行即可。
结果集：
post_id | field 14 | field 15 | field 16--------|-------------|-----------|----------- 490 | IND | LHSM | 2018-07-07 491 | ERK | LHKE | 2018-07-08
这是一个代码片段，其中包含错误检查点，以向您展示如何处理结果集：
echo '<body>';if (!$conn = new mysqli('localhost', 'admin', '', 'test')) { echo 'Connection Error'; // $conn->connect_error; // never show the exact error message to the public} else { $pivot = "SELECt `post_id`, MAX(CASE WHEN `metar_key` = '_field_14' THEN `meta_value` ELSE NULL END) AS `field14`, MAX(CASE WHEN `metar_key` = '_field_15' THEN `meta_value` ELSE NULL END) AS `field15`, MAX(CASE WHEN `metar_key` = '_field_16' THEN `meta_value` ELSE NULL END) AS `field16` FROM `booking` GROUP BY `post_id` ORDER BY `post_id`"; if (!$result = $conn->query($pivot)) { echo 'Syntax Error'; // $conn->error; // never show the exact error message to the public } else { if (!$result->num_rows) { echo 'No Rows Returned From Pivot Query'; } else { echo '<table>'; echo '<tr><th>Field14</th><th>Field15</th><th>Field16</th></tr>'; while ($row = $result->fetch_assoc()) { echo "<tr><td>{$row['field14']}</td><td>{$row['field15']}</td><td>{$row['field16']}</td></tr>"; } echo '</table>'; } // $result->free(); // just a consideration } // $conn->close(); // just a consideration}echo '</body>';欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）