我知道怎么随机取3个数字并显示总和;但是怎么样才能加上总和为24的条件呢?
select top 3 * from fundrun..Test
order by newid()
--having sum(a) = 24
compute sum(a)
select top 3 * from fundrun..Test
order by newid()
--having sum(a) = 24
compute sum(a)
解决方案 »
- 求助!统计问题
- 显示当前数据库下所有的数据表的命令是什么
- 两个问题,每个10分,谢谢帮忙
- 想深入研究SQL优化这方面技术,大家有没有好的学习资料推荐下
- 一个表中有很多字段,可以为空,怎么得到第一个非空字段。
- sql按时间统计
- 高手,SQL SERVER 一个表变化时,如何触发另一台机器的 ORACLE 的一个表?
- 使用SQLServer,在进行事务处理的过程中,突然断电.如何回复数据?请教
- 数据库无名停止服务?
- 这样的SQL语句怎么写?有a、b、c、d四个字段,d的数据是a+b+c
- 总是提示:当前事务未能导出到远程提供程序,已回滚了该事务!
- 在SQL SERVER里想写个函数判断字符串,写到这不会写了,晕~(只有5分了!)
select a.a as a_a, b.a as b_a, c.a as c_a, (a.a + b.a + c.a) as sums
from fundrun..Test a, fundrun..Test b, fundrun..Test c
where a.a < b.a and a.a < c.a and b.a < c.a
and (a.a + b.a + c.a) = 24
但是还是想各位能否给出利用方法:select top 3 * from fundrun..Test
order by newid()
实现的?
declare @t table (id int)
set @i=-100
while @i<=100
begin
insert into @t select @i
set @i=@i+1
end--隨即3個數,和為24
select A.id, B.id, 24-A.id-B.id from
(select top 1 id from @t order by newid()) A ,
(select top 1 id from @t order by newid()) B
select top 1 *
from fundrun..Test a, fundrun..Test b, fundrun..Test c
WHERE a.col+b.col+c.col=24
order by newid()-----------产生三个随机数,它们的和为24
SELECT TOP 1 a.colid,b.colid,c.colid
FROM syscolumns a, syscolumns b, syscolumns c
WHERE a.id=object_id('sysobjects')
AND b.id=object_id('sysobjects')
AND c.id=object_id('sysobjects')
AND a.colid+b.colid+c.colid=24
ORDER BY NEWID()
declare @t table (id int)
set @i=-100
while @i<=100
begin
insert into @t select @i
set @i=@i+1
end--隨即3個數,和為24
select A.id, B.id, 24-A.id-B.id from
(select top 1 id from @t order by newid()) A ,
(select top 1 id from @t order by newid()) B
where A.id+B.id<=124 and A.id+B.id>=-76
這樣就可以保證 3個數字都在 tabl中
where (A.id+B.id) between -76 and 124
declare @t table (id int)
set @i=-100
while @i<=100
begin
insert into @t select @i
set @i=@i+1
end
--SQL
select A.id as col1, B.id as col2, (24-A.id-B.id) as col3 from
(select top 1 id from @t order by newid()) A,
(select top 1 id from @t order by newid()) B
--where (A.id+B.id) between -76 and 124 可以保證 3個數字都在 @t 中,但會出現空記錄
--不加 where 時,不能保證第3個數字在 @t 中
order by newid()
你们的办法似乎可以,但是你们为了达到“保證第3個數字在 @t 中”,方法也太龌龊了点(太拘泥于当前的表数据)
——就不能加上where 24-A.id-B.id in (select * from @t)?
2、andy1995(拓狼 QQ群:16168607 MSN群:[email protected]) 方法也行
3、还是谢谢各位了!不过要是我要得到“3个数和为24的所有组合呢?”又该何解?