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

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
回复
卖水果的net 2018-08-16
测试数据也给出一些来。
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2018-08-16 03:18
社区公告
暂无公告