平面视图的亲子关系

发布于 2024-12-05 20:44:30 字数 404 浏览 0 评论 0原文

有没有一种简单的方法可以将具有父子关系的表迁移到第一列？

INPUT_TABLE

PARENT_ID,ID,NAME
null,1, USA
1   ,2, Las Vegas
2   ,3, City in las Vegas
2   ,4, Another City in las Vegas
.. a lot more

输出

ID, COUNTRY, CITY, PLACE
1,  USA, null,null
2,  USA, Las Vegas,null
3,  USA, Las Vegas,City in las Vegas
4,  USA, Las Vegas,Another City in las Vegas

提前致谢

原文

Is there an easy way to migrate a table with a parent child relation to a column one ?

INPUT_TABLE

PARENT_ID,ID,NAME
null,1, USA
1   ,2, Las Vegas
2   ,3, City in las Vegas
2   ,4, Another City in las Vegas
.. a lot more

OUTPUT

ID, COUNTRY, CITY, PLACE
1,  USA, null,null
2,  USA, Las Vegas,null
3,  USA, Las Vegas,City in las Vegas
4,  USA, Las Vegas,Another City in las Vegas

Thanks in advance

分享到QQ

分享到微博

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

发布评论

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

若水微香 2024-12-12 20:44:30

如果您根据注释拥有 3 个项目的层次结构，并且无需保留没有 City 或 Place 的行，则将表与其自身连接两次就足够了。

SQL 语句

SELECT  ID = Country.ID
        , Country = Country.NAME
        , City = City.NAME
        , Place = Place.Name
FROM    q Country
        INNER JOIN q City ON City.PARENT_ID = Country.ID
        INNER JOIN q Place ON Place.PARENT_ID = City.ID

(SQL Server) 测试脚本

;WITH q (PARENT_ID, ID, NAME) AS (
  SELECT null, 1, 'USA'
  UNION ALL SELECT 1, 2, 'Las Vegas'
  UNION ALL SELECT 2, 3, 'City in las Vegas'
  UNION ALL SELECT 2, 4, 'Another City in las Vegas'
)
SELECT  ID = Country.ID
        , Country = Country.NAME
        , City = City.NAME
        , Place = Place.Name
FROM    q Country
        INNER JOIN q City ON City.PARENT_ID = Country.ID
        INNER JOIN q Place ON Place.PARENT_ID = City.ID

Provided you have a hierarchie of 3 items as per your comments and there's no need to retain rows that have no City or Place, joining the table twice with itself would suffice.

SQL Statement

SELECT  ID = Country.ID
        , Country = Country.NAME
        , City = City.NAME
        , Place = Place.Name
FROM    q Country
        INNER JOIN q City ON City.PARENT_ID = Country.ID
        INNER JOIN q Place ON Place.PARENT_ID = City.ID

(SQL Server) Test script

;WITH q (PARENT_ID, ID, NAME) AS (
  SELECT null, 1, 'USA'
  UNION ALL SELECT 1, 2, 'Las Vegas'
  UNION ALL SELECT 2, 3, 'City in las Vegas'
  UNION ALL SELECT 2, 4, 'Another City in las Vegas'
)
SELECT  ID = Country.ID
        , Country = Country.NAME
        , City = City.NAME
        , Place = Place.Name
FROM    q Country
        INNER JOIN q City ON City.PARENT_ID = Country.ID
        INNER JOIN q Place ON Place.PARENT_ID = City.ID

回复收藏 0 原文

~没有更多了~