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

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

• 问题SQL语句
• • 问题原因
  • 修改后的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

修改后问题解决