set @Sql="select top @PageSize * from (SELECT UserName,sum(ParticipateMoney) as ParticipateMoney,sum(Profit) as Profit FROM VI_Array3_Stat where CreateTime between @StartDate and @EndDate group by UserName order by participatemoney desc)"exec @sql
set @Sql="select top @PageSize * from (SELECT UserName,sum(ParticipateMoney) as ParticipateMoney,sum(Profit) as Profit FROM VI_Array3_Stat where CreateTime between @StartDate and @EndDate group by UserName order by participatemoney desc)"估计出来的语句是: select top XX * from (SELECT UserName,sum(ParticipateMoney) as ParticipateMoney,sum(Profit) as Profit FROM VI_Array3_Stat where CreateTime between 2006-3-28 15:41:47 and 2006-3-28 15:41:47 group by UserName order by participatemoney desc) 当然报错,因为你的时间没有单引号! '2006-3-28 15:41:47'
topken(topken) ,是的, 但是我怎么在字符串中添加一个单引号呢? 比如‘select * from table where createtime=’+变量,在createtime=后面再加上一个单引号的话,后面的不是都成了红色字体了?
set @StartDate = '''2006-3-28 15:41:47''' set @EndDate = '''2006-3-29 15:41:47'''或者: 改语句: set @Sql=".... CreateTime between '" + @StartDate +"' and '" + @EndDate +"'...."
改成: select top(@PageSize) * from (SELECT UserName, sum(ParticipateMoney) as ParticipateMoney, sum(Profit) as Profit FROM VI_Array3_Stat where CreateTime between @StartDate and @EndDate group by UserName order by participatemoney desc) as TempTb
上一个有点问题:ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。 改成: select top(@PageSize) * from (SELECT UserName, sum(ParticipateMoney) as SumParticipateMoney, sum(Profit) as SumProfit FROM VI_Array3_Stat where CreateTime between @StartDate and @EndDate group by UserName order ) as TempTb by SumParticipateMoney desc
上一个有点问题:ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。 改成: select top(@PageSize) * from (SELECT UserName, sum(ParticipateMoney) as SumParticipateMoney, sum(Profit) as SumProfit FROM VI_Array3_Stat where CreateTime between @StartDate and @EndDate group by UserName order ) as TempTb order by SumParticipateMoney desc据对正确
set @Sql="select top" + cast(@PageSize as int) + " * from (SELECT "后面以此类推
print @sql
看看出来的语句在查询分析器中是否能执行.
就可以知道错在那里了
select top XX * from (SELECT UserName,sum(ParticipateMoney) as ParticipateMoney,sum(Profit) as Profit FROM VI_Array3_Stat where CreateTime between 2006-3-28 15:41:47 and 2006-3-28 15:41:47 group by UserName order by participatemoney desc)
当然报错,因为你的时间没有单引号!
'2006-3-28 15:41:47'
新生成的表没有username字段无法进行排序重写sql
set @EndDate = '''2006-3-29 15:41:47'''或者:
改语句:
set @Sql=".... CreateTime between '" +
@StartDate +"' and '" +
@EndDate +"'...."
select top(@PageSize) * from
(SELECT
UserName,
sum(ParticipateMoney) as ParticipateMoney,
sum(Profit) as Profit
FROM VI_Array3_Stat
where CreateTime between @StartDate and @EndDate group by UserName order by participatemoney desc) as TempTb
改成:
select top(@PageSize) * from
(SELECT
UserName,
sum(ParticipateMoney) as SumParticipateMoney,
sum(Profit) as SumProfit
FROM VI_Array3_Stat
where CreateTime between @StartDate and @EndDate group by UserName order ) as TempTb
by SumParticipateMoney desc
改成:
select top(@PageSize) * from
(SELECT
UserName,
sum(ParticipateMoney) as SumParticipateMoney,
sum(Profit) as SumProfit
FROM VI_Array3_Stat
where CreateTime between @StartDate and @EndDate group by UserName order ) as TempTb
order by SumParticipateMoney desc据对正确
declare @EndDate smalldatetime,
declare @CurrentPage int,
declare @PageSize int,
declare @Sql varchar(3000)