declare @sql1 varchar(1000)
set @sql1= 'select itemname as '+'项目'
select @sql1 [email protected]+', sum (case itemname when '''+itemname+''' then 1 else 0 end )as ['+itemname+']'
from (select distinct itemname from #demo ) as a
set @sql1 = @sql1 + ', sum(1) 人数 from #demo group by itemname'
exec (@sql1)
中把 from (select distinct itemname from #demo ) as a
变成 from (select distinct itemname from #demo )
什么会报错
关键字 'set' 附近有语法错误。
CREATE TABLE #DEMO(athleteName VARCHAR(100),fullName VARCHAR(100),itemName varchar(100),athleteId varchar(100) ,id varchar(100))
insert into #DEMO
select '张三','高中男子甲组','100米','40','1'
union all
select '张三','高中男子甲组','110米栏','40','6'
union all
select '张三','高中男子甲组','1500米','40','7'
union all
select '李四','高中男子甲组','100米','41','8'
union all
select '李四','高中男子甲组','110米栏','41','9'
union all
select '王五','高中男子甲组','100米','42','10'
union all
select '王五','高中男子甲组','110米栏','42','11'
union all
select '赵六','高中男子甲组','100米','43','12'
union all
select '赵六','高中男子甲组','1500米','43','13'
union all
select '孙七','高中男子甲组','100米','44','14'
------解决方案--------------------
这个嵌套查询需要一个别名 这个是SQL 的规定
------解决方案--------------------