原文地址：
http://www.pin5i.com/showtopic-21996.html
在SQL語句中，UNION關(guān)鍵字多用來將并列的多組查詢結(jié)果(表)合并成一個(gè)結(jié)果(表)，簡(jiǎn)單實(shí)例如下：
- SELECT [Id],[Name],[Comment] FROM [Product1]
- UNION
- SELECT [Id],[Name],[Comment] FROM [Product2]
復(fù)制代碼
上面的代碼可以實(shí)現(xiàn)將從Product1和Product2兩張表合并成一個(gè)表，如果您只是希望合并兩張表中符合特定條件的記錄抑或是合并兩張表各自的前N條記錄，那么您的代碼可能會(huì)像下面這樣寫：
- SELECT [Id],[Name],[Comment] FROM [Product1] WHERE LEN([Name]) > 5
- UNION
- SELECT [Id],[Name],[Comment] FROM [Product2] WHERE [Id] IN (11,20) AND [Comment] IS NOT NULL
- SELECT TOP N [Id],[Name],[Comment] FROM [Product1]
- UNION
- SELECT TOP N [Id],[Name],[Comment] FROM [Product2]
復(fù)制代碼
This is so easy！但是假如您希望從包含Type字段的某表中根據(jù)Type分別隨機(jī)篩選N條記錄并將結(jié)果合并成一張表，您可能會(huì)像下面這樣寫：
- SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE1' ORDER BY NEWID()
- UNION
- SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE2' ORDER BY NEWID()
- UNION
- SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE3' ORDER BY NEWID()
- UNION
- SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE4' ORDER BY NEWID()
- UNION
- SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE5' ORDER BY NEWID()
- UNION
- SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE6' ORDER BY NEWID()
- UNION
- SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE7' ORDER BY NEWID()
復(fù)制代碼
在查詢分析器中執(zhí)行如上語句會(huì)報(bào)錯(cuò),這個(gè)問題起初會(huì)令您覺得UNION在這方面似乎有點(diǎn)軟弱，難道UNION和ORDER BY就不能共存嗎？當(dāng)然可以，下面的代碼或許能實(shí)現(xiàn)與上面代碼希望實(shí)現(xiàn)的相同功能：
- SELECT * FROM
- (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE1' ORDER BY NEWID()) AS [Product1]
- UNION
- SELECT * FROM
- (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE2' ORDER BY NEWID()) AS [Product2]
- UNION
- SELECT * FROM
- (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE3' ORDER BY NEWID()) AS [Product3]
- UNION
- SELECT * FROM
- (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE4' ORDER BY NEWID()) AS [Product4]
- UNION
- SELECT * FROM
- (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE5' ORDER BY NEWID()) AS [Product5]
- UNION
- SELECT * FROM
- (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE6' ORDER BY NEWID()) AS [Product6]
- UNION
- SELECT * FROM
- (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE7' ORDER BY NEWID()) AS [Product7]
復(fù)制代碼
代碼看起來有些繁瑣，或許針對(duì)這個(gè)問題有更見簡(jiǎn)潔明快的方法，我在這里也只是拋磚引玉，希望各位大俠能相處更為經(jīng)典的代碼，待續(xù)~
注意：其實(shí)很多時(shí)候多可以用到這種技巧。
posted on 2011-03-21 15:25
漂漂 閱讀(297)
評(píng)論(0) 編輯 收藏 引用