关键字 ‘with‘ 附近有语法错误。如果此语句是公用表表达式、xmlnamespaces 子句或者更改跟踪上下文子句，那么前一个语句必须以分号结尾

原创于 2023-05-29 15:02:16 发布 · 4.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #with #表达式 #sqlserver

SQL Server 专栏收录该内容

4 篇文章

订阅专栏

文章描述了一个在SQL查询中遇到的with子句附近的语法错误，问题在于前一个语句缺少分号。解决方案是在with子句前添加分号，从而修复了SQL语句，使其能够正确执行。

关键字 'with' 附近有语法错误。如果此语句是公用表表达式、xmlnamespaces 子句或者更改跟踪上下文子句，那么前一个语句必须以分号结尾

问题SQL语句

问题SQL语句


DECLARE @YYMM VARCHAR(10) = '202304'
WITH
EmpDayData as
(
	SELECT	
		EmpID,SUM(ISNULL(WorkTime,0)+isnull(OverTime1,0)+isnull(OverTime2,0)+isnull(OverTime3,0)) AS [总工时]
	FROM AttDayData
	WHERE CONVERT(VARCHAR(6),YYMMDD,112) = @YYMM
	AND EmpID IN (
		SELECT A.EmpID FROM (
			SELECT	EmpID,PartNo FROM PerEmpDayDoc
			WHERE CONVERT(VARCHAR(6),YYMMDD,112) = @YYMM
			GROUP BY EmpID,PartNo
		) A
		GROUP BY A.EmpID HAVING COUNT(A.EmpID) > 1
	)
	GROUP BY EmpID
)

SELECT * FROM EmpDayData

问题原因

在异常提示中已经说的很明白了，“前一个语句必须以分号结尾”它是指需要在with前面添加一个分号，不是在with语句后添加。

修改后的SQL如下


DECLARE @YYMM VARCHAR(10) = '202304'



;WITH
EmpDayData as
(
	SELECT	
		EmpID,SUM(ISNULL(WorkTime,0)+isnull(OverTime1,0)+isnull(OverTime2,0)+isnull(OverTime3,0)) AS [总工时]
	FROM AttDayData
	WHERE CONVERT(VARCHAR(6),YYMMDD,112) = @YYMM
	AND EmpID IN (
		SELECT A.EmpID FROM (
			SELECT	EmpID,PartNo FROM PerEmpDayDoc
			WHERE CONVERT(VARCHAR(6),YYMMDD,112) = @YYMM
			GROUP BY EmpID,PartNo
		) A
		GROUP BY A.EmpID HAVING COUNT(A.EmpID) > 1
	)
	GROUP BY EmpID
)

SELECT * FROM EmpDayData