alter PROC 销售量在各省分布
@year int,@day int AS
SET NOCOUNT ON
DECLARE @STRSQL VARCHAR(MAX)
select t6.FName as 存货名称,t4.FName,t2.FConsignAmount,'' as 总计 into #TEMP from ICStockBill t1
inner join ICStockBillEntry t2 on t1.finterid = t2.FInterID
inner join t_Item t3 on t3.FItemID = t1.FSupplyID
inner join t_Item t4 on t4.FItemID = t3.FParentID
left join t_ICItem t6 on t2.fitemid = t6.fitemid --物料表
where t3.FNumber like '1.%'
SET @STRSQL = ''
SELECT @STRSQL = @STRSQL + 'SELECT 存货名称,总计'
SELECT @STRSQL = @STRSQL + 'sum(case when FNAME = '''+FName+''' then FConsignAmount else 0 end) as '''+FName+''', '
FROM (select distinct FName from #TEMP where FName <> '') as a order by FName
SELECT @STRSQL = left(@STRSQL,len(@STRSQL)-1) + 'FROM #TEMP GROUP BY 存货名称 'SELECT @STRSQL = @STRSQL + 'UNION ALL
SELECT 存货名称,总计,'
SELECT @STRSQL = @STRSQL + 'sum(case when FNAME = 存货名称 then FConsignAmount else 0 end) as ''总计'', '
FROM (select distinct 存货名称 from #TEMP where 存货名称 <> '') as a order by 存货名称
SELECT @STRSQL = left(@STRSQL,len(@STRSQL)-1) + 'FROM #TEMP GROUP BY 总计 'PRINT @STRSQL
EXEC (@STRSQL)
显示结果
存货名称 总计 A省.....B省...........................
错误提示:消息 195,级别 15,状态 10,第 1 行
'总计sum' 不是可以识别的 内置函数名称。
@year int,@day int AS
SET NOCOUNT ON
DECLARE @STRSQL VARCHAR(MAX)
select t6.FName as 存货名称,t4.FName,t2.FConsignAmount,'' as 总计 into #TEMP from ICStockBill t1
inner join ICStockBillEntry t2 on t1.finterid = t2.FInterID
inner join t_Item t3 on t3.FItemID = t1.FSupplyID
inner join t_Item t4 on t4.FItemID = t3.FParentID
left join t_ICItem t6 on t2.fitemid = t6.fitemid --物料表
where t3.FNumber like '1.%'
SET @STRSQL = ''
SELECT @STRSQL = @STRSQL + 'SELECT 存货名称,总计'
SELECT @STRSQL = @STRSQL + 'sum(case when FNAME = '''+FName+''' then FConsignAmount else 0 end) as '''+FName+''', '
FROM (select distinct FName from #TEMP where FName <> '') as a order by FName
SELECT @STRSQL = left(@STRSQL,len(@STRSQL)-1) + 'FROM #TEMP GROUP BY 存货名称 'SELECT @STRSQL = @STRSQL + 'UNION ALL
SELECT 存货名称,总计,'
SELECT @STRSQL = @STRSQL + 'sum(case when FNAME = 存货名称 then FConsignAmount else 0 end) as ''总计'', '
FROM (select distinct 存货名称 from #TEMP where 存货名称 <> '') as a order by 存货名称
SELECT @STRSQL = left(@STRSQL,len(@STRSQL)-1) + 'FROM #TEMP GROUP BY 总计 'PRINT @STRSQL
EXEC (@STRSQL)
显示结果
存货名称 总计 A省.....B省...........................
错误提示:消息 195,级别 15,状态 10,第 1 行
'总计sum' 不是可以识别的 内置函数名称。
SELECT @STRSQL = @STRSQL + 'SELECT 存货名称,总计,' -- 少了逗号
SELECT 存货名称,总计, sum(case when FNAME = 存货名称 then FConsignAmount else 0 end) as '总计', sum(case when FNAME = 存货名称 then FConsignAmou
消息 195,级别 15,状态 10,第 1 行
'总计sum' 不是可以识别的 内置函数名称。
FROM (
select 'AAA' as 存货名称,'BBB' FName,111 FConsignAmount,'' as 总计 UNION ALL
SELECT 'CCC','DDD',222,'' UNION ALL
SELECT 'CCC','EEE',222,''
) t
我用上面的代码生成临时表,加上了#2指出的逗号。最终合成了下面的SQL
SELECT 存货名称,
总计,
SUM(CASE
WHEN fname = 'BBB' THEN fconsignamount
ELSE 0
END) AS 'BBB',
SUM(CASE
WHEN fname = 'DDD' THEN fconsignamount
ELSE 0
END) AS 'DDD',
SUM(CASE
WHEN fname = 'EEE' THEN fconsignamount
ELSE 0
END) AS 'EEE'
FROM #temp
GROUP BY 存货名称
UNION ALL
SELECT 存货名称,
总计,
SUM(CASE
WHEN fname = 存货名称 THEN fconsignamount
ELSE 0
END) AS '总计',
SUM(CASE
WHEN fname = 存货名称 THEN fconsignamount
ELSE 0
END) AS '总计'
FROM #temp
GROUP BY 总计
先不说语法错误,你到底想做什么?
SELECT 存货名称,
总计,
SUM(CASE
WHEN fname = 存货名称 THEN fconsignamount
ELSE 0
END) AS '总计',
SUM(CASE
WHEN fname = 存货名称 THEN fconsignamount
ELSE 0
END) AS '总计'
FROM #temp
GROUP BY 总计
这是加一个汇总行吗 ? SELECT 存货名称,
SUM() AS '总计'
FROM #temp
GROUP BY 存货名称
老师 我问个问题
declare @s varchar(100),@sql varchar(1000)
set @s='a,b,c,d,e'
set @sql='select col='''+ replace(@s,',',''' union all select ''') +'''' ---是按字符格式 转的
PRINT @sql
exec (@sql)
这个 里面 @sql='select col='''+ replace(@s,',',''' union all select ''') +'''' 这些地方为什么 '''+ 这个3个单引号,还有 为什么 ''' union all select ''' 这也要3个引号?
PRINT '''aaa:双写单引号在字符串的头尾,看起来就是3连写'
aaa'bbb:字符串里面单引号要双写
'aaa:双写单引号在字符串的头尾,看起来就是3连写