先麻烦大家帮我解答了,我第一次用MYSQL。。
原本的SQL2005存储过程是这样的
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[pCMS_Site_Class__GetList]
@ClassID int
AS
BEGIN
with RelClass
as
(
select *,0 as Level,cast('0' as nvarchar(max)) as treepath from CMS_Site_Class where ClassID = @ClassID and ClassID_Parent=0
union all
select csc.*,
rc.[Level] + 1,dbo.Lpad(Row_Number()
over (order by csc.OrderID desc),8) as treepath
from CMS_Site_Class as csc inner join RelClass as rc on
csc.ClassID_Parent = rc.ClassID )
SELECT * from RelClass order by treepath
END现在在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 '[dbo].[pCMS_Site_Class__GetList]
@ClassID int
AS
BEGIN
with RelClass
a' at line 1
我第一次用mysql 对于这个 部位的 书写格式不是很了解,麻烦大家帮我改一下 ,谢谢了
原本的SQL2005存储过程是这样的
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[pCMS_Site_Class__GetList]
@ClassID int
AS
BEGIN
with RelClass
as
(
select *,0 as Level,cast('0' as nvarchar(max)) as treepath from CMS_Site_Class where ClassID = @ClassID and ClassID_Parent=0
union all
select csc.*,
rc.[Level] + 1,dbo.Lpad(Row_Number()
over (order by csc.OrderID desc),8) as treepath
from CMS_Site_Class as csc inner join RelClass as rc on
csc.ClassID_Parent = rc.ClassID )
SELECT * from RelClass order by treepath
END现在在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 '[dbo].[pCMS_Site_Class__GetList]
@ClassID int
AS
BEGIN
with RelClass
a' at line 1
我第一次用mysql 对于这个 部位的 书写格式不是很了解,麻烦大家帮我改一下 ,谢谢了
MySQL和MS SQL SERVER 还是有很多差异的。另外建议能说明一下你这个过程的想实现的功能。以方便别人理解你的代码。
我的数据库结构字段:
ClassID ClassID_Parent ClassName OrderID
麻烦了
我的数据库结构字段:
ClassID ClassID_Parent ClassName OrderID
麻烦了
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
完整的过程是这样的
ALTER PROCEDURE [dbo].[pCMS_Site_Class__GetList]
@ClassID int
AS
BEGIN
with RelClass
as
(
select *,0 as Level,cast('0' as nvarchar(max)) as treepath from CMS_Site_Class where ClassID = @ClassID and ClassID_Parent=0
union all
select csc.*,
rc.[Level] + 1,dbo.Lpad(Row_Number()
over (order by csc.OrderID desc),8) as treepath
from CMS_Site_Class as csc inner join RelClass as rc on
csc.ClassID_Parent = rc.ClassID )
SELECT * from RelClass order by treepath
END
另外你可以参考一下这个
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx
MySQL中进行树状所有子节点的查询
我这个存储过程中的几个SQL语句是对的。。我刚从自己SQL的项目里拿出来的
dbo.Lpad(Row_Number() over (order by csc.OrderID desc),8) as treepath 所以如果你说你的SQL语句在MYSQL中是对的,就比较奇怪了。可以提供一下你的mysql的版本吗?
额 对了 我是用mysql-front打开MYSQL的
1。把你的SQL语句先改成MYSQL兼容的SQL语句
2。把存储过程中的程序语句改写。建议你先自己完成第一步。