SQL关联查询问题，左链接，MAX

[qq_40307897]

USE imos
SELECT * FROM 
IDBGPL LEFT JOIN IDBGRPS ON IDBGPL.ORDERID=IDBGRPS.ORDERID AND IDBGPL.ID=IDBGRPS.ID
       LEFT JOIN IDBINFO ON IDBGPL.ORDERID=IDBINFO.ORDERID AND IDBGRPS.HIGHARTID=IDBINFO.ID
       LEFT JOIN MAT     ON IDBGPL.MATNAME=MAT.BESTELLUNG
WHERE IDBGPL.ORDERID='CCCCC' AND IDBGPL.ID=16437

--因为MAT表MAT.BESTELLUNG中对应的值又两行，所以没法指定唯一，
--现在想提取MAT表时间为MAX（DATE_LASTCHANGE）的一行作为最终查询结果。我写了个子查询，发现查询不出结果
--查询语句如下：
USE imos
SELECT * FROM 
IDBGPL LEFT JOIN IDBGRPS ON IDBGPL.ORDERID=IDBGRPS.ORDERID AND IDBGPL.ID=IDBGRPS.ID
       LEFT JOIN IDBINFO ON IDBGPL.ORDERID=IDBINFO.ORDERID AND IDBGRPS.HIGHARTID=IDBINFO.ID
       LEFT JOIN MAT     ON IDBGPL.MATNAME=MAT.BESTELLUNG
WHERE IDBGPL.ORDERID='CCCCC' AND IDBGPL.ID=16437 
and MAT.DATE_LASTCHANGE IN(SELECT MAX(MAT.DATE_LASTCHANGE)FROM MAT)

[顺势而为1]

SELECT * FROM 
IDBGPL LEFT JOIN IDBGRPS ON IDBGPL.ORDERID=IDBGRPS.ORDERID AND IDBGPL.ID=IDBGRPS.ID
       LEFT JOIN IDBINFO ON IDBGPL.ORDERID=IDBINFO.ORDERID AND IDBGRPS.HIGHARTID=IDBINFO.ID
       LEFT JOIN ( Select * 
					From MAT a
					Where DATE_LASTCHANGE=(Select max(DATE_LASTCHANGE) From MAT b Where a.BESTELLUNG=b.BESTELLUNG)
				 ) MAT   ON IDBGPL.MATNAME=MAT.BESTELLUNG
WHERE IDBGPL.ORDERID='CCCCC' AND IDBGPL.ID=16437

[qq_40307897]

谢谢大哥~爱死这论坛了~