首先有两个结果集
select madename,mid from made
select madename,mid from version
我要把这两个结果集连接起来,而且不能有重复,当然一个结果集中不会有数据重复,主要是当合并了made结果集和version结果集时会存在数据重复。
select madename,mid from made
select madename,mid from version
我要把这两个结果集连接起来,而且不能有重复,当然一个结果集中不会有数据重复,主要是当合并了made结果集和version结果集时会存在数据重复。
madename mid
AAA 1
BBB 2
CCC 3version结果集包含的数据如下:
madename mid
AAA 1
BBB 2
DDD 4
EEE 5合并这两个结果集后如下:
madename mid
AAA 1
BBB 2
CCC 3
DDD 4
EEE 5
union
select madename,mid from version
*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码) http://feiyun0112.cnblogs.com/
SELECT DISTINCT * FROM
(
select madename,mid from made
UNION ALL
select madename,mid from version
)A
select distinct madename,distinct mid from made as a join version as b
on a.madename=b.madename
SELECT DISTINCT * FROM
(
select madename,mid from made
union all
select madename,mid from version
)A orderby madename
根据我问的题目,
其实4楼的用法就可以解决
select madename,mid from made
union
select madename,mid from version
但是4的方法在效率上肯定会比union all要慢(为什么慢?大家可以看看这帖子:http://www.cnblogs.com/cuihongyu3503319/archive/2008/08/18/1270286.html)但是我看见了5楼的一个比较巧妙的用法,他是用union all+distinct来实现
SELECT DISTINCT * FROM
(
select madename,mid from made
union all
select madename,mid from version
)A orderby madename请问这两个方法在效率上那个快呢?
根据我问的题目,
其实4楼的用法就可以解决
select madename,mid from made
union
select madename,mid from version
但是4的方法在效率上肯定会比union all要慢(为什么慢?大家可以看看这帖子:http://www.cnblogs.com/cuihongyu3503319/archive/2008/08/18/1270286.html) 但是我看见了5楼的一个比较巧妙的用法,他是用union all+distinct来实现
SELECT DISTINCT * FROM
(
select madename,mid from made
union all
select madename,mid from version
)A orderby madename 请问这两个方法在效率上那个快呢?谁能肯定点告诉我吗?
select made.madename as aname,made.mid as amid,version.madename as bname,version.mid as bmid from made,version where made.mid=version.mid