34,593
社区成员
发帖
与我相关
我的任务
分享
USE [1112b]
GO
declare @in_kemu nvarchar(10)
set @in_kemu = '语文'
--这个大段是生成临时表
declare @QueryString nvarchar(500)
declare @kemu nvarchar(10)
set @kemu =@in_kemu --设置需传入动态语句的参数的值为@kemu
set @QueryString ='SELECT '+@kemu+' as 科目,
[ROW_NUMBER] = ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC),
[DENSE_RANK] = DENSE_RANK() OVER(ORDER BY '+@kemu+' DESC),
[PERCENT] = (ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC))*1./(SELECT COUNT(1) FROM scores_xx where 年级 ='+'1'+')
into #biaozhunfen
FROM scores_xx
where 年级 = '+'1'
exec (@QueryString) --请注意参数的顺序
--生成临时表结束
--在临时表中执行查询
declare @QueryString2 nvarchar(500)
declare @kemu2 nvarchar(10)
set @QueryString2 = 'SELECT TOP 1 '+@kemu2+' FROM
(
SELECT * FROM #biaozhunfen T1
WHERE NOT EXISTS
(SELECT 1 FROM #biaozhunfen WHERE [DENSE_RANK] = T1.[DENSE_RANK] AND [ROW_NUMBER] > T1.[ROW_NUMBER])
) AS A
ORDER BY ABS([PERCENT]-0.8)'
set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemu
exec (@QueryString2) --请注意参数的顺序
USE [1112b]
GO
declare @in_kemu nvarchar(10)
set @in_kemu = '语文'
--这个大段是生成临时表
declare @QueryString nvarchar(500)
declare @kemu nvarchar(10)
set @kemu =@in_kemu --设置需传入动态语句的参数的值为@kemu
set @QueryString ='SELECT '+@kemu+' as 科目,
[ROW_NUMBER] = ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC),
[DENSE_RANK] = DENSE_RANK() OVER(ORDER BY '+@kemu+' DESC),
[PERCENT] = (ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC))*1./(SELECT COUNT(1) FROM scores_xx where 年级 ='+'1'+')
into #biaozhunfen
FROM scores_xx
where 年级 = '+'1'
exec (@QueryString) --请注意参数的顺序
--生成临时表结束
--在临时表中执行查询
declare @QueryString2 nvarchar(500)
declare @kemu2 nvarchar(10)
/******放前面獲取參數值******/
set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemu
/******置后******/
set @QueryString2 = 'SELECT TOP 1 '+@kemu2+' FROM
(
SELECT * FROM #biaozhunfen T1
WHERE NOT EXISTS
(SELECT 1 FROM #biaozhunfen WHERE [DENSE_RANK] = T1.[DENSE_RANK] AND [ROW_NUMBER] > T1.[ROW_NUMBER])
) AS A
ORDER BY ABS([PERCENT]-0.8)'
exec (@QueryString2) --请注意参数的顺序
declare @in_kemu nvarchar(10)
set @in_kemu = '语文'
--在临时表中执行查询
declare @QueryString2 nvarchar(500)
declare @kemu2 nvarchar(10)
--提前设置@kemu2 的值
set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemu
set @QueryString2 = 'SELECT TOP 1 '+@kemu2+' FROM
(
SELECT * FROM #biaozhunfen T1
WHERE NOT EXISTS
(SELECT 1 FROM #biaozhunfen WHERE [DENSE_RANK] = T1.[DENSE_RANK] AND [ROW_NUMBER] > T1.[ROW_NUMBER])
) AS A
ORDER BY ABS([PERCENT]-0.8)'
--set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemu
PRINT (@QueryString2)
--exec (@QueryString2) --请注意参数的顺序
USE [1112b]
GO
declare @in_kemu nvarchar(10)
set @in_kemu = '语文'
--这个大段是生成临时表
declare @QueryString nvarchar(500)
declare @kemu nvarchar(10)
set @kemu =@in_kemu --设置需传入动态语句的参数的值为@kemu
create table #biaozhunfen(科目 nvarchar(10),[ROW_NUMBER] int,[DENSE_RANK] int, [PERCENT] float)
set @QueryString ='SELECT '+@kemu+' ,
ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC),
DENSE_RANK() OVER(ORDER BY '+@kemu+' DESC),
(ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC))*1./(SELECT COUNT(1) FROM scores_xx where 年级 ='+'1'+')
FROM scores_xx
where 年级 = '+'1'
insert into #biaozhunfen
exec (@QueryString) --请注意参数的顺序
--生成临时表结束
--在临时表中执行查询
declare @QueryString2 nvarchar(500)
declare @kemu2 nvarchar(10)
set @QueryString2 = 'SELECT TOP 1 '+@kemu2+' FROM
(
SELECT * FROM #biaozhunfen T1
WHERE NOT EXISTS
(SELECT 1 FROM #biaozhunfen WHERE [DENSE_RANK] = T1.[DENSE_RANK] AND [ROW_NUMBER] > T1.[ROW_NUMBER])
) AS A
ORDER BY ABS([PERCENT]-0.8)'
set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemu
exec (@QueryString2) --请注意参数的顺序