更新时间:2021-12-26 01:25:49
不要被透视查询技术吓到.就像GROUPING一样简单,然后在简单的case语句上调用 MAX()
.
Don't be scared off by the pivot query technique. It's as simple as GROUPing and then calling MAX()
on simple case statements.
查询:
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 NULL
ORDER BY `post_id`;
* edit:根据OP的要求, HAVING
子句会忽略生成的行,这些行完全由 NULL
值组成.然后只需像往常一样处理结果集行即可.
*edit: The HAVING
clause omits generated rows that are fully comprised of NULL
values -- as requested by the OP.
Then just process the resultset rows as you would normally.
结果集:
post_id | field 14 | field 15 | field 16
--------|-------------|-----------|-----------
490 | IND | LHSM | 2018-07-07
491 | ERK | LHKE | 2018-07-08
这是一个代码段,其中包含错误检查点,以向您展示如何处理结果集:
Here is a code snippet, packed with error checkpoints to show you how to process the resultset:
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>';