sql - 查询以获取所有兄弟姐妹

Question

我有这样的表：

 ID NAME SURNAME MotherID FatherID
    0   JJ   smi       NULL    NULL
    1   ja   rly       NULL    NUL
    2   ak   smi       0        1
    3   ol   smi       0        1
    4   oa   woo       2        3
    5   oe   boo       2        3

    etc.

我需要使用特定参数作为 NAME 和 surname 的查询将返回我一个人的所有兄弟姐妹。预期产出

NAME SURNAME FATHERNAME FATHERSURNAME MOTHERNAME MOTHERSURNAME
AK   SMI        JA        RLY             JJ        SMI        
OL   SMI        JA        RLY             JJ        SMI 

我试过了

SELECT 
      a.name,
      a.surname 
FROM PEOPLE a, PEOPLE b
WHERE (b.name = 'ak' AND b.surname ='smi' AND 
      (b.motherID = a.ID OR b.fatherid = ID))

标签： sqlsql-server

Answer 1

您可以使用SUB QUERY来实现此目的。

表创建：

DECLARE @T TABLE(
ID INT,
NAME VARCHAR(MAX),
SURNAME VARCHAR(MAX),
MOTHERID INT,
FATHERID INT)

表格插入：

Insert into @t Values(0,'JJ','smi',NULL,NULL)
Insert into @t Values(1,'ja','rly',NULL,NULl)
Insert into @t Values(2,'ak','smi',0,1)
Insert into @t Values(3,'ol','smi',0,1)
Insert into @t Values(4,'oa','woo',2,3)
Insert into @t Values(5,'oe','boo',2,3)

询问：

SELECT S.NAME AS NAME,S.SURNAME AS SURNAME,S.FATHER_NAME ,S.FATHER_SURNAME,M.NAME AS 
MOTHER_NAME,M.SURNAME AS MOTHER_SURNAME 
FROM @T M INNER JOIN(
SELECT T2.NAME AS NAME,T2.SURNAME AS SURNAME,T1.NAME AS FATHER_NAME,T1.SURNAME AS 
FATHER_SURNAME,T2.MOTHERID
FROM @T T1
INNER JOIN @T T2 ON  T1.ID=T2.FATHERID 
WHERE T2.NAME IN ('AK','OL'))S ON M.ID=S.MOTHERID

输出：

另外，根据您的参数要求添加WHERE子句。