现有两张表,分别为babies和family。babies表中包含如下字段:Id(int,主键,自增),Name(varchar(50)),Birthday(datatime),Sex(nvarchar(1))。family表中包含如下字段:Id(int,主键,自增),Relation(varchar(10)),Name(varchar(50)),Birthday(datatime),BabyId(int)。
要求编写SQL脚本,返回以下结果集:BabyId ,BabyName ,FatherName,Birthday,Sex,MotherName,按babies表的主键升序排列。
要求编写SQL脚本,返回以下结果集:BabyId ,BabyName ,FatherName,Birthday,Sex,MotherName,按babies表的主键升序排列。
from babies a left join family b on a.id=b.BabyId and b.Relation='Father'
left join family c on a.id=c.BabyId and c.Relation='Mother'
order by a.Id
应该是Relation这个字段。
我把代码拿回去看报错了,其实主要是C,a指的是babies表,b指的应该是family表吧,那c又指的是什么啊?
family表中的字段Relation两个值分别是‘父亲’和‘母亲’,通过这个来判断是FatherName还是MotherName,最后在一个查询结果之中显示出来。
SELECT 'Jim',GETDATE(),'M'
UNION ALL
SELECT 'Kate',GETDATE(),'F'CREATE TABLE family (Id INT IDENTITY ,Relation varchar(10),Name varchar(50),Birthday datetime,BabyId int)INSERT INTO family
SELECT 'Father','Green',getdate(),1
UNION ALL
SELECT 'Mother','Lily',GETDATE(),1
SELECT f.*,ma.NAME AS 'MotherName'
FROM
(SELECT ba.Id ,ba.Name ,fa.[Name] AS 'FotherName',fa.Birthday,Sex
FROM babies ba
LEFT JOIN family fa
ON ba.ID=fa.BabyId
WHERE fa.Relation='father') F
left JOIN family ma
ON f.Id=ma.BabyId
WHERE ma.Relation='mother'
ORDER BY f.Id ASCId Name FotherName Birthday Sex MotherName
----------- -------------------------------------------------- -------------------------------------------------- ----------------------- ---- --------------------------------------------------
1 Jim Green 2010-08-25 08:32:23.987 M Lily(1 row(s) affected)