家族树

前几天碰到一个关于家庭树方面的问题，这是解决办法：
建库脚本：
成员信息表：

CREATE TABLE [tbMember] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [gender] [int] NULL ,
 [birthdate] [datetime] NULL ,
 CONSTRAINT [PK_tbMember] PRIMARY KEY  CLUSTERED
 (
  [id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

成员关系表：

CREATE TABLE [tbRelation] (
 [id] [int] NOT NULL ,
 [parid] [int] NOT NULL ,
 [creator] [int] NULL ,
 [relation] [int] NULL ,
 CONSTRAINT [PK_tbRelation] PRIMARY KEY  CLUSTERED
 (
  [id],
  [parid]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

 

T-SQL:
 Relation:0=直系有血缘关系下上代关系，1=直系无血缘关系下上代,2=夫妻关系

declare @id int
set @id=5

select r.parid,m.name,gender,birthdate,relation from dbo.tbRelation r
left join dbo.tbMember m
on r.parid=m.id
where r.id=@id and relation!=2

select distinct r.id,m.name,m.gender,m.birthdate,relation from dbo.tbRelation r
left join tbMember m
on r.id=m.id
where r.parid in (select parid from  tbRelation where id=@id and relation=0)
--得到妻子

select r.parid,m.name,gender,birthdate,relation from tbRelation as r
left join tbMember m
on r.parid=m.id
where r.id=@id  and relation=2

--得到丈夫
select r.id,m.name,gender,birthdate,relation from tbRelation as r
left join tbMember m
on r.id=m.id
where r.parId=@id  and relation=2

select r.id,m.name,gender,birthdate,relation  from tbRelation r
left join tbMember m
on r.id=m.id
where parid=@id