请问一个左连接的有关问题
请教一个左连接的问题
表students:
CREATE TABLE [dbo].[Students](
[StudentsOID] [uniqueidentifier] NOT NULL,
[SYS_Created] [datetime] NULL,
[SYS_LAST_UPD] [datetime] NULL,
[SYS_Deleted] [bit] NULL,
[StuNo] [varchar](50) NULL,
[StuName] [varchar](50) NULL,
[StuSex] [varchar](50) NULL,
[CellPhoneNumber] [varchar](50) NULL,
[PhoneNumber] [varchar](50) NULL,
[QQ] [varchar](50) NULL,
[PracticeType] [varchar](50) NULL,
[Trained] [bit] NULL,
[TrainingInstitution] [varchar](50) NULL,
[Note] [varchar](50) NULL,
[Tutor] [varchar](50) NULL,
[Classes] [varchar](50) NULL,
[SYS_CreatedBy] [uniqueidentifier] NULL,
[SYS_REPLACEMENT] [uniqueidentifier] NULL,
[SYS_POSTN] [uniqueidentifier] NULL,
[SYS_DIVISION] [uniqueidentifier] NULL,
[SYS_ORG] [uniqueidentifier] NULL,
[SYS_LAST_UPD_BY] [uniqueidentifier] NULL,
[Teachers_FK] [uniqueidentifier] NULL,
[Classes_FK] [uniqueidentifier] NULL
) ON [PRIMARY]
表Internships:
CREATE TABLE [dbo].[Internships](
[InternshipsOID] [uniqueidentifier] NOT NULL,
[InternshipsEnterprise] [varchar](50) NULL,
[EnterprisesAddress] [varchar](500) NULL,
[EnterprisesLocation] [varchar](50) NULL,
[EnterprisesLeader] [varchar](50) NULL,
[EnterprisesContact] [varchar](50) NULL,
[InternshipsPost] [varchar](50) NULL,
[InternshipsPostType] [varchar](50) NULL,
[Salary] [money] NULL,
[OnBoardDate] [datetime] NULL,
[Students_FK] [uniqueidentifier] NULL
) ON [PRIMARY]
两个表通过StudentsOID=Students_FK关联,students与Internships的对应关系是1:n
现在想做个视图,显示students中所有的数据+Internships表中对应的最新1条数据(OnBoardDate倒序排)
我现在是这样写的:
觉得很笨,效率也很低,各位大侠有没有更好的办法?
------解决方案--------------------
表students:
CREATE TABLE [dbo].[Students](
[StudentsOID] [uniqueidentifier] NOT NULL,
[SYS_Created] [datetime] NULL,
[SYS_LAST_UPD] [datetime] NULL,
[SYS_Deleted] [bit] NULL,
[StuNo] [varchar](50) NULL,
[StuName] [varchar](50) NULL,
[StuSex] [varchar](50) NULL,
[CellPhoneNumber] [varchar](50) NULL,
[PhoneNumber] [varchar](50) NULL,
[QQ] [varchar](50) NULL,
[PracticeType] [varchar](50) NULL,
[Trained] [bit] NULL,
[TrainingInstitution] [varchar](50) NULL,
[Note] [varchar](50) NULL,
[Tutor] [varchar](50) NULL,
[Classes] [varchar](50) NULL,
[SYS_CreatedBy] [uniqueidentifier] NULL,
[SYS_REPLACEMENT] [uniqueidentifier] NULL,
[SYS_POSTN] [uniqueidentifier] NULL,
[SYS_DIVISION] [uniqueidentifier] NULL,
[SYS_ORG] [uniqueidentifier] NULL,
[SYS_LAST_UPD_BY] [uniqueidentifier] NULL,
[Teachers_FK] [uniqueidentifier] NULL,
[Classes_FK] [uniqueidentifier] NULL
) ON [PRIMARY]
表Internships:
CREATE TABLE [dbo].[Internships](
[InternshipsOID] [uniqueidentifier] NOT NULL,
[InternshipsEnterprise] [varchar](50) NULL,
[EnterprisesAddress] [varchar](500) NULL,
[EnterprisesLocation] [varchar](50) NULL,
[EnterprisesLeader] [varchar](50) NULL,
[EnterprisesContact] [varchar](50) NULL,
[InternshipsPost] [varchar](50) NULL,
[InternshipsPostType] [varchar](50) NULL,
[Salary] [money] NULL,
[OnBoardDate] [datetime] NULL,
[Students_FK] [uniqueidentifier] NULL
) ON [PRIMARY]
两个表通过StudentsOID=Students_FK关联,students与Internships的对应关系是1:n
现在想做个视图,显示students中所有的数据+Internships表中对应的最新1条数据(OnBoardDate倒序排)
我现在是这样写的:
- SQL code
SELECT StuNo, StuName, StuSex, CellPhoneNumber, StudentsOID, PhoneNumber, QQ, PracticeType, Trained, TrainingInstitution, Note, Tutor, Classes, (SELECT TOP (1) InternshipsEnterprise FROM dbo.Internships WHERE (Students_FK = dbo.Students.StudentsOID) ORDER BY OnBoardDate DESC) AS 实习单位, (SELECT TOP (1) EnterprisesAddress FROM dbo.Internships AS Internships_1 WHERE (Students_FK = dbo.Students.StudentsOID) ORDER BY OnBoardDate DESC) AS 单位地址 FROM dbo.Students
觉得很笨,效率也很低,各位大侠有没有更好的办法?
------解决方案--------------------