--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (城市类型 varchar(4),最小面积 int,最大面积 int) insert into #T select '山地',200,500 union all select '林地',200,500 union all select '草原',200,500 union all select '湖泊',200,500select top 1 * from #T where 最小面积>=200 and 最大面积<=500 order by newid()
select top 1 * from [Table] where 城市类型 in(select top 1 lx from (select '山地' lx union all select '林地' union all select '草原' union all select '湖泊') xx order by newid()) and 最小面积>=200 and 最大面积<=500
select top 1 * from tb where 城市类型 in ('山地','林地','草原','湖泊') and @area between 最小面积 and 最大面积 order by newid()
1楼的就可以了啥,还有什么问题,select top 1 * from @tb where 最小面积>=200 and 最大面积<=500 order by newid()
如果最小面积跟最大面积每条记录都一样的话就好办点select top 1 城市类型, 面积= left(RAND()*300+200,3) from #T order by newid()
select top 1 城市类型, 面积= left(RAND()*(最大面积-最小面积)+最小面积,3) from #T order by newid()这样吧
declare @t table([type] varchar(4),[m1] int,[m2] int) insert into @t select '山地',200,500 insert into @t select '林地',200,500 insert into @t select '草原',200,500 insert into @t select '湖泊',200,500--代码-------------------------------------------------------------------------- select top 1 城市类型=type,面积= case when m2-m1+1 <> 0 then abs(checksum(newid()))%(m2-m1+1)+m1 else m1 end from @t order by newid() /*结果-------------------------------------------------------------------------- 城市类型 面积 ---- ----------- 山地 364 --清除------------------------------------------------------------------------*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (城市类型 varchar(4),最小面积 int,最大面积 int)
insert into #T
select '山地',200,500 union all
select '林地',200,500 union all
select '草原',200,500 union all
select '湖泊',200,500select top 1 *
from #T
where 最小面积>=200 and 最大面积<=500
order by newid()
select top 1 * from tb
where 城市类型 in ('山地','林地','草原','湖泊')
and @area between 最小面积 and 最大面积
order by newid()
from @tb
where 最小面积>=200 and 最大面积<=500
order by newid()
面积= left(RAND()*300+200,3)
from #T
order by newid()
select top 1 城市类型,
面积= left(RAND()*(最大面积-最小面积)+最小面积,3)
from #T
order by newid()这样吧
/******************************************************************************/
/*回复:20080605002总:00063 */
/*主题:取随机 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @t table([type] varchar(4),[m1] int,[m2] int)
insert into @t select '山地',200,500
insert into @t select '林地',200,500
insert into @t select '草原',200,500
insert into @t select '湖泊',200,500--代码--------------------------------------------------------------------------
select top 1 城市类型=type,面积=abs(checksum(newid()))%300+200 from @t order by newid()/*结果--------------------------------------------------------------------------
城市类型 面积
---- -----------
草原 474 --清除------------------------------------------------------------------------*/
/******************************************************************************/
/*回复:20080605002总:00063 */
/*主题:取随机 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @t table([type] varchar(4),[m1] int,[m2] int)
insert into @t select '山地',200,500
insert into @t select '林地',200,500
insert into @t select '草原',200,500
insert into @t select '湖泊',200,500--代码--------------------------------------------------------------------------
select top 1 城市类型=type,面积=
case when m2-m1+1 <> 0 then abs(checksum(newid()))%(m2-m1+1)+m1 else m1 end
from @t order by newid()
/*结果--------------------------------------------------------------------------
城市类型 面积
---- -----------
山地 364
--清除------------------------------------------------------------------------*/
/******************************************************************************/
/*回复:20080605002总:00063 */
/*主题:取随机 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @t table([type] varchar(4),[m1] int,[m2] int)
insert into @t select '山地',200,500
insert into @t select '林地',2,5
insert into @t select '草原',2000,5000
insert into @t select '湖泊',20,50--代码--------------------------------------------------------------------------
--比较
declare @i int
set @i = 1
while @i<10
beginselect top 1 城市类型=type,面积=
case when m2-m1+1 <> 0 then abs(checksum(newid()))%(m2-m1+1)+m1 else m1 end
from @t order by newid() set @i = @i +1
end
/*结果--------------------------------------------------------------------------
城市类型 面积
---- -----------
湖泊 21 城市类型 面积
---- -----------
林地 5 城市类型 面积
---- -----------
草原 3881 城市类型 面积
---- -----------
湖泊 40 城市类型 面积
---- -----------
山地 248 城市类型 面积
---- -----------
山地 352 城市类型 面积
---- -----------
草原 4461 城市类型 面积
---- -----------
湖泊 23 城市类型 面积
---- -----------
山地 472
--清除------------------------------------------------------------------------*/