MSSQL转MYSQL遇到的语法问题(300)

1.转换MYSQL语法，转换错误函数如下： 错误提示：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 'return(TmpDays); end' at line 1 CREATE Function GetSatOrSunDay (Types int, SDates varchar(10), Edates varchar(10)) Returns int begin declare TmpDays int; select (case DAYOFWEEK('2009-02-28') When 7 then 1 else 0 end + IF(((datediff('2009-08-31', '2008-02-22')/7) - (datediff('2009-08-31', '2008-02-22') div 7)) > 0, datediff('2009-08-31', '2008-02-22') div 7 + 1, datediff('2009-08-31', '2008-02-22') div 7)) as tempfield FROM tempdata INTO TmpDays; return(TmpDays); end; 2.SQL Server视图转MYSQL视图，附上SQL Server视图如下，那位好心人帮我转一下： 错误提示：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 'ON PatrolLine.LineID = PatrolDay.Line_ID AND PatrolLine_Dot.Line' at line 16SELECT PatrolDay.PatrolerNo, PatrolDay.EvtTime, PatrolDay.DueTime, PatrolDay.FactTime, PatrolDay.ErrorMinute, PatrolDay.Flag, PatrolDay.Work_ID, PatrolDay.ID, PatrolLine.LineName, PatrolType.TypeName, PatrolLine_Dot.LineDotID, PatrolLine.LineID, USERS.UserName, DOOR.DoorName, PatrolDot.DcuID, PatrolDot.DoorAddr, USERS.Dept_ID AS DeptID, DEPT.DeptNameFROM PatrolLine_Dot INNER JOIN PatrolLine ON PatrolLine_Dot.Line_id = PatrolLine.LineID LEFT OUTER JOIN PatrolDot ON PatrolLine_Dot.DcuID = PatrolDot.DcuID AND PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr RIGHT OUTER JOIN PatrolType INNER JOIN USERS INNER JOIN PatrolDay ON USERS.UserNo = PatrolDay.PatrolerNo ON PatrolType.ID = PatrolDay.Flag INNER JOIN DEPT ON USERS.Dept_ID = DEPT.ID ON PatrolLine.LineID = PatrolDay.Line_ID AND PatrolLine_Dot.LineDotID = PatrolDay.LineDot_ID LEFT OUTER JOIN DOOR ON PatrolDot.DcuID = DOOR.DcuID AND PatrolDot.DoorAddr = DOOR.DoorAddr 3.触发器--错误提示-->"Not allowed to set autocommit from a stored function or trigger" CREATE DEFINER='root'@`localhost` TRIGGER `PatrolLineInsertA` AFTER INSERT ON `PatrolLine` FOR EACH ROW BEGIN SET AUTOCOMMIT = 0; START TRANSACTION; if not Exists(Select LineGroupID From PatrolLineGroup Where LineGroupID = NEW.LineGroup_ID) THEN rollback work; else commit; END IF; END;

CREATE VIEW V_PatrolWork_Line_DotASSELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID, PatrolLine_Dot.DoorAddrFROM PatrolWork INNER JOIN PatrolLine_Dot INNER JOIN //和PatrolWork join，On部分没有写？ PatrolLine ON PatrolLine_Dot.Line_id = PatrolLine.LineID INNER JOIN PatrolDot ON PatrolLine_Dot.DcuID = PatrolDot.DcuID AND PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr ON PatrolWork.Line_ID = PatrolLine.LineID

楼上的：往下看，在最后呢。。

把return(TmpDays); 改成return TmpDays; 试试

至于你那个视图，肯定是可以转换的，试试下面的语句：（建议你写的规范些.[]）SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID, PatrolLine_Dot.DoorAddr FROM PatrolWork INNER JOIN PatrolLine_Dot ON PatrolWork.Line_ID = PatrolLine_Dot.Line_id INNER JOIN PatrolLine ON PatrolLine_Dot.Line_id = PatrolLine.LineID INNER JOIN PatrolDot ON PatrolLine_Dot.DcuID = PatrolDot.DcuID AND PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr

to : szhcracker 1.把return(TmpDays); 改成return TmpDays; 还是错误。 2.INNER JOIN PatrolLine ON PatrolLine_Dot.Line_id = PatrolLine.LineID 把On提到这里来得到的数据和原来的不一至，专门那样写的.

应该是 Select aaa Into TmpDays From tbl ...，你看看是不是写反了你那个视图中，表PatrolWork与PatrolLine_Dot怎样关联呢？你那样写肯定不行，你可能要用子查询的方法写。

