单表子查询

发布于 2024-11-18 22:13:23 字数 738 浏览 4 评论 0原文

好吧，我有一个表，

Tasks
--
TaskId  (unique autoinc primary)
ChildOf   (Contains task ID of parent, or 0 if top tier (no parent))

我需要编写一个查询来选择 ChildOf = 0 的所有记录...简单吧？

好的，但还需要返回另一列，其中的结果告诉每个任务有多少个子项...

所以结果看起来像这样...

TaskID ...  ChildOf ... countChildren
 37   ......  0   ....    3
 42   ......  0   ....    0
 99   ......  0   ....    1 

etc....

我知道我需要的两个查询是这样的，但需要以某种方式将它们组合起来...

Select TaskId as ParentTaskId, ChildOf from Tasks where ChildOf = 0

并且

Select count(TaskId) from Tasks where ChildOf = ParentTaskId

注意：只有 2 层...父母和孩子...没有孙子！所以希望这能让事情变得不那么复杂。

非常感谢任何帮助。感谢迄今为止所有的帮助！

原文

Ok so I have a table

Tasks
--
TaskId  (unique autoinc primary)
ChildOf   (Contains task ID of parent, or 0 if top tier (no parent))

I need to write a query that selects all records with ChildOf = 0 ... Simple right ?

Ok but also need to have another column returned with the results that tells how many Children each Task has ...

So result would look like this ...

TaskID ...  ChildOf ... countChildren
 37   ......  0   ....    3
 42   ......  0   ....    0
 99   ......  0   ....    1 

etc....

I know the two queries I need are something like this, but need to combine them somehow...

Select TaskId as ParentTaskId, ChildOf from Tasks where ChildOf = 0

and

Select count(TaskId) from Tasks where ChildOf = ParentTaskId

NOTE: There are only 2 tiers.. Parent and Child ... No Grandchildren! So hopefully that makes it a bit less complicated.

Any help is greatly appreciated. Thanks for all the help so far!

分享到QQ

分享到微博

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

发布评论

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

坐在坟头思考人生 2024-11-25 22:13:23

像这样的事情应该这样做：

SELECT TaskId as ParentTaskId, ChildOf , 
 (SELECT count(t2.TaskId) 
  FROM Tasks t2
  WHERE t2.ChildOf = t.TaskId) as CountChildren
FROM Tasks t 
WHERE t.ChildOf = 0

Something like this should do it:

SELECT TaskId as ParentTaskId, ChildOf , 
 (SELECT count(t2.TaskId) 
  FROM Tasks t2
  WHERE t2.ChildOf = t.TaskId) as CountChildren
FROM Tasks t 
WHERE t.ChildOf = 0

回复收藏 0 原文

雨后彩虹 2024-11-25 22:13:23

试试这个：

SELECT T1.TaskID, T1.ChildOf, count(*) from 
  Tasks as T1 join Tasks as T2 on T1.TaskID = T2.ChildOf 
  WHERE T1.ChildOf = 0 
    GROUP BY (T1.TaskID)

Try this:

SELECT T1.TaskID, T1.ChildOf, count(*) from 
  Tasks as T1 join Tasks as T2 on T1.TaskID = T2.ChildOf 
  WHERE T1.ChildOf = 0 
    GROUP BY (T1.TaskID)

回复收藏 0 原文

~没有更多了~