今天在SQL2008试了一个普通的递归SQL,在服务器端没问题,
但在客户端显示with语法错误,请教什么原因?
客户端是XP操作系统
WITH lmenu(name,senior,level) as
(
SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL
UNION ALL
SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
where a.senior = b.name
)
SELECT * from lmenu
------解决方案--------------------
with 前加个;号看。
------解决方案--------------------
- SQL code
WITH lmenu(name,senior,level) as(SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULLUNION ALLSELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu bwhere a.senior = b.name)SELECT * from lmenu--你这个是递归吗？给你一个递归的例子，最基础的递归：--> 测试数据：[tbl]goif object_id('[tbl]') is not null drop table [tbl]gocreate table [tbl]([name] varchar(1),[date] varchar(5),[num] int)goinsert [tbl]select 'a','1-1号',1 union allselect 'b','1-2号',4 union allselect 'a','1-3号',8 union allselect 'a','1-4号',5 union allselect 'b','1-5号',6 union allselect 'b','1-6号',9;with tas(select ROW_NUMBER()over(partition by nameorder by [date]) as id,*,num as total from tbl),m as(select id,name,[date],num,total from t where id=1union allselect a.id,a.name,a.[date],a.num,b.total+a.num from t ainner join m b on a.id=b.id+1 and a.name=b.name)select name,[date],num,total from m order by name/*name date num totala 1-3号 8 8a 1-4号 5 13a 1-1号 1 14b 1-2号 4 4b 1-5号 6 10b 1-6号 9 19*/