DECLARE @department NVARCHAR(100), @month CHAR(6), @sql NVARCHAR(2000), @collist NVARCHAR(2000) SELECT @department = N'中保', @month = '201308' SET @collist = '' SELECT @collist = @collist + QUOTENAME(number+1)+' = SUM(CASE DAY(AllMonthDay) WHEN '+ LTRIM(number+1) +' THEN CNT END),' FROM master..spt_values WHERE type = 'p' AND number < DATEDIFF(DAY, @month+'01', DATEADD(MONTH,1,@month+'01')) SET @collist = @collist + '[总计] = SUM(CASE WHEN AllMonthDay IS NULL THEN cnt END)' SET @sql = N' SELECT '+ @collist +' FROM ( SELECT A.AllMonthDay, CNT = COUNT(b.CIID) FROM ( SELECT AllMonthDay=DATEADD(DAY, NUMBER, '''+ @month +'''+''01'') FROM master..spt_values WHERE type = ''p'' AND number < DATEDIFF(DAY, '''+ @month +'''+''01'', DATEADD(MONTH,1,'''+ @month +'''+''01'')) ) a LEFT JOIN CarInsuranceTL b ON a.AllMonthDay = b.[CIInsuranceDate] AND B.[CIInsuranceCompany] = N'''+ @department +''' GROUP BY A.AllMonthDay WITH ROLLUP ) T ' --PRINT @sql<br abp="288"> EXEC(@sql) 这段代码一直提示消息 170,级别 15,状态 1,第 10 行 第 10 行: '<' 附近有语法错误。但在第十行没有<符号,请大侠指点
DECLARE @department NVARCHAR(100), @month CHAR(6), @sql NVARCHAR(2000), @collist NVARCHAR(2000) SELECT @department = N'珠海店', @month = '201308' SET @collist = '' SELECT @collist = @collist + QUOTENAME(number+1)+' = SUM(CASE DAY(AllMonthDay) WHEN '+ LTRIM(number+1) +' THEN CNT END),' FROM master..spt_values WHERE type = 'p' and number < DATEDIFF(DAY,@month+'01',DATEADD(MONTH,1,@month+'01')) SET @collist = @collist + '[总计] = SUM(CASE WHEN AllMonthDay IS NULL THEN cnt END)' SET @sql = N' SELECT '+ @collist +' FROM ( SELECT A.AllMonthDay, CNT = COUNT(b.[CIID]) FROM ( SELECT AllMonthDay=DATEADD(DAY, NUMBER, '''+ @month +'''+''01'') FROM master..spt_values WHERE type = ''p'' and number < DATEDIFF(DAY,'''+ @month +'''+''01'',DATEADD(MONTH,1,'''+ @month +'''+''01'')) )a LEFT JOIN CarInsuranceTL b ON a.AllMonthDay = CONVERT(CHAR(10),b.[CIInsuranceDate],120) AND B.[Dealerships] = N'''+ @department +''' GROUP BY A.AllMonthDay WITH ROLLUP ) T ' --PRINT @sql EXEC(@sql)我改过后,为什么一直报错呢? 消息 170,级别 15,状态 1,第 9 行 第 9 行: 'DATEDIF' 附近有语法错误。语法错误,但是没发现语法错误的.
DATEDIFF 没有写错,我检查了几遍都查不到是哪里的错误
把拼的SQL,打印出来(PRINT @sql),发出来看看。
被截断了 改成VARCHAR(MAX) DECLARE @department NVARCHAR(100), @month CHAR(6), @sql NVARCHAR(max), @collist NVARCHAR(2000) SELECT @department = N'珠海店', @month = '201308' SET @collist = '' SELECT @collist = @collist + QUOTENAME(number+1)+' = SUM(CASE DAY(AllMonthDay) WHEN '+ LTRIM(number+1) +' THEN CNT END),' FROM master..spt_values WHERE type = 'p' and number < DATEDIFF(DAY,@month+'01',DATEADD(MONTH,1,@month+'01')) SET @collist = @collist + '[总计] = SUM(CASE WHEN AllMonthDay IS NULL THEN cnt END)' SET @sql = N' SELECT '+ @collist +' FROM ( SELECT A.AllMonthDay, CNT = COUNT(b.[CIID]) FROM ( SELECT AllMonthDay=DATEADD(DAY, NUMBER, '''+ @month +'''+''01'') FROM master..spt_values WHERE type = ''p'' and number < DATEDIFF(DAY, ''' + @month + '''+''01'', DATEADD(MONTH,1,''' + @month + '''+''01'')) )a LEFT JOIN CarInsuranceTL b ON a.AllMonthDay = CONVERT(CHAR(10),b.[CIInsuranceDate],120) AND B.[Dealerships] = N'''+ @department +''' GROUP BY A.AllMonthDay WITH ROLLUP ) T ' --PRINT @sql EXEC(@sql)
SELECT @department = N'中保', @month = '201308'
SET @collist = ''
SELECT @collist = @collist + QUOTENAME(number+1)+' = SUM(CASE DAY(AllMonthDay) WHEN '+ LTRIM(number+1) +' THEN CNT END),'
FROM master..spt_values
WHERE type = 'p'
AND number < DATEDIFF(DAY, @month+'01', DATEADD(MONTH,1,@month+'01'))
SET @collist = @collist + '[总计] = SUM(CASE WHEN AllMonthDay IS NULL THEN cnt END)'
SET @sql = N'
SELECT '+ @collist +'
FROM
(
SELECT A.AllMonthDay, CNT = COUNT(b.CIID)
FROM
(
SELECT AllMonthDay=DATEADD(DAY, NUMBER, '''+ @month +'''+''01'') FROM master..spt_values
WHERE type = ''p''
AND number < DATEDIFF(DAY, '''+ @month +'''+''01'', DATEADD(MONTH,1,'''+ @month +'''+''01''))
) a
LEFT JOIN CarInsuranceTL b
ON a.AllMonthDay = b.[CIInsuranceDate]
AND B.[CIInsuranceCompany] = N'''+ @department +'''
GROUP BY A.AllMonthDay
WITH ROLLUP
) T
'
--PRINT @sql<br abp="288">
EXEC(@sql)
这段代码一直提示消息 170,级别 15,状态 1,第 10 行
第 10 行: '<' 附近有语法错误。但在第十行没有<符号,请大侠指点
SELECT @department = N'珠海店', @month = '201308'
SET @collist = ''
SELECT @collist = @collist + QUOTENAME(number+1)+' = SUM(CASE DAY(AllMonthDay) WHEN '+ LTRIM(number+1) +' THEN CNT END),'
FROM master..spt_values WHERE type = 'p' and number < DATEDIFF(DAY,@month+'01',DATEADD(MONTH,1,@month+'01'))
SET @collist = @collist + '[总计] = SUM(CASE WHEN AllMonthDay IS NULL THEN cnt END)'
SET @sql = N'
SELECT '+ @collist +'
FROM
(
SELECT A.AllMonthDay, CNT = COUNT(b.[CIID])
FROM
(
SELECT AllMonthDay=DATEADD(DAY, NUMBER, '''+ @month +'''+''01'') FROM master..spt_values
WHERE type = ''p'' and number < DATEDIFF(DAY,'''+ @month +'''+''01'',DATEADD(MONTH,1,'''+ @month +'''+''01''))
)a
LEFT JOIN CarInsuranceTL b
ON a.AllMonthDay = CONVERT(CHAR(10),b.[CIInsuranceDate],120)
AND B.[Dealerships] = N'''+ @department +'''
GROUP BY A.AllMonthDay
WITH ROLLUP
) T
'
--PRINT @sql
EXEC(@sql)我改过后,为什么一直报错呢?
消息 170,级别 15,状态 1,第 9 行
第 9 行: 'DATEDIF' 附近有语法错误。语法错误,但是没发现语法错误的.
改成VARCHAR(MAX)
DECLARE @department NVARCHAR(100), @month CHAR(6), @sql NVARCHAR(max), @collist NVARCHAR(2000)
SELECT @department = N'珠海店', @month = '201308'
SET @collist = ''
SELECT @collist = @collist + QUOTENAME(number+1)+' = SUM(CASE DAY(AllMonthDay) WHEN '+ LTRIM(number+1) +' THEN CNT END),'
FROM master..spt_values WHERE type = 'p' and number < DATEDIFF(DAY,@month+'01',DATEADD(MONTH,1,@month+'01'))
SET @collist = @collist + '[总计] = SUM(CASE WHEN AllMonthDay IS NULL THEN cnt END)'
SET @sql = N'
SELECT '+ @collist +'
FROM
(
SELECT A.AllMonthDay, CNT = COUNT(b.[CIID])
FROM
(
SELECT AllMonthDay=DATEADD(DAY, NUMBER, '''+ @month +'''+''01'') FROM master..spt_values
WHERE type = ''p'' and number < DATEDIFF(DAY, ''' + @month + '''+''01'', DATEADD(MONTH,1,''' + @month + '''+''01''))
)a
LEFT JOIN CarInsuranceTL b
ON a.AllMonthDay = CONVERT(CHAR(10),b.[CIInsuranceDate],120)
AND B.[Dealerships] = N'''+ @department +'''
GROUP BY A.AllMonthDay
WITH ROLLUP
) T
'
--PRINT @sql
EXEC(@sql)
改成了存储过程,更方便了!