SELECT TOP 2 *
FROM t
WHERE field1 = 'a'
ORDER BY newid()
FROM t
WHERE field1 = 'a'
ORDER BY newid()
解决方案 »
- 写一个存储过程
- 散分兼讨论:大量数据更新的问题
- 有偿求助mssql 数据仓库架构设计
- 两张表:table1(ID0,ID1),table2(ID1,ID0),table1的ID1外键约束于table2,table2的ID0约束于table1,怎么插入数据??
- sql 模糊查询,求助!
- 这个语句有什么错误?
- 左外连接,右外连接,全连接,内连接。四种连接的差异,以及何时用什么连接??
- c#调用存储过程,存储过程要返回一个select结果集,同时返回一个output型参数,但是无法返回值,为什么?
- 我今天装了orcale,可是为什么启动不了服务,请高手指点
- oracle 高手!!·!help me
- 求一SQL语句写法!在select中如何生成1,2,3,...这样的序号值?
- Help,数据库为什么连接不上?
,(SELECT COUNT(*) FROM t WHERE field1 = 'a') Num
FROM t
WHERE field1 = 'a'
ORDER BY newid()
我要列出的不只是field1='a'的随便两条
而是field1中所有不同字段的都要列出两条
按table表,最后希望列出的是:
id field1 field2 field3 .....
2 a as sss
3 a df ddd
4 b asdf aaa
7 b ddd sss
5 c bbb ddd
6 d sss sss
8 d dds aaa
不知道这样行不行了,
我的随机视图意思是说
SELECT TOP 2 *
FROM t
ORDER BY newid()
结果不可重复
我的随机视图意思是说
SELECT *,newid() new_Row_Reamrk
FROM t
结果不可重复
楼上的请再指教一下,
where (select count(*) from table t2 where t1.field1=t2.field1 and t1.id<=t2.id) <2
where field1 in ( select field1 from t group by field1 having (count(field1)>1) )
(
select rownum num, * from t
where field1 in ( select field1 from t group by field1 having (count(field1)=1) )
)
unionselect * from
(
select rownum num, * from t
where field1 in ( select field1 from t group by field1 having (count(field1)>1) )
)
where mod(num,2) =0 //商为0
select TOP 2 max(id) as id,max(field1) as field1,max(field2) as field2,max(field3) as field3 FROM t where field1='查寻值' group by field1
这样会比较快哦。
下面是个用游标的例子/* ***************************************************************
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
在authors中,根据state字段,在相同的state字段中随机取出一条记录。这个也用到了游标
思路:
把所有的state字段提取到一个游标state_cursor 中,
再在游标中扫描,没扫描一个字段,就根据当前关键字把authors表中state字段等于当前字段的记录提取到一个游标state_1_cursor 中,
再在游标state_1_cursor 中随机的提取一条记录插入到临时表#test中。
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
*/
create procedure get_rnd_record
asdeclare @state varchar(20),
@var1 varchar(50), --保存记录中第一个字段
@var2 varchar(50), --保存记录中第二个字段
@var3 varchar(50) --保存记录中第三个字段
create table #test(fld1 varchar(50),fld2 varchar(50),fld3 varchar(50))
declare state_cursor cursor scroll for
select distinct state from authors
open state_cursor
--FETCH RELATIVE 1 FROM state_cursor
FETCH next from state_cursor into @state
while @@FETCH_STATUS=0
begin declare state_1_cursor cursor scroll for
select state,au_fname,au_lname from authors where state=@state
declare @row_count int --当前关键字的记录数
declare @rnd_recd int --随机记录数
open state_1_cursor
select state from authors where state=@state
set @row_count=@@rowcount
if @row_count=1 --如果只有一行
begin
FETCH next from state_1_cursor into @var1,@var2,@var3
insert into #test values(@var1,@var2,@var3)
end
else --如果有多行
begin
set @rnd_recd=cast(right(RAND()*1000, 2) as int)--产生随机数
while @rnd_recd>@row_count
begin
set @rnd_recd=cast(right(RAND()*1000, 2) as int)----产生随机数
end
FETCH relative @rnd_recd from state_1_cursor into @var1,@var2,@var3
insert into #test values(@var1,@var2,@var3)
end
declare state_cursor cursor scroll for
select distinct state from authors
open state_cursor close state_1_cursor
deallocate state_1_cursor --关闭游标
FETCH next from state_cursor into @state
end
--fetch cursor1 into @i
select * from #test
drop table #test
CLOSE state_cursor --关闭游标
DEALLOCATE state_cursor
select identity(int,1,1) idx,* into #3 from #2select * from #3 a
where (select count(*) from #3 where field1=a.field1 and idx<=a.idx)<=2
order by field1不随机,取前2
select * from top2 a
where (select count(*) from top2 where field1=a.field1 and idx<=a.idx)<=2
order by field1结果:
随机:
idx id field1 field2 field3
----------- ----------- ------ ---------- ----------
1 3 a sdaa asdaa
3 1 a aa aaa
2 7 b agha arewaa
10 4 b sdaa dfaaa
5 5 c fgaa gffaaa
6 8 d art ghha
9 6 d fgaa adsgdga
7 12 e art ghha
4 11 e art ghha(9 row(s) affected)
不随机:
id field1 field2 field3
----------- ------ ---------- ----------
1 a aa aaa
2 a asasa dfdfa
4 b sdaa dfaaa
7 b agha arewaa
5 c fgaa gffaaa
6 d fgaa adsgdga
8 d art ghha
9 e art ghha
10 e art ghha(9 row(s) affected)