今天遇到一个问题,要表格里面的内容合并。
原始数据
这个一个学习和测试的记录,Type是类型(0学习,1测试)。一天中可能会学习多次,也可能会测试多次,学习次数和测试次数可能不一样。
想要的到得是,按日期列出当天学习和测试的记录。
类似这样的结果:(图中两行数据一样,是两种语言表示)
大致的SQL语句是
select A.Date,A.MID,A.Contents1,B.Contents2,B.Passed from
(select ROW_NUMBER() over(partition by Date order by Date) as MID,Date,Contents as Contents1 from History where Type=0 ) A
left join
(select ROW_NUMBER() over(partition by Date order by Date) as MID,Date,Contents as Contents2,Passed from History where Type=1 ) B
on A.Date=B.Date and A.MID=B.MID
union
select B.Date,B.MID, A.Contents1,B.Contents2,B.Passed from
(select ROW_NUMBER() over(partition by Date order by Date) as MID,Date,Contents as Contents1 from History where Type=0 ) A
right join
(select ROW_NUMBER() over(partition by Date order by Date) as MID,Date,Contents as Contents2,Passed from History where Type=1) B
on A.Date=B.Date and A.MID=B.MID
结果如下:
至此,主要问题解决。