use tempdb
go
if exists (select * from sysobjects where type = 'U' and name = 'tableA')
drop table tableA
create table tableA(group_id int , [rand] int)
go
insert into tableA values(11, 2)
insert into tableA values(12, 4)
insert into tableA values(13, 1)
go
if exists (select * from sysobjects where type = 'U' and name = 'tableB')
drop table tableB
create table tableB(group_id int , id varchar(10))
go
insert into tableB values(11, '螳レ')
insert into tableB values(11, '襷レ')
insert into tableB values(11, 'レ')
insert into tableB values(11, '′')
insert into tableB values(12, 'ル')
insert into tableB values(12, '覓願')
insert into tableB values(12, '蠍碁')
insert into tableB values(12, '螻蠍碁')
insert into tableB values(12, '蟾蠍碁')
insert into tableB values(12, '覦蠍碁')
insert into tableB values(13, '螳訖')
insert into tableB values(13, '訖')
go
select * from tableA
/*
group_id rand
----------- -----------
11 2
12 4
13 1
*/
select * from tableB
/*
group_id id
----------- ----------
11 螳レ
11 襷レ
11 レ
11 ′
12 ル
12 覓願
12 蠍碁
12 螻蠍碁
12 蟾蠍碁
12 覦蠍碁
13 螳訖
13 訖
*/
Group_id 螳 11 蟆 れ朱 2螳襯 螳語り, Group_id螳 12 蟆 れ朱 4螳襯 螳語る 企. ろ 蟆 覲伎ロ蠍 伎 螳 觀磯ゼ 燕.
create view v_tableB
as
select top 100 percent group_id, id
from tableB
order by NewID()
--れ 覿螳 .
--SQL Server 2005 覯 TableSample れ襯 覃 .
蠏碁Μ螻 覯 豌襴蠍 伎 貎朱Μ襯 燕. れ襦 貉れ襯 覃伎 SQL覓語 襷れ伎 螻殊企.
DECLARE
@group_id int
, @rand int
, @count int
, @cursor_count int
, @sql varchar(1000)
SET @cursor_count = 1
BEGIN
DECLARE table_cursor CURSOR FOR
SELECT group_id, rand FROM tableA
SELECT @count = COUNT(*) FROM tableA
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @group_id, @rand
SET @sql =
'SELECT TOP ' + CAST(@rand AS VARCHAR) +
' group_id, id FROM v_tableB ' +
'WHERE group_id = ' + CAST(@group_id AS VARCHAR) +
' UNION ALL '
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM table_cursor INTO @group_id, @rand
IF(@count <> @cursor_count)
BEGIN
SET @sql = @sql +
'SELECT TOP ' + CAST(@rand AS VARCHAR) +
' group_id, id FROM v_tableB ' +
'WHERE group_id = ' +
CAST(@group_id AS VARCHAR) + ' '
Print @count
Print @cursor_count
END
SET @cursor_count = @cursor_count + 1
IF(@count > @cursor_count)
SET @sql = @sql + 'UNION ALL '
END
CLOSE table_cursor
DEALLOCATE table_cursor
PRINT @sql
EXEC(@sql)
END
/*
group_id id
----------- ----------
11 螳レ
11 襷レ
12 ル
12 覓願
12 蠍碁
12 螻蠍碁
13 螳訖
*/
れ襦 @sql 襴壱 企慨覃 れ螻 螳 貎朱Μ覓語 朱 襷れ伎 蟆 覲 .
SELECT TOP 2 group_id, id FROM v_tableB WHERE group_id = 11
UNION ALL
SELECT TOP 4 group_id, id FROM v_tableB WHERE group_id = 12
UNION ALL
SELECT TOP 1 group_id, id FROM v_tableB WHERE group_id = 13
れ襦 v_tableB朱 觀磯ゼ 3覯企 蠏狩. 讀, 觀一 伎襦 tableB
NewID()襦 ろ 螳 至鍵 讌襷 group_id螳 襷 螳企朱 焔レ 谿 觜讌 蟆企. WHERE 譟郁唄 group_id 蟆一覩襦 れ襦 group_id 覦 碁煙り ″ 伎 蟆.