试试下面的语句：SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID, PatrolLine_Dot.DoorAddr FROM PatrolWork, PatrolLine_Dot, PatrolLine, PatrolDot WHERE PatrolLine_Dot.Line_id = PatrolLine.LineID AND PatrolLine_Dot.DcuID = PatrolDot.DcuID AND PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr AND PatrolWork.Line_ID = PatrolLine.LineID

还是不成，请看上面的。

视图里包函了， INNER JOIN, RIGHT OUTER JOIN，LEFT OUTER JOIN。。加上了AND以后全都变成INNER JOIN了。

return TmpDays;不需分号,上面那些语句也不需分号RIGHT OUTER JOIN PatrolType INNER JOIN--没有ON条件 USERS INNER JOIN--没有ON条件SET AUTOCOMMIT = 0;不能在过程或者触发器里设置这个

to kkyy 1.return TmpDays;不需分号,上面那些语句也不需分号--去掉分号错误信息和前面一至未发生变化。 2.RIGHT OUTER JOIN PatrolType INNER JOIN--没有ON条件 USERS INNER JOIN--没有ON条件 这是SQL SERVER的语法，ON在后面请仔细看，需转成MYSQL。 3.SET AUTOCOMMIT = 0;不能在过程或者触发器里设置这个--去除后错误还是和前面一至。

你那个函数我在EMS SQL Manger 2005 for MySQL中以如下语句试过，编译没有报错，不知道你那里报什么错。我的MySQL是5.0的。CREATE Function GetSatOrSunDay(lTypes int, SDates varchar(10), Edates varchar(10)) RETURNS intBEGIN Declare TmpDays int; Select (Case DAYOFWEEK('2009-02-28') When 7 Then 1 Else 0 End + IF(((DateDiff('2009-08-31', '2008-02-22')/7) - (DateDiff('2009-08-31', '2008-02-22') div 7)) > 0, DateDiff('2009-08-31', '2008-02-22') div 7 + 1, DateDiff('2009-08-31', '2008-02-22') div 7)) as tempfield FROM tempdata INTO TmpDays; Return TmpDays;END;编译后会变为：CREATE FUNCTION `GetSatOrSunDay`(lTypes int, SDates varchar(10), Edates varchar(10)) RETURNS int(11) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ''BEGIN Declare TmpDays int; Select (Case DAYOFWEEK('2009-02-28') When 7 then 1 else 0 end + IF(((datediff('2009-08-31', '2008-02-22')/7) - (datediff('2009-08-31', '2008-02-22') div 7)) > 0, datediff('2009-08-31', '2008-02-22') div 7 + 1, datediff('2009-08-31', '2008-02-22') div 7)) as tempfield FROM tempdata INTO TmpDays; return TmpDays;END;

你那个视图我改了一下，不过没有试过：SELECT RP.PatrolerNo, RP.EvtTime, RP.DueTime, RP.FactTime, RP.ErrorMinute, RP.Flag, RP.Work_ID, RP.ID, PL.LineName, RP.TypeName, PLD.LineDotID, PL.LineID, RP.UserName, RP.DoorName, PD.DcuID, PD.DoorAddr, RP.Dept_ID, RP.DeptName FROM PatrolLine_Dot PLD INNER JOIN PatrolLine PL ON PLD.Line_id = PL.LineID LEFT JOIN PatrolDot PD ON PLD.DcuID = PD.DcuID AND PLD.DoorAddr = PD.DoorAddr LEFT JOIN DOOR ON PD.DcuID = DOOR.DcuID AND PD.DoorAddr = DOOR.DoorAddr LEFT JOIN (SELECT P.PatrolerNo, P.EvtTime, P.DueTime, P.FactTime, P.ErrorMinute, P.Flag, P.Work_ID, P.ID, PT.TypeName, U.UserName, U.Dept_ID, D.DeptName, P.Line_ID, P.LineDot_ID FROM PatrolType PT INNER JOIN PatrolDay P ON PT.ID = P.Flag INNER JOIN USERS U ON U.UserNo = P.PatrolerNo INNER JOIN DEPT D ON U.Dept_ID = D.ID) RP ON PL.LineID = RP.Line_ID AND PLD.LineDotID = RP.LineDot_ID

to : szhcracker 谢谢你的回答，我用MYSQL FONT 4.1 数据库版本: 5.1，怎么都行不过去， 至于下面你改那个视图也不正确，原数据库查询可查出25条记录，用改写后的只能查出5条记录。。。 下面这里是我的数据库文件： http://access911.net/csdn/FileDescription.asp?mdb=2009-2-26&id=32

顶一下，看来这问题没人解决了。