求多行子表信息转成主表信息的列

AjaxExec 中国地质大学软件工程师 2018-08-16 03:18:52

主表简历表resumeGid,子表教育经历表，子表中每个人对应4条教育经历，需要将教育经历的列变成主表的列，建表语句如下

CREATE TABLE [dbo].[ResumeEducationHistory](

	[EduGID] [uniqueidentifier] NOT NULL,

	[ResumeGID] [uniqueidentifier] NOT NULL,

	[CollegeCode] [varchar](100) NULL,

	[CollegeName] [nvarchar](200) NULL,

	[Faculty] [nvarchar](100) NULL,

	[MajorCode] [varchar](100) NULL,

	[MajorName] [nvarchar](200) NULL,

	[StartDate] [date] NULL,

	[EndDate] [date] NULL,

	[IsHitherto] [bit] NULL,

	[EducationLevelCode] [varchar](100) NULL,

	[EducationLevelName] [nvarchar](200) NULL,

	[DegreeCode] [varchar](100) NULL,

	[DegreeName] [nvarchar](200) NULL,

	[EducationalSystem] [nvarchar](50) NULL,

	[StudyMode] [nvarchar](50) NULL,

	[TrainingStyle] [nvarchar](50) NULL,

	[GPA] [decimal](18, 2) NULL,

	[MajorRanking] [nvarchar](50) NULL,

	[MajorRemark] [nvarchar](1000) NULL,

	[SequenceNum] [int] NOT NULL,

 CONSTRAINT [PK__ResumeEd__D403ABC3A3208640] PRIMARY KEY CLUSTERED 

(

	[EduGID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[ResumeEducationHistory]  WITH CHECK ADD  CONSTRAINT [FK_ResumeEducationHistory_Resume] FOREIGN KEY([ResumeGID])

REFERENCES [dbo].[Resume] ([ResumeGID])

GO



ALTER TABLE [dbo].[ResumeEducationHistory] CHECK CONSTRAINT [FK_ResumeEducationHistory_Resume]

GO



 

CREATE TABLE [dbo].[Resume](

	[ResumeGID] [uniqueidentifier] NOT NULL,

	[Name] [nvarchar](100) NOT NULL,

	 

 CONSTRAINT [PK_RESUME] PRIMARY KEY CLUSTERED 

(

	[ResumeGID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

...全文

92 点赞收藏 2

写回复

2 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

RINK_1 2018-08-16


select A.ResumeGID,A.Name,
max(case when rn=1 then StartDate else null end) as StartDate_1,
max(case when rn=1 then EndDate else null end) as EndDate_1,
max(case when rn=1 then CollegeName else null end) as CollegeName_1,
max(case when rn=1 then DegreeName else null end) as DegreeName_1,
max(case when rn=1 then Faculty else null end) as Faculty_1,
max(case when rn=1 then MajorName else null end) as MajorName_1,
max(case when rn=1 then MajorRanking else null end) as MajorRanking_1,
max(case when rn=2 then StartDate else null end) as StartDate_2,
max(case when rn=2 then EndDate else null end) as EndDate_2,
max(case when rn=2 then CollegeName else null end) as CollegeName_2,
max(case when rn=2 then DegreeName else null end) as DegreeName_2,
max(case when rn=2 then Faculty else null end) as Faculty_2,
max(case when rn=2 then MajorName else null end) as MajorName_2,
max(case when rn=2 then MajorRanking else null end) as MajorRanking_2,
max(case when rn=3 then StartDate else null end) as StartDate_3,
max(case when rn=3 then EndDate else null end) as EndDate_3,
max(case when rn=3 then CollegeName else null end) as CollegeName_3,
max(case when rn=3 then DegreeName else null end) as DegreeName_3,
max(case when rn=3 then Faculty else null end) as Faculty_3,
max(case when rn=3 then MajorName else null end) as MajorName_3,
max(case when rn=3 then MajorRanking else null end) as MajorRanking_3,
max(case when rn=4 then StartDate else null end) as StartDate_4,
max(case when rn=4 then EndDate else null end) as EndDate_4,
max(case when rn=4 then CollegeName else null end) as CollegeName_4,
max(case when rn=4 then DegreeName else null end) as DegreeName_4,
max(case when rn=4 then Faculty else null end) as Faculty_4,
max(case when rn=4 then MajorName else null end) as MajorName_2,
max(case when rn=4 then MajorRanking else null end) as MajorRanking_2,
from Resume A
left join 
(select *,row_number() over (partition by ResumeGID order by StartDate) as rn from ResumeEducationHistory) as B on A.ResumeGID=B.ResumeGID
group by A.ResumeGID,A.Name

回复赞