if object_id('ta')is not null drop table ta
create table ta(id int identity(1,1) ,x1 nvarchar(12),x2 nvarchar(12)) --实际的id不一定是连续的
insert ta select
'aa' , 'a1'union all select
'aa', 'a2'union all select
'aa', 'a3'union all select
'bb', 'b1'union all select
'bb', 'b2'union all select
'bb', 'b3'union all select
'cc', 'c1'union all select
'cc', 'c2'union all select
'cc', 'c3'union all select
'dd', 'd1'
--可能表Ta中,列x1还有ee,ff,gg。等,不定if object_id('tb')is not null drop table tb
create table tb(x1 nvarchar(12),num int)
insert tb select
'aa',2 union all select
'bb',1 union all select
'cc',2要求在Ta中选择记录,
如aa随机选择2条(tb.num=2)
如bb随机选择1条(tb.num=1)
如cc随机选择2条(tb.num=2)
如dd随机选择0条
go
create table ta(id int identity(1,1) ,x1 nvarchar(12),x2 nvarchar(12)) --实际的id不一定是连续的
insert ta select
'aa' , 'a1'union all select
'aa', 'a2'union all select
'aa', 'a3'union all select
'bb', 'b1'union all select
'bb', 'b2'union all select
'bb', 'b3'union all select
'cc', 'c1'union all select
'cc', 'c2'union all select
'cc', 'c3'union all select
'dd', 'd1'
--可能表Ta中,列x1还有ee,ff,gg。等,不定if object_id('tb')is not null drop table tb
go
create table tb(x1 nvarchar(12),num int)
insert tb select
'aa',2 union all select
'bb',1 union all select
'cc',2;with t as
(
select rn=row_number()over(partition by x1 order by newid()),*
from ta
)
select t.id,t.x1,t.x2
from t
join tb
on t.rn<=tb.num and t.x1=tb.x1
/*
id x1 x2
----------- ------------ ------------
3 aa a3
2 aa a2
4 bb b1
9 cc c3
8 cc c2(5 行受影响)
*/
create table ta(id int identity(1,1) ,x1 nvarchar(12),x2 nvarchar(12)) --实际的id不一定是连续的
insert ta select
'aa' , 'a1'union all select
'aa', 'a2'union all select
'aa', 'a3'union all select
'bb', 'b1'union all select
'bb', 'b2'union all select
'bb', 'b3'union all select
'cc', 'c1'union all select
'cc', 'c2'union all select
'cc', 'c3'union all select
'dd', 'd1'
--可能表Ta中,列x1还有ee,ff,gg。等,不定if object_id('tb')is not null drop table tb
create table tb(x1 nvarchar(12),num int)
insert tb select
'aa',2 union all select
'bb',1 union all select
'cc',2;with liang as
(
select a.x1,a.x2,b.num,
rowid=row_number() over(partition by a.x1 order by newid())
from ta as a join tb as b
on a.x1=b.x1
)
select * from liang
where rowid <=num;drop table ta,tb/*
x1 x2 num rowid
------------ ------------ ----------- --------------------
aa a3 2 1
aa a1 2 2
bb b3 1 1
cc c1 2 1
cc c3 2 2(5 行受影响)*/
create table ta(id int identity(1,1) ,x1 nvarchar(12),x2 nvarchar(12)) --实际的id不一定是连续的
insert ta select
'aa' , 'a1'union all select
'aa', 'a2'union all select
'aa', 'a3'union all select
'bb', 'b1'union all select
'bb', 'b2'union all select
'bb', 'b3'union all select
'cc', 'c1'union all select
'cc', 'c2'union all select
'cc', 'c3'union all select
'dd', 'd1'
--可能表Ta中,列x1还有ee,ff,gg。等,不定if object_id('tb')is not null drop table tb
create table tb(x1 nvarchar(12),num int)
insert tb select
'aa',2 union all select
'bb',1 union all select
'cc',2;with liang as
(
select a.x1,a.x2,b.num,
rowid=row_number() over(partition by a.x1 order by newid())
from ta as a join tb as b
on a.x1=b.x1
)
select * from liang
where rowid <=num;drop table ta,tb/*
x1 x2 num rowid
------------ ------------ ----------- --------------------
aa a3 2 1
aa a1 2 2
bb b3 1 1
cc c1 2 1
cc c3 2 2(5 行受影响)*/
create table ta(id int identity(1,1) ,x1 nvarchar(12),x2 nvarchar(12)) --实际的id不一定是连续的
insert ta select
'aa' , 'a1'union all select
'aa', 'a2'union all select
'aa', 'a3'union all select
'bb', 'b1'union all select
'bb', 'b2'union all select
'bb', 'b3'union all select
'cc', 'c1'union all select
'cc', 'c2'union all select
'cc', 'c3'union all select
'dd', 'd1'
--可能表Ta中,列x1还有ee,ff,gg。等,不定if object_id('tb')is not null drop table tb
create table tb(x1 nvarchar(12),num int)
insert tb select
'aa',2 union all select
'bb',1 union all select
'cc',2select o.*
from tb cross apply (select top(tb.num) * from ta where ta.x1=tb.x1 order by newid()) o
对这个不是太了解,望指教
;with t as
(
)
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( a int)
go
insert tb SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 5
UNION ALL SELECT 7
UNION ALL SELECT 9
UNION ALL SELECT 10
go
update t
set t.a=k.a
from (select id=ROW_NUMBER() over(order by a),a from tb) t , (select id=ROW_NUMBER() over(order by a desc ),a from tb) k
where t.id=k.id
select * from tb
go
/*
a
-----------
10
9
7
5
2
1*/