sql - 我需要在 sql 中做一个 View ，返回每个公司的最新发票日期

Question

我有一个公司表是 dbo.companies并有 companyId作为专栏。我还有一个发票表是 dbo.invoices与 invoicecompanyId列(与另一个表上的 companyId 相同)并且它还有一个名为 invoicedate 的列。 .
我想要实现的是每个 View companyid与我拥有的所有公司相应的最新发票日期。

我已完成以下操作，但我不知道如何筛选最新发票，它返回所有公司的所有发票，我需要所有公司的最新发票

SELECT     TOP (100) PERCENT 
    'A' + 
    SUBSTRING('000000', 1, 6 - LEN(CAST(dbo.companies.companyId AS varchar(10)))) +
    CAST(dbo.companies.companyId AS varchar(10)) AS Client_ID, 
    dbo.invoices.invoiceDate AS S_Inv_Date
FROM dbo.invoices 
INNER JOIN dbo.companies 
ON dbo.invoices.invoiceCompanyId = dbo.companies.companyId
ORDER BY Client_ID

你能帮忙吗？

塔

Answer 1

SELECT 
       SUBSTRING('000000', 1, 6 - LEN(CAST(dbo.companies.companyId AS varchar(10)))) +
           CAST(dbo.companies.companyId AS varchar(10)) AS Client_ID
     , b.mxDate maxInvoiceDate
  FROM dbo.companies c
     , (SELECT dbo.invoices.invoiceCompanyId companyId
             , MAX(dbo.invoices.invoiceDate) mxDate
          FROM dbo.invoices
        GROUP BY dbo.invoices.invoiceCompanyId ) b
 WHERE c.companyId = b.companyId
 ORDER BY 1