sql查询问题，求大神帮忙，在线等

[laodeng0710]

创建了一个视图进行多表查询，如下
SELECT t.SalesInvoiceCode AS ERP发票号, t.BillDate AS 开票日期, t.BillToParty AS 客户代码, t.BillToName AS 客户名称,
t.PostYear AS 年, t.PostPeriod AS 月, t.TaxControlCode AS 金税发票号, t.TotalValue AS 发票金额,
t.TotalValue - t.ReceiptValue AS 未回款金额, t.DepartmentID AS 部门编码, t2.LSBMZD_BMMC AS 部门名称,
t4.LSZGZD_ZGBH AS 职工编号, t4.LSZGZD_ZGXM AS 职工姓名, t1.CreditBaseDate AS 信贷开始日,
t1.CreditDays AS 信贷天数, t1.CreditEndDate AS 信贷到期日
FROM LC0019999.SalesInvoices AS t INNER JOIN
(SELECT DISTINCT SalesInvoiceID, CreditBaseDate, CreditDays, CreditEndDate
FROM LC0019999.SalesInvoiceItems
WHERE (ReceiptFlag < 2)) AS t1 ON t.SalesInvoiceID = t1.SalesInvoiceID INNER JOIN
LC0019999.LSBMZD AS t2 ON t.DepartmentID = t2.LSBMZD_BMBH INNER JOIN
LC0019999.SalesCustomers AS t3 ON t.SalesOrgID = t3.SalesOrgID AND t.DisChannelID = t3.DisChannelID AND
t.DivisionID = t3.DivisionID AND t.BillToParty = t3.PartnerID INNER JOIN
LC0019999.LSZGZD AS t4 ON t3.EmployeeID = t4.LSZGZD_ZGBH
那么问题来了，现在想做一个多条件筛选，希望当ERP发票号相同的情况下，只显示t1.CreditBaseDate AS 信贷开始日列的最大日期，求大神帮帮忙啊，在线等

[中国风]

这样？

SELECT  t.SalesInvoiceCode AS ERP发票号
       ,t.BillDate AS 开票日期
       ,t.BillToParty AS 客户代码
       ,t.BillToName AS 客户名称
       ,t.PostYear AS 年
       ,t.PostPeriod AS 月
       ,t.TaxControlCode AS 金税发票号
       ,t.TotalValue AS 发票金额
       ,t.TotalValue - t.ReceiptValue AS 未回款金额
       ,t.DepartmentID AS 部门编码
       ,t2.LSBMZD_BMMC AS 部门名称
       ,t4.LSZGZD_ZGBH AS 职工编号
       ,t4.LSZGZD_ZGXM AS 职工姓名
       ,MAX(t1.CreditBaseDate)OVER(PARTITION BY t.SalesInvoiceCode) AS 信贷开始日
       ,t1.CreditDays AS 信贷天数
       ,t1.CreditEndDate AS 信贷到期日
FROM    LC0019999.SalesInvoices AS t
        INNER JOIN ( SELECT DISTINCT
                            SalesInvoiceID
                           ,CreditBaseDate
                           ,CreditDays
                           ,CreditEndDate
                     FROM   LC0019999.SalesInvoiceItems
                     WHERE  ( ReceiptFlag < 2 )
                   ) AS t1 ON t.SalesInvoiceID = t1.SalesInvoiceID
        INNER JOIN LC0019999.LSBMZD AS t2 ON t.DepartmentID = t2.LSBMZD_BMBH
        INNER JOIN LC0019999.SalesCustomers AS t3 ON t.SalesOrgID = t3.SalesOrgID
                                                     AND t.DisChannelID = t3.DisChannelID
                                                     AND t.DivisionID = t3.DivisionID
                                                     AND t.BillToParty = t3.PartnerID
        INNER JOIN LC0019999.LSZGZD AS t4 ON t3.EmployeeID = t4.LSZGZD_ZGBH;