用临时表来解决吧: select top 25 * into #t from tablename where clxz='公务车' and cllx='polo' order by newid()insert into #t select top 25 * from tablename where clxz='公务车' and cllx='audi' order by newid()insert into #t select top 25 * from tablename where clxz='私家车' and cllx='passat' order by newid()insert into #t select top 25 * from tablename where clxz='私家车' and cllx='bird' order by newid()select * from #t drop table #t
select top 25 * from tablename where clxz='公务车' and cllx='polo' union allselect top 25 * from tablename where clxz='公务车' and cllx='audi' union all select top 25 * from tablename where clxz='私家车' and cllx='passat' union all select top 25 * from tablename where clxz='私家车' and cllx='bird'如果不想平均的话你就自己调数字。如果你有车不够25就要记录余下的数目。 如果你想完全随机的话你可以用随机函数 create procedure aaaa @seed int as declare count int,randNum float set count=0 set randNum=0 while(count<50) begin randNum=RAND(@seed) if(randNum<0.25) insert into #t select top 1 * from tablename where clxz='私家车' and cllx='bird' and id not in #t else if(randNum>0.25 and randNum<5) insert into #t select top 1 * from tablename where clxz='私家车' and cllx='passat' and id not in #t else if(randNum>0.5 and randNum<0.75) insert into #t select top 1 * from tablename where clxz='私家车' and cllx='ddd' and id not in #t else if(randNum>0.75 ) insert into #t select top 25 * from tablename where clxz='私家车' and cllx='eeee' and id not in #t--后50个相同 end select * from #t drop table #t这么做除了适合抽奖以外就不要用了,因为速度很慢。
union
select top 50 * from 表 where clxz='私家车'
order by clxz
union all
select top 50 * from tablename where clxz='私家车'
cllx(车辆类型) clxz(车辆性质) xlrq(修理日期) khmc(客户名称)
polo 公务车 2004-01-01 变成
passat 私家车 2004-10-10 似的
audi 公务车 2001-01-01 反对
bird 私家车 2002-02-10 感到法
…… …… …… ……
现在如果要实现在若干条记录中随机取出100条记录,其中公车、私车各一半,并且四种车型(polo、passat、audi、bird)的车各占25%。
请各位多多帮忙!!!
select top 25 * into #t from tablename where clxz='公务车' and cllx='polo'
order by newid()insert into #t select top 25 * from tablename where clxz='公务车' and cllx='audi'
order by newid()insert into #t select top 25 * from tablename where clxz='私家车' and cllx='passat'
order by newid()insert into #t select top 25 * from tablename where clxz='私家车' and cllx='bird'
order by newid()select * from #t
drop table #t
union allselect top 25 * from tablename where clxz='公务车' and cllx='audi'
union all
select top 25 * from tablename where clxz='私家车' and cllx='passat'
union all
select top 25 * from tablename where clxz='私家车' and cllx='bird'如果不想平均的话你就自己调数字。如果你有车不够25就要记录余下的数目。
如果你想完全随机的话你可以用随机函数
create procedure aaaa
@seed int
as
declare count int,randNum float
set count=0
set randNum=0
while(count<50)
begin
randNum=RAND(@seed)
if(randNum<0.25)
insert into #t select top 1 * from tablename where clxz='私家车' and cllx='bird'
and id not in #t
else
if(randNum>0.25 and randNum<5)
insert into #t select top 1 * from tablename where clxz='私家车' and cllx='passat'
and id not in #t
else
if(randNum>0.5 and randNum<0.75)
insert into #t select top 1 * from tablename where clxz='私家车' and cllx='ddd'
and id not in #t
else
if(randNum>0.75 )
insert into #t select top 25 * from tablename where clxz='私家车' and cllx='eeee'
and id not in #t--后50个相同
end
select * from #t
drop table #t这么做除了适合抽奖以外就不要用了,因为速度很慢。