家庭表
FamilyId
HusbandId
WifeID
FatherId
MotherId
Child1Id
Child2Id
个人表
PersonId
Name
Age
Sex
FamilyId家庭表中的HusbandId,WifeID,FatherId,MotherId,Child1Id,Child2Id都是PersonId
请问如何在查询获得HusbandName,WifeName,FatherName,MotherName,Child1Name,Child2Name?
FamilyId
HusbandId
WifeID
FatherId
MotherId
Child1Id
Child2Id
个人表
PersonId
Name
Age
Sex
FamilyId家庭表中的HusbandId,WifeID,FatherId,MotherId,Child1Id,Child2Id都是PersonId
请问如何在查询获得HusbandName,WifeName,FatherName,MotherName,Child1Name,Child2Name?
解决方案 »
- 请教一个sql语句的写法
- 请问在哪可以下载SQL 2000 server下载?要怎么安装才不会出错
- SQL 更新
- 求助:sql中有没有办法判断字符串中含有几个字节?len中对汉字只是认为长度1,不是2
- 怎样求得两个字段差的总和啊?
- 在线求SQL语句。50分。。。
- 设计成为计算列出现问题了。
- 安装SqlServer2005 安装写至文件Microsoft.SQLServer.MonitorFrameWork.DLL时出错
- 我在WIN2K上面安装了ORACLE8i,运行SQLPLUS,可是出现ORA-12560错误,怎么回事?
- 急急急!!!分分分!!!SQL语句挑战?
- 分组查询问题
- 求一条SQL语句
a.FamilyId,
b1.name as HusbandName,
b2.name as WifeName,
b3.name as FatherName,
b4.name as MotherName,
b5.name as Child1Name,
b6.name as Child2Name
from 家庭表 a
left join 个人表 b1
on a.HusbandId = b1.PersonId
left join 个人表 b2
on a.WifeID = b2.PersonId
left join 个人表 b3
on a.FatherId = b3.PersonId
left join 个人表 b4
on a.MotherId = b4.PersonId
left join 个人表 b5
on a.Child1Id = b5.PersonId
left join 个人表 b6
on a.Child2Id = b6.PersonId
C.NAME AS WifeName,
D.NAME AS FatherName,
E.NAME AS MotherName
F.NAME AS Child1Name,
G.NAME AS Child2Name
FROM 家庭表 A
LEFT JOIN 个人表 B ON A.HusbandId=B.PersonId
LEFT JOIN 个人表 C ON A.WifeID=C.PersonId
LEFT JOIN 个人表 D ON A.FatherId=D.PersonId
LEFT JOIN 个人表 E ON A.MotherId=E.PersonId
LEFT JOIN 个人表 F ON A.Child1Id=F.PersonId
LEFT JOIN 个人表 G ON A.Child2Id=G.PersonId
DROP FUNCTION [fn_test]
GOCREATE FUNCTION [fn_test]
(
@PersonId INT
)
RETURNS NVARCHAR(50)
AS
BEGIN
RETURN(
SELECT Name
FROM 个人表
WHERE PersonId = @PersonId
)
END
GO
DROP FUNCTION [fn_test]
GOCREATE FUNCTION [fn_test]
(
@PersonId INT
)
RETURNS NVARCHAR(50)
AS
BEGIN
RETURN(
SELECT Name
FROM 个人表
WHERE PersonId = @PersonId
)
END
GO
--查询
SELECT dbo.fn_test(HusbandId) HusbandName, dbo.fn_test(WifeID) WifeName, dbo.fn_test(FatherId) FatherName,
dbo.fn_test(MotherId) MotherName, dbo.fn_test(Child1Id) Child1Name, dbo.fn_test(Child2Id) Child2Name
FROM 家庭表
HusbandName = (select name from 个人表 where PersonId = m.HusbandId and FamilyId = m.FamilyId),
WifeName = (select name from 个人表 where PersonId = m.WifeID and FamilyId = m.FamilyId),
FatherName = (select name from 个人表 where PersonId = m.FatherId and FamilyId = m.FamilyId),
MotherName = (select name from 个人表 where PersonId = m.MotherId and FamilyId = m.FamilyId),
Child1Name = (select name from 个人表 where PersonId = m.Child1Id and FamilyId = m.FamilyId),
Child2Name = (select name from 个人表 where PersonId = m.Child2Id and FamilyId = m.FamilyId)
from 家庭表 m