转换MYSQL语法,转换错误函数如下:
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; SQL Server视图转MYSQL视图,附上SQL Server视图如下,那位好心人帮我转一下:
CREATE VIEW V_PatrolWork_Line_Dot
AS
SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID,
PatrolLine_Dot.DoorAddr
FROM PatrolWork INNER JOIN
PatrolLine_Dot 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 ON ----MYSQL中这里的ON认不到
PatrolWork.Line_ID = PatrolLine.LineID
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; SQL Server视图转MYSQL视图,附上SQL Server视图如下,那位好心人帮我转一下:
CREATE VIEW V_PatrolWork_Line_Dot
AS
SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID,
PatrolLine_Dot.DoorAddr
FROM PatrolWork INNER JOIN
PatrolLine_Dot 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 ON ----MYSQL中这里的ON认不到
PatrolWork.Line_ID = PatrolLine.LineID
解决方案 »
- SQL语句合并如何实现。
- 数据库开关的问题
- mysql连接池出错
- 共享 的数据库设计问题 MYSQL
- [100分]菜鸟问题,以时间为排序,给定一个时间段,和一个数字n,要求顺序获取这个时间段之前的n个记录和时间段的记录
- 触发器造成的内容更新是否会被写入binlog从而能够被slave同步?
- jdbc 事务,两条insert语句之间的事务关系
- linux Jsp 如何连接 MySQL
- 在windows98+apache+php+mysql环境下怎样配置才能访问cgi-bin目录?
- mysql如何统计以关联表中某个字段的所有取值所对应数据记录数,并以另一个字段作为分组条件
- 提示mysql TCP port 没有开启?
- 关于mysql登录问题
AS
SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID,
PatrolLine_Dot.DoorAddr
FROM PatrolWork INNER JOIN PatrolLine_Dot ON PatrolWork.Line_ID = PatrolLine.LineID
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
错误提示: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 PatrolWork.Line_ID = PatrolLine.LineID show WARNINGS' at line 9
CREATE VIEW V_PatrolWork_Line_Dot
AS
SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID,
PatrolLine_Dot.DoorAddr
FROM PatrolWork INNER JOIN
PatrolLine_Dot 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 ON
PatrolWork.Line_ID = PatrolLine.LineID3.触发器--错误提示-->"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;
错误提示: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.DeptName
FROM 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
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; #-- changed by ACMAIN, please refer to the mySQL syntax
end;
AS
SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID,
PatrolLine_Dot.DoorAddr
from ((PatrolLine_Dot INNER JOIN
PatrolLine PatrolLine_Dot.Line_id = PatrolLine.LineID )
INNER JOIN PatrolDot ON PatrolLine_Dot.DcuID = PatrolDot.DcuID)
INNER JOIN PatrolWork ON PatrolWork.Line_ID = PatrolLine.LineID
FOR EACH ROW
BEGIN
START TRANSACTION;
if not Exists(Select LineGroupID From PatrolLineGroup Where LineGroupID = NEW.LineGroup_ID) THEN
rollback work;
else
commit;
END IF;
END;
那个手册里内容我看过了,讲的太简单了,还有就是问题1, 2, 3都没有解决继续
CREATE VIEW V_PatrolWork_Line_Dot
AS
SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID,
PatrolLine_Dot.DoorAddr
FROM PatrolWork INNER JOIN PatrolLine_Dot ON PatrolWork.Line_ID = PatrolLine.LineID
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
DELIMITER $$DROP FUNCTION IF EXISTS `test`.`GetSatOrSunDay`$$CREATE DEFINER=`root`@`localhost` FUNCTION `GetSatOrSunDay`(Types int,
SDates varchar(10),
Edates varchar(10)) RETURNS int(11)
begin
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
INTO @TmpDays
FROM tempdata ;
return @TmpDays;
END$$DELIMITER ;
MYSQL似乎不支持在触发器中回滚