MySQL 视图不能在 from 中包含子查询，并且不维护顺序。如何根据该查询创建视图？

Question

我本质上是想获得每个员工当前头衔的结果集。我想从中创建一个视图以供以后使用，但我发现我被难住了，并且可能缺少一个简单的解决方案。这是有问题的查询，提前致谢！

select * from 
(SELECT
appointment.employee_id,
title.`name` as title_name
FROM
appointment
INNER JOIN appointment_title ON appointment.id = appointment_title.appointment_id
INNER JOIN title ON appointment_title.title_id = title.id
order by appointment_title.effective_date DESC) tmp group by employee_id

Answer 1

更新：

SELECT
    appointment.employee_id ,
    ( SELECT title.`name`
      FROM appointment_title
        INNER JOIN title
          ON appointment_title.title_id = title.id
      WHERE appointment.id = appointment_title.appointment_id
      ORDER BY appointment_title.effective_date DESC
      LIMIT 1
    ) AS title_name
FROM appointment
GROUP BY appointment.employee_id

Answer 2

另一种选择是将查询分解为两个视图。第一个视图将包含派生表子查询，第二个视图将简单地从该子查询中进行选择：

CREATE VIEW vwEmployee_Inner AS
SELECT
appointment.employee_id,
title.`name` as title_name
FROM
appointment
INNER JOIN appointment_title ON appointment.id = appointment_title.appointment_id
INNER JOIN title ON appointment_title.title_id = title.id
order by appointment_title.effective_date DESC

然后您的原始视图将变为：

CREATE VIEW vwEmployee AS
SELECT * FROM vwEmployee_Inner GROUP BY employee_id