sql - 从连接行中选择不同的字段

Question

我有一个表 ANIMAL 和另一个表 CALF_PARENT

ANIMAL表的设计是:

ID PK IDENTITY(1,1),
TagNo varchar(30)

and other columns...

CALF_PARENT 的设计是:

ID PK IDENTITY(1,1),
Parent int (FK from Animal),
IsMother varchar(1)

我正在编写以下查询来连接两个表:

SELECT a.[TagNo]
  ,a.ID   
 ,a2.TagNo
,isnull(cp.Parent,0)   
 ,a3.TagNo 
,isnull(cp.Parent,0)    

FROM [dbo].[ANIMAL] a

  LEFT JOIN [CALF_PARENT] cp
  ON a.ID = cp.Calf

  LEFT JOIN ANIMAL a2
  ON a2.ID = cp.Parent AND cp.IsMother = 'Y' AND a2.ID IS NOT NULL

  LEFT JOIN ANIMAL a3
  ON a3.ID = cp.Parent AND cp.IsMother = 'N' AND a3.ID IS NOT NULL

它返回给我的记录是这样的:

Tag, CalfID, FatherTagNo, FatherID, MotherTagNo, MotherID

FA-56   21   AB-670         3          
FA-56   21                            CW-59         7   

我希望它返回我这样的单行:

Tag, CalfID, FatherTagNo, FatherID, MotherTagNo, MotherID

FA-56   21   AB-670         3          CW-59         7  

实现它的最简单方法是什么。

Answer 1

SELECT DISTINCT a.[TagNo] As Tag
  ,a.ID  As CalfID  
 ,MAX(a2.TagNo) As FatherTagNo
,MAX(isnull(cp.Parent,0))   As FatherID
 ,MAX(a3.TagNo)  As MotherTagNo
,MAX(isnull(cp.Parent,0))As MotherID    

FROM [dbo].[ANIMAL] a

  LEFT JOIN [CALF_PARENT] cp
  ON a.ID = cp.Calf

  LEFT JOIN ANIMAL a2
  ON a2.ID = cp.Parent AND cp.IsMother = 'Y' AND a2.ID IS NOT NULL

  LEFT JOIN ANIMAL a3
  ON a3.ID = cp.Parent AND cp.IsMother = 'N' AND a3.ID IS NOT NULL

  GROUP BY a.[TagNo] ,a.ID

并举例

declare @t table (id varchar(10),cal int,father varchar(10),fatherid int,mother varchar(20),motherid int)

insert into @t(id,cal,father,fatherid,mother,motherid) values ('FA-56',21,'AB-670',3,NULL,NULL)
insert into @t(id,cal ,father,fatherid,mother,motherid) values ('FA-56',21,null,null, 'CW-59',21)

select distinct id As i,cal,MAX(father),MAX(fatherid),MAX(mother),MAX(motherid) from @t
group by  id,cal