从自引用表获取生育状况

发布于 2024-08-20 13:59:18 字数 567 浏览 6 评论 0原文

假设您有下表: 项目(item_id,item_parent) ...它是一个自引用表,因为 item_parent 引用 item_id。

您将使用什么 MySQL 支持的 SQL 查询来 SELECT 表中的每个项目以及一个布尔值,该布尔值指示该项目是否是父项目/是否有其他项目引用它?

如果表中有以下数据:

item_id     item_parent
----------- -----------
1           0          
2           0            
3           2          
4           2          
5           3          

...查询应该能够检索以下对象集:

{"item_id":1,"is_parent":0}
{"item_id":2,"is_parent":1}
{"item_id":3,"is_parent":1}
{"item_id":4,"is_parent":0}
{"item_id":5,"is_parent":0}

Let's say you have the following table:
items(item_id, item_parent)
... and it is a self-referencing table as item_parent refers to item_id.

What MySQL supported SQL query would you use to SELECT each item in the table along with a boolean value that indicates whether that item is a parent / has other items referencing to it?

If you have the following data in the table:

item_id     item_parent
----------- -----------
1           0          
2           0            
3           2          
4           2          
5           3          

... the query should be able to retrieve the following set of objects:

{"item_id":1,"is_parent":0}
{"item_id":2,"is_parent":1}
{"item_id":3,"is_parent":1}
{"item_id":4,"is_parent":0}
{"item_id":5,"is_parent":0}

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

初吻给了烟 2024-08-27 13:59:18
SELECT  i.item_id,
        item_id IN
        (
        SELECT  item_parent
        FROM    items
        )
FROM    items i
SELECT  i.item_id,
        item_id IN
        (
        SELECT  item_parent
        FROM    items
        )
FROM    items i
探春 2024-08-27 13:59:18

这将返回所有项目和一个整数,指定每个项目有多少个子项:

SELECT T1.item_id, COUNT(T2.item_parent) AS is_parent
FROM items AS T1
LEFT JOIN items AS T2
ON T1.item_id = T2.item_parent
GROUP BY item_id

如果您想要一个布尔值(0 或 1),只需将其更改为:

SELECT T1.item_id, COUNT(T2.item_parent) > 0 AS is_parent
FROM items AS T1
LEFT JOIN items AS T2
ON T1.item_id = T2.item_parent
GROUP BY item_id

This returns all items and an integer specifying how many children each has:

SELECT T1.item_id, COUNT(T2.item_parent) AS is_parent
FROM items AS T1
LEFT JOIN items AS T2
ON T1.item_id = T2.item_parent
GROUP BY item_id

If you want a boolean (0 or 1) just change it to:

SELECT T1.item_id, COUNT(T2.item_parent) > 0 AS is_parent
FROM items AS T1
LEFT JOIN items AS T2
ON T1.item_id = T2.item_parent
GROUP BY item_id
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文