SQL 的 Union 为何出错？

[gotoyangjm]

try
{
CString strSQL;
strSQL.Format("Select InventoryNo,CheckingNo,Description,"
"[Department],Employee,DateAcquired,UnitPrice,Vendor,Status "
"From [家私基本信息] Where InventoryNo In "
"(Select InventoryNo From [资产维修信息] Where LimitDate<=GetDate())"
" And Status=2 And DepartmentID Like '%s' "
"Order By InventoryNo "
"Union "
"Select InventoryNo,CheckingNo,Description,"
"[Department],Employee,DateAcquired,UnitPrice,Vendor,Status "
"From [设备基本信息] Where InventoryNo In "
"(Select InventoryNo From [资产维修信息] Where LimitDate<=GetDate())"
" And Status=2 And DepartmentID Like '%s' "
"Order By InventoryNo",
gstrDepartmentID,gstrDepartmentID);
/////m_pSet为(CRecordset*)
if(m_pSet->IsOpen()) m_pSet->Close();m_pSet->Open(CRecordset::dynaset,strSQL,CRecordset::readOnly);
}
catch(...)
{
}

以上语句在 Access 数据库里顺利执行，但在 SQL 里却出错，提示语法错误，未能准备语句，请各位指点！

[gotoyangjm]

我发现把Order By语句放到最后就可以了，难道Order By一定要放在最后？这样的排序是对最后一个查询排序还是对整个查询排序？

[chenybin]

这中问题最好是在数据库环境里面看看

那样你可以比较块解决问题

[gotoyangjm]

哦，谢谢chenybin(小马) 提醒。