27,579
社区成员
发帖
与我相关
我的任务
分享
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)
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