mysql - 如何找到我在第一个 View 中创建的每个供应商的订单时间列的平均值

Question

Results from first view

My second view which gives me an error

我收到错误代码:1054，“on 子句”中的未知列“V1.V_Name”

DROP VIEW IF EXISTS vendor_order_times;

CREATE VIEW vendor_order_times AS
 SELECT
    v.V_NAME as 'Vendor Name',
    DATEDIFF(o.O_ARR_DATE, o.O_DATE) as ordertime
    FROM vendor v 
      JOIN product p ON v.V_ID = p.V_ID
      JOIN line l ON p.P_ID = l.P_ID
      JOIN orders o ON l.O_ID = o.O_ID
    GROUP BY o.O_ID
    ORDER BY v.V_ID;

DROP VIEW IF EXISTS vendor_shipping_stats;

CREATE VIEW vendor_shipping_stats AS
  SELECT 
    v.V_NAME as 'Vendor Name', 
    ROUND(AVG(vot.ordertime),1) as 'Vendor: Avg Ship Times',
    QueryAuthor() as 'Query Author'
  FROM vendor v
    JOIN vendor_order_times as vot on vot.V_NAME = v.V_NAME 
  GROUP BY v.V_NAME
  ORDER BY `Vendor: Avg Ship Times` DESC;

Answer 1

在 vendor_order_times View 中，您将 V_NAME 字段重命名为 'Vendor Name'，因此您需要在第二个中使用此名称 View ，或者您需要删除 vendor_order_times View 中的别名。