_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 貉れ襯殊語伎狩蟾

Contents

1
2
3 襷 焔 谿
4 磯殊


1 #

一危磯伎 蟯 貉るる一 覲企 螳 貉れ 蟯 讌覓語企 蠍れ 殊り . 覿覿 貉れ襯 讌 襷手 覈視 蟆曙郁 覿覿企. 蠏 伎 貉れ襯 朱 誤 譬 殊 蟆蠍 覓語企手 螳. 蠏碁 貉れ襯 覓伎^蟇 讌 襷手 覈視讌 . る 貉れ襯 誤 譴 碁 SQL覓語 蟆郁骸讌 豕 譴 貉れ襯 朱 蟆企. 覓朱 豕 貉れ襯 讌 襷朱 襯 讌. 貉れ螳 焔レ 譬り 伎 碁 讌 螻 豌襴 覓語螳 . 朱 一危磯伎れ 豌襴襯 蟆 る 焔レ 蟆 伎. 蠏碁 伎 貉れ襯 伎 蟆曙磯 . 蠏碁 蟆曙磯 朱 蟆郁骸螳 覲 蟆 企. れ螻 螳 企.
* 讌ъ 螳 - SQL ルレ 磯殊 貉れ 覿 殊 . 覿覿 貉れ襯 讌 SQL襦 燕 朱 讀(2009.09) 螳 危危蠍 覿襯 螳讌 ろ襴渚碁 讌覲伎襯 螳覃 蟆一 煙企手 讌 . 1% ルレ螳 讌 伎 SQL 讌覲伎伎 99% 朱 螳覦襯 螳覃 襷 "覦貎朱Μ"螳 螻殊 譬 蟆瑚襯 れ 覯 螳 覲 螳 . DB企れ DB襷 覃 讌襷 DB伎語 螳覦れ るジ 語企 伎 螻, SQL 襷讌 譴 . 蠏碁れ 螳伎殊.

2 #

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 覦 碁煙り ″ 伎 蟆.

3 襷 焔 谿 #

'譟一 語'朱 豈 覲企 ' 瑚概 螻襴讀'企朱 伎 . 企 蟆 COUNT(*)襯 伎 覯 襷り碓 SUM() 伎 蟲 覦. 觚貎朱Μ 譟一語朱 襴 '>=' '<=' 覿燕 一一螳 . 讀, 螳 磯殊 螻朱 X螻煙 蠏碁 螳 一危一 ′語 襷讌 蟆曙一企. 企 蟆曙磯 貉れ襯 磯 蟆 .

4 磯殊 #

磯殊 貉れ襯 伎 襭 觜 蟆 蟆曙郁 . 襯 れ, 蟲 蟆螻 螳 蟆曙一企.
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

蟇 蟆 . 覈襯 蟇 譟郁 蟆企. 蠍磯ゼ 蟇 覈 蟆企.