SQL查询需求：关联两表展示子资产、账户及对应父资产

表结构回顾 #

• ManagedSystem表（资产表）
  • AssetName：资产名称
  • ManagedSystemID：资产唯一标识（两表关联字段）
• ManagedAccount表（账户表）
  • AccountName：账户名称
  • AccountID：账户唯一标识
  • ParentAccountID：父账户ID（关联自身表的AccountID）
  • ManagedSystemID：关联资产表的ID

核心实现思路 #

要实现“子资产对应账户有父ID时显示父资产”的需求，需要：

1. 通过自连接ManagedAccount表，把子账户和对应的父账户关联起来
2. 分别关联ManagedSystem表，获取子资产和父资产的名称
3. 用LEFT JOIN保证无父账户的记录也能被保留（父资产列显示NULL）

可行SQL语句 #

SELECT
    ms_child.AssetName AS 子资产,
    ma_child.AccountName AS 子账户,
    ms_parent.AssetName AS 父资产
FROM
    ManagedAccount ma_child
-- 关联子账户所属的子资产
LEFT JOIN ManagedSystem ms_child 
    ON ma_child.ManagedSystemID = ms_child.ManagedSystemID
-- 自连接找到子账户对应的父账户
LEFT JOIN ManagedAccount ma_parent 
    ON ma_child.ParentAccountID = ma_parent.AccountID
-- 关联父账户所属的父资产
LEFT JOIN ManagedSystem ms_parent 
    ON ma_parent.ManagedSystemID = ms_parent.ManagedSystemID

可选调整 #

如果只需要显示存在父资产的记录，可以添加WHERE条件：

SELECT
    ms_child.AssetName AS 子资产,
    ma_child.AccountName AS 子账户,
    ms_parent.AssetName AS 父资产
FROM
    ManagedAccount ma_child
LEFT JOIN ManagedSystem ms_child 
    ON ma_child.ManagedSystemID = ms_child.ManagedSystemID
LEFT JOIN ManagedAccount ma_parent 
    ON ma_child.ParentAccountID = ma_parent.AccountID
LEFT JOIN ManagedSystem ms_parent 
    ON ma_parent.ManagedSystemID = ms_parent.ManagedSystemID
WHERE
    ms_parent.AssetName IS NOT NULL

内容的提问来源于stack exchange，提问作者am1168