父母/孩子在同一张桌子 [英] Parent / Child in same table

问题描述

我的表格结构如下:

id |parent|name |value
1  | 0    | aaa |
2  | 0    | bbb |
3  | 0    | ccc |
4  | 1    |     | 111
5  | 1    |     | 222
6  | 3    |     | 333

如果它有子记录，我想显示它的父母.

I want to display parent if it has child records.

赞:

(父ID +名称+值第一个孩子)

(parent id + name + value first child)

1 - aaa - 111
3 - ccc - 333

推荐答案

数据库中第一个孩子没有任何意义，您可以通过id的最小值或值的最小值获得第一个孩子，但是这些值未按特定顺序存储在表中，因此您无法确定哪个值是第一个.

There is no meaning of the first child in the database, you can get the first child by the mininum of the id or the minimum of the value, but the values are not stored with a specific order in the table, so you can't tell which value is the first one.

但是，假设id是自动增量列，则第一个子项的值是最小值id的值，那么您可以执行以下操作:

But, assuming that the id is auto incremental column, then value of the first child is the value of the minimum id, then you can do this:

SELECT
  t1.parent,
  t2.name,
  t1.value
FROM tablename AS t1
INNER JOIN
(
  SELECT MIN(id) AS id, parent
  FROM tablename
  GROUP BY parent
) AS t22 ON t22.id = t1.id AND t1.parent = t22.parent
INNER JOIN tablename AS t2 ON t1.parent = t2.id;

在此处查看其运行情况:

See it in action here:

• SQL小提琴演示

这会给你:

| PARENT | NAME | VALUE |
-------------------------
|      1 |  aaa |   111 |
|      3 |  ccc |   333 |

---

或:您可以通过最小值获得它:

---

Or: You can get it by the minimum value:

SELECT
  t1.parent,
  t2.name,
  MIN(t1.value) AS value
FROM tablename AS t1
INNER JOIN tablename AS t2 ON t1.parent = t2.id
GROUP BY t1.parent, t2.name;

查看实际效果:

• SQL小提琴演示

这将为您提供:

| PARENT | NAME | VALUE |
-------------------------
|      1 |  aaa |   111 |
|      3 |  ccc |   333 |

这篇关于父母/孩子在同一张桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！