演示使用如下两张表:
CREATE TABLE #UserInfo (ID INT IDENTITY(1,1),UserAddress NVARCHAR(50),UserName NVARCHAR(50),EntryDate DATETIME) INSERT INTO #UserInfo ( UserAddress, UserName, EntryDate ) VALUES ( N\'北京\',N\'Joey\', \'2015-04-12\'), ( N\'上海\',N\'John\', \'2013-04-23\'), ( N\'郑州\',N\'Mery\', \'2012-03-17\'), ( N\'深圳\',N\'Anna\', \'2014-05-07\'), ( N\'合肥\',N\'Dave\', \'2011-01-12\'), ( N\'西安\',N\'Alex\', \'2012-03-03\') CREATE TABLE #UserOrder (ID INT IDENTITY(1,1),UserName NVARCHAR(50),Amount INT ,Price float) INSERT INTO #UserOrder ( UserName, Amount, Price ) VALUES ( N\'Joey\', 30,200), ( N\'John\', 20,120), ( N\'Mery\', 70,231), ( N\'Anna\', 10,201), ( N\'Dave\', 5,12), ( N\'Alex\', 98,1200)
合并查询的特点
1.合并表中的列的个数、数据类型必须相同或相兼容
--案例1 SELECT UserAddress, UserName, EntryDate FROM #UserInfo UNION SELECT Amount, UserName FROM #UserOrder
-----结果 列数个数不相等
消息 205,级别 16,状态 1,第 33 行
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。
--案例2 SELECT UserAddress, UserName, EntryDate FROM #UserInfo UNION SELECT Amount, UserName,Price FROM #UserOrder ------结果,数据类型不相同或不兼容 消息 245,级别 16,状态 1,第 33 行 在将 nvarchar 值 \'北京\' 转换成数据类型 int 时失败。
保证数据类型相同我们需要进行转换:
--案例 只是更加清晰理解UNION的使用方法,实际项目此类转换无意义 SELECT UserAddress, UserName, EntryDate FROM #UserInfo UNION SELECT CONVERT(NVARCHAR, Amount), UserName,CONVERT(datetime, Price) FROM #UserOrder
2.UNION 默认去掉重复值。如果允许有重复值,请使用UNION ALL
--案例1 SELECT UserAddress, UserName, EntryDate FROM #UserInfo UNION SELECT CONVERT(NVARCHAR, Amount), UserName,CONVERT(datetime, Price) FROM #UserOrder UNION SELECT UserAddress, UserName, EntryDate FROM #UserInfo ---结果只有12行,说明去掉重复值了
--案例2,改为UNION ALL 显示所有 SELECT UserAddress, UserName, EntryDate FROM #UserInfo UNION ALL SELECT CONVERT(NVARCHAR, Amount), UserName,CONVERT(datetime, Price) FROM #UserOrder UNION ALL SELECT UserAddress, UserName, EntryDate FROM #UserInfo ---结果显示18行,保留了重复数据
3.执行顺序与左向右(可 通过列的顺序或空格改变结果集的排列顺序)
--案例 例如将UserName 放在前面,可以比较下其他列的情况 SELECT UserName,UserAddress, EntryDate FROM #UserInfo UNION SELECT UserName, CONVERT(NVARCHAR, Amount), CONVERT(datetime, Price) FROM #UserOrder
--案例是用空格 排序,可以演示查看结果 SELECT UserName,UserAddress, EntryDate FROM #UserInfo UNION SELECT \' \', CONVERT(NVARCHAR, Amount), CONVERT(datetime, Price) FROM #UserOrder
4.可以与SELECT INTO 一起使用,但是INTO 必须放在第一个SELECT 语句中
--案例 只能放在第一个select 中 SELECT UserName,UserAddress, EntryDate Into #InsertTables FROM #UserInfo UNION SELECT UserName, CONVERT(NVARCHAR, Amount), CONVERT(datetime, Price)
FROM #UserOrder --结果显示 插入12条数据
5.可以对合并的结果集进行排序,但排序的ORDER BY 必须放在最后一个SELECT 后面,所使用的列名必须是第一个SELECT中出现过的
--案例 排序的ORDER BY 必须放在最后一个SELECT 后面 SELECT UserName,UserAddress, EntryDate FROM #UserInfo UNION SELECT UserName, CONVERT(NVARCHAR, Amount), CONVERT(datetime, Price) FROM #UserOrder ORDER BY UserName
--案例 如果需要随机获取指定数据,参照如下方法 SELECT* FROM (SELECT TOP 2 UserName,UserAddress, EntryDate FROM #UserInfo Order By NewId()) A1 UNION ALL SELECT* FROM (SELECT TOP 2 UserName, CONVERT(NVARCHAR, Amount) as Amount, CONVERT(datetime, Price)as Price FROM #UserOrder Order By NewId()) A2