如何取得两个SELECT语句结果的交集

[cyh027]

有数据表结构和纪录如下


select name1,min(time1) from 试验
group by name1
--用此语句得到每个名字的最初记录

select name1,min(time1) from 试验
where class1=2
group by name1
--用此语句得到每个名字，class1等于2的最早记录



--能否用一条命今得到这两条命令的交集，class1=2的，time1=最初时间的记录，只得到B1 2 2004-03-02 00:00:00


运行结果如下图

[liangCK]

select * from tb1
intersect
select * from tb2

[fa_ge]

select name,time1
from 
    (
     select name1,min(time1) as time1 from 试验 
      group by name1 
     union all 
      select name1,min(time1) as time1 from 试验 
      where class1=2 
     group by name1 
     )a
group by name1,time1
having count(1)>1

[cyh027]

LS的方法是我的是在一张TABLE里面，而且是用分组统计查询出来纪录集，按下面的写法会出错呀....

select name1,min(time1) from 试验
group by name1
intersect
select name1,min(time1) from 试验
where class1=2
group by name1

服务器: 消息 156，级别 15，状态 1，行 20
在关键字 'intersect' 附近有语法错误。

[liangCK]

嘿嘿..
不好意思..那是2005的写法..
看鶴嘯九天的

[Rainland_Lee]

sqlserver2000中好像不支持intersect运算,刚作业一个类似的练习
用嵌套应该能实现吧

select * from 试验
where class1=2
and time1=(select min(time1) from 试验 group by name1)

[cyh027]

弄了半天才看懂，请大家再帮忙看看是不是我这样理解的

select name1,time1
from 
    (
     select name1,min(time1) as time1 from 试验 
      group by name1 
     union all --取得包含所有记录的两个纪录集合的并集
      select name1,min(time1) as time1 from 试验 
      where class1=2 
     group by name1 
     )as a  --把所得纪录集合，当成上一组语句的数据来源
group by name1,time1
having count(1)>1 --过滤出有两条重复记录的记录，因为只有合符上面两个条件的纪录才会出现两次

[huangqing_80]

同意鹤啸九天的

[cyh027]

5楼的兄弟的方法也可以只不过我的SQL2000里好像要写成
select * from 试验
where class1=2
and time1 in (select min(time1) from 试验 group by name1)
才有效果

[vchao13]

select * from tb1
union all
select * from tb2

[Rainland_Lee]

谢8楼指正,小弟也有个问题,现在也没人回帖了,拜托看看
http://topic.csdn.net/u/20080421/19/7e74966a-c927-4ee7-9f7c-a476dadeb7a0.html

[cyh027]

select * from 试验
where class1=2
and time1=(select min(time1) from 试验 group by name1)
这个方法还是有一点问题，假设当A2有这样两条记录
A1 2 2004-03-02 00:00:00
A1 3 2004-03-01 00:00:00
这样得出来的记录集就不是很正确了.