SQL QUERY - 如何提取表的最后一个子项? [英] SQL QUERY - How to extract last child of a table?

问题描述

我有一个按国家、地区、子地区和城市组织的人群的 MYSQL 表.

I have a MYSQL table of groups of people organised by country, region, sub region and city.

当访客加入一个组时，他选择一个城市，我们会自动将他添加到父组子区域"、区域"、国家"中.

When visitor join a group, he select a city and we automatically add him in the parents groups "sub-region", "region, "country".

例如:约翰选择了伦敦.因此，他将加入伦敦、大伦敦、英格兰、英国的组别.我们得到这样的父子表:

For example: John selected London. So he will be added in groups London, Greater London, England, UK. We get a parent-child table like this:

http://prntscr.com/3kui69

我需要提取城市组的所有行.如何识别城市群?它是唯一一个其 ID 不在其他行的 id_parent 字段中的行.是的！城市组行不能是其他组的父级.所以我们在 id_parent 字段中找不到城市组 id.

I need to extract all the rows for the city groups. How to recognise a city groups? It is the only rows where its ID is not in id_parent field of other rows. Yes! City groups rows can't be parents of other groups. So we can't find city groups id in id_parent fields.

既然我们知道了他，我该如何用 SQL 语言提取城市组行?对我来说太复杂了.

Now that we know his, how can I extract the city groups rows with SQL language? It is too complicate for me.

提前致谢.

推荐答案

请尝试使用此查询:

select * from table
where id not in (select id_parent from table);

这是一个例子:http://sqlfiddle.com/#!2/7de26/1

这篇关于SQL QUERY - 如何提取表的最后一个子项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！