SQL - 在单个查询中选择"child"行和"parent"行

Question

我有一个数据库，里面装满了很多"空"记录，我想去掉那些孤立的记录。

数据库的结构是这样的

1. 包含实际条目(meta_value)的子行
2. "链接"到其子行(通过其meta_key)的父行

---

| meta_id post_id meta_key meta_value | | | | 011 301 ACF__P_01_01 Foo | | 012 309 _ACF__P_01_01 field_5874d5 | | 013 321 ACF__P_01_02 | | 014 316 _ACF__P_01_02 field_54290a | | 015 119 ACF__P_01_03 Bar | | 016 101 _ACF__P_01_03 field_a04a88 | | 017 119 ACF__P_01_03 | | 018 101 _ACF__P_01_03 field_a04a88 | | 019 149 ACF__P_01_03 | | 020 111 _ACF__P_01_03 field_a04a88 | | 021 169 ACF__P_01_03 Foo Bar | | 022 171 _ACF__P_01_03 field_a04a88 |

---

使用这样的查询可以很容易地选择所有(空的)"子"行；

SELECT
*
FROM
wp_postmeta
WHERE
wp_postmeta.meta_key LIKE '%ACF__%'
AND
wp_postmeta.meta_value LIKE ''

但是这个查询只获取(空的)"子"行，而不是它们的"父"行。

---

有两个逻辑条件将父对象绑定到子对象；

1. 第一行始终是子行，紧接着是其父行
2. 父行具有与其子行相同的">meta_key"值，但由下划线预先固定

---

有没有办法创建一个SQL查询来选择空的子记录(就像我上面做的那样)和它的父记录？

我更喜欢用一个查询来同时获取它们，但当这不可能时，我也可以运行两个查询(第一个用于获取父查询，第二个用于获取空的子查询，我想)。

我使用Navicat进行数据库管理，所以它必须是纯SQL，而不是PHP。

---

查询应该像这样吐出结果

---

| meta_id post_id meta_key meta_value | | | | 013 321 ACF__P_01_02 | | 014 316 _ACF__P_01_02 field_54290a | | 017 119 ACF__P_01_03 | | 018 101 _ACF__P_01_03 field_a04a88 | | 019 149 ACF__P_01_03 | | 020 111 _ACF__P_01_03 field_a04a88 |

---

所以空的子项("meta_value"中没有值)和它的父项(都有相同的"meta_key"，其中父项有下划线。

Answer 1

使用自加入

Select *
from wp_postmeta c
join wp_postmeta p
on p.meta_key = '_' + c.meta_key

以上操作将仅获取父子记录的"对"。如果父母没有孩子是可能的(我认为没有父母是不可能的)，并且你希望所有的父母，包括没有孩子的父母，都使用外部连接

Select *
from wp_postmeta p
left join wp_postmeta c
on '_' + c.meta_key = p.meta_key 

在关系数据库中，没有"after"或"before"这样的概念，除非你自己定义它，并将数据添加到实现该概念的表中(如日期时间戳或按顺序递增的数字键，或其他什么)。如果没有这样的工件，关系数据库表中的记录就不会有任何隐含的顺序。

Answer 2

使用PHP我解决了这个问题。我迭代查询的第一个结果(获取所有空的子项)，并在循环中获取该子项之后的每一行。

但那是PHP——我想要纯SQL：)

<?php
$connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($connection -> connect_errno > 0) {
die ("Unable to connect to database [" . $connection->connect_error . "]");
}
$query_1 = "SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE '%ACF%__' AND `meta_value` LIKE ''";
if (!$result_1 = $connection -> query($query_1)) {
die ("There was an error running query[" . $connection -> error . "]");
}
if ($result_1) {
$i = 0;
echo "<h2>ACF - empty fields</h2>";
echo "<table border=1 cellpadding=4 cellspacing=0 width=800>";
echo "<tr style='background:#eee;font-weight:bold;'><td>row</td><td>meta_id</td><td>post_id</td><td>meta_key</td><td>meta_value</td></tr>";
while ($row_1 = $result_1 -> fetch_assoc()) {
$i++;
echo "<tr>";
echo "<td width=100>" . sprintf('%03d', $i) . "</td>";
echo "<td width=100 style='background:#aaa;color:#fff;font-weight:bold;'>" . $row_1["meta_id"] . "</td>";
echo "<td width=100>" . $row_1["post_id"] . "</td>";
echo "<td width=100>" . $row_1["meta_key"] . "</td>";
echo "<td width=500>" . $row_1["meta_value"] . "</td>";
echo "</tr>";
$query_2 = "SELECT * FROM `wp_postmeta` WHERE `meta_id` = " . ($row_1["meta_id"] + 1);
if (!$result_2 = $connection -> query($query_2)) {
die ("There was an error running query[" . $connection -> error . "]");
}
while ($row_2 = $result_2 -> fetch_assoc()) {
$i++;
echo "<tr>";
echo "<td width=100>" . sprintf('%03d', $i) . "</td>";
echo "<td width=100 style='background:#aaa;color:#fff;font-weight:bold;'>" . $row_2["meta_id"] . "</td>";
echo "<td width=100>" . $row_2["post_id"] . "</td>";
echo "<td width=100>" . $row_2["meta_key"] . "</td>";
echo "<td width=500>" . $row_2["meta_value"] . "</td>";
echo "</tr>";
}
mysqli_free_result($result_2);
}
mysqli_free_result($result_1);
echo "</table>";
} else {
echo "No entries found.";
}
?>

-编辑-

这个PHP代码更好，我想。。。它只使用了两个查询，并对第一个查询进行迭代。。。

它工作得很好，但仍然不是纯SQL——我想去掉PHP。。。

<?php
/* --------------------------------------------------- */
$connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($connection -> connect_errno > 0) {
die ("Unable to connect to database [" . $connection->connect_error . "]");
}
/* --------------------------------------------------- */
$query = "SELECT `meta_id` FROM `wp_postmeta` WHERE `meta_key` LIKE '%ACF%__' AND `meta_value` LIKE ''";
if (!$result = $connection -> query($query)) {
die ("There was an error running query[" . $connection -> error . "]");
}
if ($result) {
$array = array();
while ($row = $result -> fetch_assoc()) {
$array[] = $row["meta_id"];
$array[] = $row["meta_id"] + 1;
}
mysqli_free_result($result);
/* --------------------------------------------------- */
$query = "SELECT * FROM `wp_postmeta` WHERE `meta_id` IN (" . implode(",", array_map("intval", $array)) . ")";
if (!$result = $connection -> query($query)) {
die ("There was an error running query[" . $connection -> error . "]");
}
if ($result) {
$i = 0;
echo "<h2>ACF - empty fields</h2>";
echo "<table>";
echo "<tr><td>record</td><td>meta_id</td><td>post_id</td><td>meta_key</td><td>meta_value</td></tr>";
while ($row = $result -> fetch_assoc()) {
$i++;
echo "<tr>";
echo "<td>" . sprintf('%03d', $i) . "</td>";
echo "<td>" . $row["meta_id"] . "</td>";
echo "<td>" . $row["post_id"] . "</td>";
echo "<td>" . $row["meta_key"] . "</td>";
echo "<td>" . $row["meta_value"] . "</td>";
echo "</tr>";
}
mysqli_free_result($result);
echo "</table>";
if (isset($_GET["delete"])) {
echo "<span>all records deleted</span>";
} else {
echo "<a href='?delete'>delete empty records</a>";
}
}
/* --------------------------------------------------- */
} else {
echo "No entries found.";
}
?>