一对多的表关系 查询出一对一的展示

一对多的表关系 查询出一对一的显示

创建2张表  Table_A 和 Table_B  关系是一对多,   表Table_A.Id = Table_B.aId

CREATE TABLE [dbo].[Table_A](
 [Id] [char](36) NULL,
 [column1] [nvarchar](50) NULL,
 [column2] [nvarchar](50) NULL
) ON [PRIMARY]

 

CREATE TABLE [dbo].[Table_B](
 [Id] [char](36) NULL,
 [aId] [char](36) NULL,
 [column1] [nvarchar](50) NULL,
 [column2] [nvarchar](50) NULL,
 [columnDatetime] [datetime] NULL
) ON [PRIMARY]

 

插入一些测试数据

INSERT Table_A (id,column1,column2) VALUES (NEWID(),'table1_row1_1','table1_row1_2')
INSERT Table_A (id,column1,column2) VALUES (NEWID(),'table1_row2_1','table1_row2_2')
INSERT Table_A (id,column1,column2) VALUES (NEWID(),'table1_row3_1','table1_row3_2')
 
INSERT Table_B (id,aId,column1,column2,columnDatetime) VALUES (NEWID(),'A75A6798-9C34-4FA2-AB23-AA146704A71C','table2_row1_1','table2_row1_2','2014-12-22')
INSERT Table_B (id,aId,column1,column2,columnDatetime) VALUES (NEWID(),'A75A6798-9C34-4FA2-AB23-AA146704A71C','table2_row2_1','table2_row2_2','2014-12-23')
INSERT Table_B (id,aId,column1,column2,columnDatetime) VALUES (NEWID(),'A75A6798-9C34-4FA2-AB23-AA146704A71C','table2_row3_1','table2_row3_2','2014-12-24')
INSERT Table_B (id,aId,column1,column2,columnDatetime) VALUES (NEWID(),'FA5543B4-9EF0-4DD1-8801-76F12633223E','table2_row4_1','table2_row4_2','2014-12-22')
INSERT Table_B (id,aId,column1,column2,columnDatetime) VALUES (NEWID(),'FA5543B4-9EF0-4DD1-8801-76F12633223E','table2_row5_1','table2_row5_2','2014-12-23')

我这边第一条生成的ID是 'A75A6798-9C34-4FA2-AB23-AA146704A71C'

SELECT * FROM  dbo.Table_A
LEFT JOIN Table_B ON Table_B.aId = dbo.Table_A.Id
WHERE Table_B.aId = 'A75A6798-9C34-4FA2-AB23-AA146704A71C'

 

我这边只取时间晚的一条作为显示

SELECT * FROM  dbo.Table_A
OUTER APPLY (SELECT TOP 1 * FROM Table_B WHERE Table_B.aId = dbo.Table_A.Id ORDER BY columnDatetime DESC) Table_B
WHERE Table_B.aId = 'A75A6798-9C34-4FA2-AB23-AA146704A71C'

以下内容摘自博客园夏雪光转载请注明出处：http://www.cnblogs.com/leoxjy/p/4184457.html