实际T—SQL问题如何解决！！！！！！！！

wisdomone 2006-08-24 04:31:14

-------建立family数据库--------

create database family
------建立表person-------
create table person(
personid int not null primary key,
[name] char(6) not null,
gender char(2) not null,
fatherid int null,
motherid int null,
dateofbirth datetime not null,
addofbirth char(100) not null,
addofcurr char(100) null,
dateofdeath datetime null)
--------------------在表person建立自反关系-------------
use family
go
alter table person add constraint fk_person_father
foreign key(fatherid) references dbo.person(personid)
alter table person add constraint fk_person_mother

------------------------------插入示例数据向表person----------------------------------
use family
INSERT dbo.Person (PersonID, [name], Gender, FatherID, MotherID, DateOfBirth, addofbirth,addofcurr,DateOfDeath)
VALUES(1, '翟明龙', '男', NULL, NULL, '1/1/1901','军宅村','军宅村','8/17/1970')
go
insert dbo.person(PersonID, [name], Gender, FatherID, MotherID, DateOfBirth, addofbirth,addofcurr,DateOfDeath)
values(2 ,'翟振南','男',1,null,'2/03/1932','军宅村','军宅村','12/09/2004')
go

insert dbo.person
values(3,'韩碧霞','女',null,null,'9/04/1933',' 王仁村','军宅村','8/28/2005')
go
insert dbo.person
values(4,'翟大理','男',2,3,'7/09/1956','军宅村','军宅村',null)
go
insert dbo.person
values(5,'王玉英','女',null,null,'9/18/1956','卤川村','军宅村',null)
go
insert dbo.person
values(6,'翟勋扬','男',4,5,'8/28/1981','军宅村','师大附中',null)
go
insert dbo.person
values(7,'翟勋章','男',4,5,'6/28/1984','军宅村','山东村',null)
go
insert dbo.person
values(8,'翟小理','男',2,3,'8/28/1958','军宅村','军宅村',null)
go
insert dbo.person
values(9,'刘咪','女',null,null,'9/04/1960',' 兴户村','军宅村',null)
go
insert dbo.person
values(10,'翟勋涛','男',8,9,'7/09/1982','军宅村','广东村',null)
go
insert dbo.person
values(11,'邢莉娟','女',null,null,'9/18/1983','邢村','广东村',null)
go
insert dbo.person
values(12,'翟涛勇','男',10,11,'8/28/2005','军宅村','军宅村',null)
go
insert dbo.person
values(13,'翟勋钊','男',8,9,'6/28/1983','军宅村','上海市',null)
go
insert dbo.person
values(14,'吴暄兰','女',null,null,'8/28/1984','东川村','上海市',null)
go

insert dbo.person
values(15,'翟钊芬','女',13,14,'9/04/2006',' 军宅村','军宅村',null)
go
insert dbo.person
values(16,'翟大赢','男',2,3,'9/11/1965','军宅村','军宅村',null)
go
insert dbo.person
values(17,'王玲','女',null,null,'9/18/1968','镇上村','军宅村',null)
go
insert dbo.person
values(18,'翟勋波','男',16,17,'8/28/1990','军宅村','‘军宅村',null)
go
insert dbo.person
values(19,'翟勋彤','女',16,17,'8/28/1993','军宅村','‘军宅村',null)

------------建立表 marriage-----------------------
use family
go

create table marriage(
marriageid int not null primary key,
husbandid int not null,
wifeid int not null,
dateofwedding datetime not null,
dateofdivoice datetime null)

-------------插入表MARRIAGE示例数据----------
insert marriage
values(1,2,3,'6/07/1953',null)

go
insert marriage
values(2,4,5,'6/07/1980',null)
go
insert marriage
values(3,8,9,'6/07/1981',null)

go
insert marriage
values(4,16,17,'6/07/1989',null)
go
insert marriage
values(5,10,11,'6/07/2004',null)
go
insert marriage
values(6,13,14,'1/07/2005',null)

总题如下：
1，查出作父母成员共有多少个孩子。
2，查找每个成员在同代中共有多个同性成员(不含自己)
3,输出拥有共同的父母且成员间为同性的成员.
4,输出每个成员父代除你父母以外的成员个数
5,输出每个成员在本代中排名每几位。
6，输出作父母的成员其子女年龄和在同代有子女的同代中排第几名。
7，输出作父母的成员在家族中其子女年龄总在有子女排第几名
希望大家一起来计论。
谢谢大家

...全文