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

创建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_ALEFT JOIN Table_B ON Table_B.aId = dbo.Table_A.IdWHERE Table_B.aId = 'A75A6798-9C34-4FA2-AB23-AA146704A71C'

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

SELECT * FROM dbo.Table_AOUTER 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