22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE TB
(
id int identity(1,1),
x1 nvarchar(10),
x2 nvarchar(10)
)
INSERT INTO TB 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'
create table ta(x1 nvarchar(12),num int)
insert ta select
'aa',2 union all select
'bb',1 union all select
'cc',3
declare @count int
declare @maxid int
declare @x1 nvarchar(10)
set @count = 1
select x1, IDENTITY(int,1,1) as id into #tmp from tb group by x1
select @maxid = MAX(id) from #tmp
declare @result table( id int, x1 nvarchar(10), x2 nvarchar(10))
while @count <= @maxid
begin
select @x1 = x1 from #tmp where #tmp.id = @count
insert into @result select * from tb a where a.id in (select top (select num from ta where ta.x1 = @x1) id from tb b where b.x1 = @x1 order by NEWID())
set @count = @count + 1
end
select * from @result
drop table #tmp
/*
id x1 x2
1 aa a1
3 aa a3
5 bb b2
7 cc c1
8 cc c2
9 cc c3
*/
DECLARE @ROWS INT --取条数变量
SET @ROWS=2 --按需赋值
CREATE TABLE tmpTable(id int identity(1,1) ,x1 nvarchar(12),x2 nvarchar(12))
insert tmpTable 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'
--select * from tmpTable
declare @I int
declare @J int --总分组数
DECLARE @SQL CHAR(4000)
SET @SQL=''
CREATE table BT(id int identity(1,1) ,x1 nvarchar(12))
INSERT INTO BT
select X1 FROM tmpTable GROUP BY X1 ORDER BY X1
SET @J=@@ROWCOUNT
SET @I=1
WHILE @I<=@J --循环组织SQL语句
BEGIN
SET @SQL=RTRIM(@SQL)+' SELECT TOP '+str(@I+@ROWS-1) +' ID,X1,X2,flag='+str(@I)+
' FROM tmpTable WHERE
X1=(SELECT X1 FROM BT WHERE ID='+str(@I)+')'
IF @I<@J
SET @SQL=RTRIM(@SQL)+' UNION ALL '
SET @I=@I+1
CONTINUE
END
SET @SQL='select ID,X1,X2 from ('+RTRIM(@SQL)+') A order by flag,id'
--SELECT Rtrim(@SQL)
exec (@SQL)
DROP TABLE tmpTable
DROP TABLE BT
--执行结果
--------------------------
id x1 x2
1 aa a1
2 aa a2
4 bb b1
5 bb b2
6 bb b3
7 cc c1
8 cc c2
9 cc c3
]
-- 经过我向大侠们请教之后,总结了3个方法,但都是SQL2005的
if object_id('ta')is not null drop table ta
create table ta(id int identity(1,1) ,x1 nvarchar(12),x2 nvarchar(12))
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'union all select
'dd', 'd2'
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',3
--1、
;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
--2、
;with liang as
(
select a.x1,a.x2,b.num,
id=row_number() over(partition by a.x1 order by newid())
from ta as a join tb as b
on a.x1=b.x1
)
select x1,x2,id from liang
where id <=num;
--3、
select o.*
from tb cross apply (select top(tb.num) * from ta where ta.x1=tb.x1 order by newid()) o