Create Proc usp_LJ(@N int=0)
AS
select top @N
T_XCSJ.LJTM,
COUNT(T_XCSJ.LJTM) as SL,
T_LJTM.LJMC
From
T_XCSJ LEFT JOIN T_LJTM ON T_XCSJ.LJTM=T_LJTM.LJTM
group By T_XCSJ.LJTM,T_LJTM.LJMC
order By SL Desc它提示我这个错误:
服务器: 消息 170,级别 15,状态 1,过程 usp_LJ,行 5
第 5 行: '@i' 附近有语法错误。
AS
select top @N
T_XCSJ.LJTM,
COUNT(T_XCSJ.LJTM) as SL,
T_LJTM.LJMC
From
T_XCSJ LEFT JOIN T_LJTM ON T_XCSJ.LJTM=T_LJTM.LJTM
group By T_XCSJ.LJTM,T_LJTM.LJMC
order By SL Desc它提示我这个错误:
服务器: 消息 170,级别 15,状态 1,过程 usp_LJ,行 5
第 5 行: '@i' 附近有语法错误。
第 3 行: '@N' 附近有语法错误。
n 为常数,不能为变量的
AS
declare @sql nvarchar(300)
set @sql=N'select top '+ltrim(@N)+'
T_XCSJ.LJTM,
COUNT(T_XCSJ.LJTM) as SL,
T_LJTM.LJMC
From
T_XCSJ LEFT JOIN T_LJTM ON T_XCSJ.LJTM=T_LJTM.LJTM
group By T_XCSJ.LJTM,T_LJTM.LJMC
order By SL Desc'exec sp_executesql @sql
T_XCSJ.LJTM,
COUNT(T_XCSJ.LJTM) as SL,
T_LJTM.LJMC
From
T_XCSJ LEFT JOIN T_LJTM ON T_XCSJ.LJTM=T_LJTM.LJTM
group By T_XCSJ.LJTM,T_LJTM.LJMC
order By SL Desc加上括號試下
top后面只能跟常量必须
exec('select top '+cast(@n as varchar)+' T_XCSJ.LJTM,
COUNT(T_XCSJ.LJTM) as SL,
T_LJTM.LJMC
From
T_XCSJ LEFT JOIN T_LJTM ON T_XCSJ.LJTM=T_LJTM.LJTM
group By T_XCSJ.LJTM,T_LJTM.LJMC
order By SL Desc')
AS
Declare @i int
select @i=count(*) from T_XCSJ
select top @N
T_XCSJ.LJTM,
COUNT(T_XCSJ.LJTM) as SL,
T_LJTM.LJMC,round(cast(COUNT(T_XCSJ.LJTM) as float)/cast(@i as float)*100,0)
From
T_XCSJ LEFT JOIN T_LJTM ON T_XCSJ.LJTM=T_LJTM.LJTM
group By T_XCSJ.LJTM,T_LJTM.LJMC
order By SL Desc
提示这样的错误
服务器: 消息 170,级别 15,状态 1,过程 usp_LJ,行 6
第 6 行: '@N' 附近有语法错误。
CREATE Proc usp_LJ(@N int)
AS
Declare @i int
Declare @sql nvarchar(1000)
select @i=count(*) from T_XCSJ
set @sql='select top '+convert(varchar(5),@N)+' T_XCSJ.LJTM,
COUNT(T_XCSJ.LJTM) as SL,
T_LJTM.LJMC,Round(cast(COUNT(T_XCSJ.LJTM) as float)/cast('+cast(@i as varchar)+ ' as float)*100,0)
From
T_XCSJ LEFT JOIN T_LJTM
ON T_XCSJ.LJTM=T_LJTM.LJTM
Group By T_XCSJ.LJTM,T_LJTM.LJMC
Order By SL Desc'
Exec(@sql)
GO