我有一个 SQL 问题,典型的:上一行,下一行问题但是:不使用 rownum、lead 或 rankover 这些函数,只选择和加入,
桌子: Student
领域: Student_ID, Department, Start_Date
前任:
1,C, 2017-01-1
1,B, 2017-07-1
1,A, 2017-12-1
预期输出:
Student_ID, Department, Start_Date, End_Date
前任:
1,C, 2017-01-1, 2017-07-01
1,B, 2017-07-1,2017-12-01
1,A, 2017-12-1, ...
End_Date 是学生 ID 下一条记录的开始日期
你可以试试这个:
数据
create table student (
Student_ID int,
Department char(1),
Start_Date date
);
insert into student values (1, 'A', '2017-01-01');
insert into student values (1, 'B', '2017-01-01');
insert into student values (1, 'C', '2017-12-31');
数据库服务器
select
student_id,
department,
start_date,
(select top 1 start_date
from student
where student_id = s.student_id
) as end_date
from student s
order by student_id, department;
示例:http : //rextester.com/HLL58959
PostgreSQL 和 MySQL 和 SQLite
select
student_id,
department,
start_date,
(select start_date
from student
where student_id = s.student_id
limit 1) as end_date
from student s
order by student_id, department;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句