基于Northwind数据库创建发票表格视图的技术问题咨询

我看你已经搭好了查询的基础框架，针对旧版Northwind数据库的发票信息提取需求，我帮你补全并优化了查询语句，确保覆盖所有核心发票字段：

SELECT 
    o.OrderID,
    o.CustomerID,
    c.CompanyName AS CustomerName,
    c.Address AS CustomerAddress,
    c.City AS CustomerCity,
    c.PostalCode AS CustomerPostalCode,
    c.CountryID AS CustomerCountryID,
    CONCAT(e.FirstName, ' ', e.LastName) AS SalespersonName,
    s.CompanyName AS ShippingCompany,
    od.ProductID,
    p.ProductName,
    od.Quantity,
    od.UnitPrice,
    od.Discount,
    -- 计算折扣后的单行商品总价，保留两位小数符合财务规范
    ROUND(od.UnitPrice * od.Quantity * (1 - od.Discount), 2) AS ExtendedPrice,
    -- 新增整单合计金额，方便快速查看订单总价值
    (SELECT ROUND(SUM(od2.UnitPrice * od2.Quantity * (1 - od2.Discount)), 2) 
     FROM [Order Details] od2 
     WHERE od2.OrderID = o.OrderID) AS OrderTotal,
    o.OrderDate,
    o.ShippedDate,
    o.Freight
FROM 
    Orders o
JOIN 
    Customers c ON o.CustomerID = c.CustomerID
JOIN 
    Employees e ON o.EmployeeID = e.EmployeeID
JOIN 
    Shippers s ON o.ShipVia = s.ShipperID
JOIN 
    [Order Details] od ON o.OrderID = od.OrderID
JOIN 
    Products p ON od.ProductID = p.ProductID
ORDER BY 
    o.OrderID, od.ProductID;

关键优化说明： #

• 补全了所有必要表关联：通过Orders表串联客户、销售代表、承运商、商品明细等所有发票相关数据源
• 新增了UnitPrice、Discount字段，让发票明细的计算逻辑更透明
• 用ROUND()函数格式化金额结果，符合财务数据的展示要求
• 增加了整单合计字段OrderTotal，无需额外计算就能看到订单总金额
• 补充了OrderDate、ShippedDate、Freight这类发票必备的时间和运费信息
• 最后按订单ID和商品ID排序，让结果条理更清晰

如果你的发票还有特定字段需求（比如客户联系电话、商品类别等），可以随时调整查询逻辑~

内容的提问来源于stack exchange，提问作者DoktorAgon