首页 新闻 搜索 专区 学院

sql错误 ,求大家帮忙指正

0
悬赏园豆:5 [已解决问题] 解决于 2013-03-14 17:20

错误:TOP 子句中的行数必须是整数

 

SELECT TOP(Rand(9)*100) *
FROM   (
           SELECT *,
                  (
                      SELECT md.Attention
                      FROM   MasterDisplay md
                      WHERE  t1.UserID = md.UserID
                  ) IsEXIST,
                  ROW_NUMBER() OVER(ORDER BY t1.MBC DESC) Num
           FROM   (
                      SELECT TOP(200) su.UserID,ui.NickName,su.validated,ui.SelfPicture,
                             (
                                 SELECT COUNT(*)
                                 FROM   Concern cc
                                 WHERE  cc.BeConcernedUser = su.UserID
                             ) MBC,
                             (
                                 SELECT a.NodeName
                                 FROM   Area a
                                 WHERE  ui.Province = a.NodeID
                             ) AS ProvinceName,
                             (
                                 SELECT a.NodeName
                                 FROM   Area a
                                 WHERE  ui.City = a.NodeID
                             ) AS CityName
                      FROM   SysUser su,
                             UserInfo ui
                      WHERE  su.UserID = ui.UserID
                             AND su.UserID != 'admin'
                             AND su.UserID != 'xiaowei' and su.UserID not in(select BeConcernedUser from Concern where ConcernUser='codeyuyu')
                      ORDER BY
                             MBC DESC
                  )t1
       )T2
WHERE  T2.IsEXIST != '0' OR T2.IsEXIST IS NULL

sql
问题补充:

其实我想要做的是在前100中随机取出9个,麻烦大牛指点

梁子se7en的主页 梁子se7en | 初学一级 | 园豆:177
提问于:2012-06-05 13:19
< >
分享
最佳答案
0

select cast(floor(Rand()*100) as int) --0-99之间的任一整数
select cast(ceiling(Rand()*100) as int)--1-100之间的任一整数

 

这个是随机从表tb中选10条记录的sql:SELECT TOP 10 * FROM tb ORDER BY NEWID()

你可以先取top100,然后再随机选出SELECT TOP 10 * FROM tb ORDER BY NEWID()可能这样稍微有点繁琐,不过你可以试一下

收获园豆:2
^董^ | 菜鸟二级 |园豆:204 | 2012-06-05 13:30
其他回答(3)
0

select a = round(rand() * 100, 0)

 

你运行会知道结果。

 

另外,SQL语句里,TOP只能使用常量值。

要达到你的目的,你只能写成字符串。

 

declare @num int;

set @num = round(rand() * 100, 0);

declare @sql nvarchar(4000);

set @sql = 'select top ' + @num + ' * from ...'

exec @sql

 

收获园豆:1
无之无 | 园豆:5095 (大侠五级) | 2012-06-05 13:33
0

select top 9 * from(select top 100 * from 表名) a order by newid()

收获园豆:1
Rich.T | 园豆:3440 (老鸟四级) | 2012-06-05 14:55
0

SELECT  top(cast(Rand(9)*100 as int)) * from tableName;这个办法可行。

因为Top(val) val要求:指定返回行数的数值表达式。如果指定了 PERCENT,则 expression 将隐式转换为 float 值;否则,它将转换为 bigint。而Rand(num)返回的是float数值,因此会报错。

收获园豆:1
闪电风 | 园豆:125 (初学一级) | 2012-06-05 15:15
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册