22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE PROC jianXiang
@userId numeric(18,0),
@fullName varchar(50)
AS
DECLARE @sql varchar(8000)
set @sql = '';
select @sql = @sql + ',sum(case when b.itemname = ''' + itemName + ''' then 1 else 0 end) as [' + itemName + ']'
from jianXiangView
where userId=@userId and fullName = @fullName
group by itemName
set @sql = 'select a.itemName as [项目]'+ @sql +',count(distinct a.athletename) as [项目报名人数]
from jianXiangView a,jianXiangView b
where a.athletename = b.athletename and a.userId='+@userId+' and a.fullName = '+@fullName+'
group by a.itemName '
print @sql
EXEC (@sql)
exec jianXiang @userId =9,@fullName='高中男子甲组'
服务器: 消息 8115,级别 16,状态 6,过程 jianXiang,行 13
将 varchar 转换为数据类型 numeric 时发生算术溢出错误。
,sum(case when b.itemname = '3000米' then 1 else 0 end) as [3000米],sum(case when b.itemname = '60米' then 1 else 0 end) as [60米],sum(case when b.itemname = '跳高' then 1 else 0 end) as [跳高]
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。
where manageId= 9 and fullName='高中男子甲组'
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'
DECLARE @sql varchar(8000)
set @sql = '';
select @sql = @sql + ',sum(case when b.itemname = ''' + itemName + ''' then 1 else 0 end) as [' + itemName + ']'
from #DEMO
group by itemName
set @sql = 'select a.itemName as [项目]'+ @sql +',count(distinct a.athletename) as [项目报名人数]
from #DEMO a,#DEMO b
where a.athletename = b.athletename
group by a.itemName '
print @sql
EXEC (@sql)
drop table #DEMO
--结果
项目 100米 110米栏 1500米 项目报名人数
100米 5 3 2 5
110米栏 3 3 1 3
1500米 2 1 2 2
select a.itemname
,sum(case when b.itemname = '100米' then 1 else 0 end) as [100米]
,sum(case when b.itemname = '100米栏' then 1 else 0 end) as [100米栏]
,sum(case when b.itemname = '1500米' then 1 else 0 end) as [1500米]
from tab a inner join tab b
on a.athletename = b.athletename
group by a.itemname
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 '李四','高中男子甲组','110米栏','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'
SELECT * FROM #DEMO
DECLARE @sql varchar(8000)
set @sql = '';
select @sql = @sql + ' (select count(1) from #DEMO A INNER JOIN (SELECT athleteName FROM #DEMO WHERE itemName='''
+ itemName + ''' GROUP BY athleteName ) B ON A.athleteName=B.athleteName AND A.itemName=t1.itemName ) AS ['+itemName+'], '
from #DEMO
group by itemName
set @sql = 'select itemName as [项目],'+ @sql +' count(*) as [项目报名人数]
from #DEMO t1
group by itemName '
print @sql
EXEC (@sql)
drop table #DEMO
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 '李四','高中男子甲组','110米栏','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'
SELECT * FROM #DEMO
DECLARE @sql varchar(8000)
set @sql = '';
select @sql = @sql + ' (select count(1) from #DEMO A INNER JOIN (SELECT athleteName FROM #DEMO WHERE itemName='''
+ itemName + ''' GROUP BY athleteName ) B ON A.athleteName=B.athleteName AND A.itemName=t1.itemName ) AS ['+itemName+'], '
from #DEMO
group by itemName
set @sql = 'select itemName as [项目],'+ @sql +' count(*) as [项目报名人数]
from #DEMO t1
group by itemName '
print @sql
EXEC (@sql)
drop table #DEMO