sql - 从 SQL Server 连接中获取最大值

Question

我在 SQL Server 查询上挣扎了一段时间，并意识到我必须从比我更了解 SQL Server 的人那里获得帮助。

环境:SQL Server 2008

SELECT      
    t1.SUPPL_ORDERNO, 
    t2.OUR_ORDER,
    t3.CUST_INVOICE
FROM t1 

    LEFT OUTER JOIN t2 
        ON t1.SUPPL_ORDERNO = t2.ORDER_REM5 

    LEFT OUTER JOIN t3
        ON t2.ORDER_REM5  = t3.INV_REM5

GROUP BY t1.SUPPL_ORDERNO, t2.OUR_ORDER, t3.CUST_INVOICE
ORDER BY t1.SUPPL_ORDERNO,t3.CUST_INVOICE

这是我通过上面的查询得到的结果:

SUPPL_ORDERNO                  OUR_ORDER CUST_INVOICE
------------------------------ --------- ------------
143914                         64228     179890
143914                         64228     179935
143914                         64228     179995
143914                         64228     179999
143914                         64228     180067
143914                         64228     180148
326042                                   
326052                         64549     180219
326086                         64633  

我得到多个 CUST_INVOICE每个 SUPPL_ORDERNO和 OUR_ORDER组合但我只想返回最新的 CUST_INVOICE (=最大发票号)。

这是我想要返回的结果:

SUPPL_ORDERNO                  OUR_ORDER CUST_INVOICE
------------------------------ --------- ------------
143914                         64228     180148
326042                                   
326052                         64549     180219
326086                         64633   

总结:

如果有不止一 (1) 个 CUST_INVOICE为SUPPL_ORDERNO的组合和 OUR_ORDER我只想显示 CUST_INVOICE数量最多的

如果没有 OUR_ORDER没有 CUST_INVOICE我希望它显示为空

如果有 OUR_ORDER没有 CUST_INVOICE我希望它显示为空

任何帮助将不胜感激!

Answer 1

从 GROUP BY 中删除 t3.CUST_INVOICE 并在 SELECT 语句中使用 MAX(t3.CUST_INVOICE) 函数

SELECT t1.SUPPL_ORDERNO, t2.OUR_ORDER, MAX(t3.CUST_INVOICE) AS CUST_INVOICE
FROM t1 LEFT JOIN t2 ON t1.SUPPL_ORDERNO = t2.ORDER_REM5 
        LEFT JOIN t3 ON t2.ORDER_REM5  = t3.INV_REM5
GROUP BY t1.SUPPL_ORDERNO, t2.OUR_ORDER
ORDER BY t1.SUPPL_ORDERNO, t3.CUST_INVOICE

SQLFiddle 上的简单示例