当子列为0时，SQL如何获取父级

Question

我下面有两个表格。

CREATE TABLE Name
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(256)
)

CREATE TABLE BranchAddress
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    UserId INT NOT NULL,
    ParentId INT NOT NULL,
    IsNetwork BIT,
    Address VARCHAR(256)
)

数据：

INSERT INTO dbo.Name ( Name )
VALUES ('George'), ('Greg')

INSERT INTO BranchAddress (UserId, ParentId, IsNetwork, Address)
VALUES (1, 0, 1, 'Kings Street'), (1, 1, 0, 'GS Road'),
       (1, 0, 0, 'Nariman Point'), (1, 1, 1, 'St. College Street'),
       (1, 2, 1, 'PG National Road'), (1, 1, 0, 'LOS India')

使用连接，我已经获取了两个表格中所需的信息，但在一个新的情况下，我需要提取Address，例如如果IsNetwork = 0，则地址应该是其父级的。

以下是连接查询的查询：

SELECT 
    t.Id, t.Name, t2.ParentId, t2.IsNetwork, t2.Address 
FROM 
    dbo.Name t 
INNER JOIN 
    BranchAddress t2 ON t.Id = t2.UserId 

这是该查询的输出结果：

Id          Name    ParentId IsNetwork    Address
----------- ------- ----------- --------- -------------------------
1           George  0           1         Kings Street
1           George  1           0         GS Road
1           George  0           0         Nariman Point
1           George  1           1         St.College Street
1           George  2           1         PG National Road
1           George  1           0         LOS India

我希望你能够输出以下内容。

Id          Name    ParentId IsNetwork    Address
----------- ------- ----------- --------- -------------------------
1           George  0           1         Kings Street
1           George  1           0         Kings Street
1           George  0           0         Kings Street
1           George  1           1         St.College Street
1           George  2           1         PG National Road
1           George  1           0         Kings Street

- Abhishek K. Upadhyay

---

有没有用于对行进行排序的列？否则会很困难。 - DineshDB

在您的期望输出中，对于Nariman Point地址，您也想要Kings街道，即使它的父级为0。这是打字错误吗？ - sam

Answer 1

你需要再次使用JOIN到BranchAddress来获取父级的Address，然后根据原始行的IsNetwork值选择要返回的地址。

SELECT n.Id, n.Name, b1.ParentId, b1.IsNetwork, 
       CASE WHEN b1.IsNetwork = 0 THEN b2.Address
            ELSE b1.Address
       END AS Address
FROM Name n
INNER JOIN BranchAddress b1 ON n.Id = b1.UserId
LEFT JOIN BranchAddress b2 ON b2.ID = b1.ParentId

输出

Id  Name    ParentId    IsNetwork   Address
1   George  0           true        Kings Street
1   George  1           false       Kings Street
1   George  1           false       Kings Street
1   George  1           true        St. College Street
1   George  2           true        PG National Road
1   George  1           false       Kings Street

请注意，Nariman Point的条目具有无效的ParentId（0），我已将其更改为1（以保持与预期结果的一致性）以进行演示。

在SQLFiddle上的演示

- Nick

Answer 2

在您期望的输出中，对于纳里曼角地址，您也希望使用 Kings 街道，即使其父级为 0。这是一个打字错误吗？

SELECT t.Id,t.Name,t2.ParentId,t2.IsNetwork,
     case when t2.isNetWork = 0 and t2.ParentId > 0  then t3.Address else t2.Address end as Address
     FROM [dbo].[Name] t 
     INNER JOIN dbo.BranchAddress t2 ON t.Id = t2.UserId
     left JOIN dbo.BranchAddress t3 on t3.id = t2.ParentId

- sam

Answer 3

在父节点零中添加一个额外的左连接，该连接是一个网络连接。但仅适用于非网络地址。

SELECT 
usr.Id, 
usr.Name,
addr1.ParentId, 
addr1.IsNetwork,
COALESCE(addr2.Address, addr1.Address) as Address2
FROM dbo.Name usr
INNER JOIN BranchAddress addr1
  ON addr1.UserId = usr.Id
LEFT JOIN BranchAddress addr2
  ON addr2.UserId = usr.Id
 AND addr2.ParentId = 0
 AND addr2.IsNetwork = 1
 AND addr1.IsNetwork = 0

- LukStorms

Answer 4

首先运行SELECT * FROM [Name]

SELECT t.Id,t.Name,t2.ParentId,t2.IsNetwork,t2.Address 
FROM [Name] t LEFT OUTER JOIN BranchAddress t2 ON t.Id = t2.UserId 

- Mohamed Farouk

Answer 5

你可以尝试这个方法来选择父级元素

Select t.Id,t.Name,t2.ParentId,t2.IsNetwork,case when IsNetwork = 0 then (select Address from BranchAddress where Id in (select min(Id) from BranchAddress)) else t2.Address end 'Address' 
FROM dbo.Name t 
INNER JOIN BranchAddress t2 
ON t.Id = t2.UserId 

- Sagar Kumar