SQL计算列

Question

我正在与SAP HANA一起工作。以下是我的计算视图的一些示例数据。我期望的结果是计算列最后一位老师。

我有2名学生。有一个学生最后一次见过老师的日期。对于每个学生，我想用sql语句计算上次看到的老师。任何想法？

Answer 1

在MySQL，目前不支持窗口的功能，你可以试试这个：

SELECT 
    t1.*, 
    t3.TeacherID AS last_teacher 
FROM yourTable t1 
INNER JOIN 
(
    SELECT studentID, MAX(LastSeen) AS max_last_seen 
    FROM yourTable 
    GROUP BY studentID 
) t2 
    ON t1.studentID = t2.studentID 
INNER JOIN yourTable t3 
    ON t2.studentID  = t3.studentID AND 
     t2.max_last_seen = t3.LastSeen; 

使用窗口功能，我们可以用一个做掉联接和略微简化为以下几点：

SELECT 
    t1.StudentID, 
    t1.TeacherID, 
    t1.LastSeen, 
    t2.TeacherID AS last_teacher 
FROM 
(
    SELECT StudentID, TeacherID, LastSeen, 
     MAX(LastSeen) OVER (PARTITION BY StudentID) AS max_last_seen 
    FROM yourTable 
) t1 
INNER JOIN yourTable t2 
    ON t1.StudentID  = t2.StudentID AND 
     t1.max_last_seen = t2.LastSeen; 

Answer 2

请尝试以下查询。通过为每个studentIdGroup LastSeen列

Select * from 
(
    select 
    ROW_NUMBER() OVER (PARTITION BY studentId ORDER BY LastSeen desc) as rn, 
    studentId, 
    TeacherId 
    from yourTable 
) 
where rn = 1; 

该功能组每studentId和命令行：

SELECT * 
FROM table 
WHERE last_seen IN (
    SELECT Max(last_seen) 
    FROM table 
    GROUP BY student_id 
) 

Answer 3

可以使用Row_Number()功能通过列oracle分贝这样得到LastRecord。然后你可以使用选择每组第一排where语句where rn = 1

UPDATE 如果你想选择表中的所有行，那么你就可以加入你的表这个选择的语句是这样的：

select * from yourTable t 
join 
(
    Select * from 
    (
    select 
     ROW_NUMBER() OVER (PARTITION BY studentId ORDER BY LastSeen desc) as rn, 
     studentId, 
     TeacherId 
    from yourTable 
) 
    where rn = 1 
) s 
on t.studentId = s.studentId;