SQLSERVER中遇到的SQL问题

最新推荐文章于 2022-07-22 10:31:44 发布

fkbush

最新推荐文章于 2022-07-22 10:31:44 发布

阅读量1.1k

点赞数

本文链接：https://blog.csdn.net/fkbush/article/details/49330707

版权

1.SQL如下

with tmp(menuid ,parentid ,menuname,path)as(
select menuid ,parentid ,menuname,menuname  path  from TKWORK.dbo.p_menu where menuid='A1'
UNION ALL
SELECT CHILD.menuid,CHILD.PARENTID,CHILD.MENUNAME,PARENT.path +'-'+child.menuname FROM TKWORK.dbo.P_MENU CHILD,TMP PARENT
WHERE CHILD.PARENTID=PARENT.menuid)
SELECT * FROM TMP

报错信息如下：

[Err] 42000 - [SQL Server]Types don't match between the anchor and the recursive part in column "path" of recursive query "tmp".

报错原因是字段的类型、长度必须一样，所以做如下修改：

with tmp(menuid ,parentid ,menuname,path)as(
select menuid ,parentid ,menuname,cast(menuname as varchar(50)) path  from TKWORK.dbo.p_menu where menuid='A1'
UNION ALL
SELECT CHILD.menuid,CHILD.PARENTID,CHILD.MENUNAME,cast(PARENT.path as varchar(250))+'-'+cast(child.menuname as varchar(249)) FROM TKWORK.dbo.P_MENU CHILD,TMP PARENT
WHERE CHILD.PARENTID=PARENT.menuid)
SELECT * FROM TMP

2.插入数据时，报如下错误：

[Err] 23000 - [SQL Server]Cannot insert explicit value for identity column in table 'subject_property' when IDENTITY_INSERT is set to OFF.

报错原因：建表时主键设置了自动增长，有如下语句

DBCC CHECKIDENT(N'[dbo].[table]', RESEED, 10000)

如果确定要使用自定义主键，可以用下面这种方式

SET IDENTITY_INSERT subject_property ON;
insert into subject_property(id,name,parent_id,level)
values(1111,'test',111,2);
SET IDENTITY_INSERT subject_property OFF;