语法问题 被难住了！

[ofei]

我想执行如下的语句，在sqlserver里可以，但在mysql里就有语法问题！

select invoice_with_status.* from invoice_with_status,(select invoiceid,max(invoicestatus_dateadded) as invoicestatus_dateadded from Invoice_with_status where InvoiceStatusID !=2 group by invoiceid) f1 where invoice_with_status.invoiceid = f1.invoiceid and invoice_with_status.invoicestatus_dateadded = f1.invoicestatus_dateadded

不知道在mysql里如何写才正确，学mysql没长时间：）

出错信息
{Microsoft.Data.Odbc.OdbcException}
System.SystemException: {"ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select invoiceid,max(invoicestatus_dateadded) as invoicestatus_"}
_retcode: ERROR
Errors: {Microsoft.Data.Odbc.OdbcErrorCollection}
Message: "ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select invoiceid,max(invoicestatus_dateadded) as invoicestatus_"
odbcErrors: {Microsoft.Data.Odbc.OdbcErrorCollection}
Source: "libmyodbc3.so"

[alipaymate]

你的mysql 版本是 "[mysqld-4.0.17-nt]"

可能不支持 from 子句的 结果集.

在 my5.0 中就可以.

[ofei]

但客户那边用的是4.X

有没有可代替的办法？

[alipaymate]

不好意思， 现在才回答这个问题!

你可以试试:

select *
from invoice_with_status
where (invoiceid, invoicestatus_dateadded) in (

select invoiceid,
max(invoicestatus_dateadded)
from Invoice_with_status
where InvoiceStatusID !=2
group by invoiceid
);

[ofei]

不行的　in 前面只能是一个字段吧？

[alipaymate]

你可以试一下嘛!

[ice_berg16]

4.x的不支持子查询