原文地址：
http://www.pin5i.com/showtopic-21996.html
在SQL語句中，UNION關鍵字多用來將并列的多組查詢結果(表)合并成一個結果(表)，簡單實例如下：
- SELECT [Id],[Name],[Comment] FROM [Product1]
- UNION
- SELECT [Id],[Name],[Comment] FROM [Product2]
復制代碼
上面的代碼可以實現將從Product1和Product2兩張表合并成一個表，如果您只是希望合并兩張表中符合特定條件的記錄抑或是合并兩張表各自的前N條記錄，那么您的代碼可能會像下面這樣寫：
- 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]
復制代碼
This is so easy！但是假如您希望從包含Type字段的某表中根據Type分別隨機篩選N條記錄并將結果合并成一張表，您可能會像下面這樣寫：
- 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()
復制代碼
在查詢分析器中執行如上語句會報錯,這個問題起初會令您覺得UNION在這方面似乎有點軟弱，難道UNION和ORDER BY就不能共存嗎？當然可以，下面的代碼或許能實現與上面代碼希望實現的相同功能：
- 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]
復制代碼
代碼看起來有些繁瑣，或許針對這個問題有更見簡潔明快的方法，我在這里也只是拋磚引玉，希望各位大俠能相處更為經典的代碼，待續~
注意：其實很多時候多可以用到這種技巧。
posted on 2011-03-21 15:25
漂漂 閱讀(309)
評論(0) 編輯 收藏 引用