34,837
社区成员




create table A(id int, name varchar(10))
insert into A values(1, 'aa')
insert into A values(2, 'bb')
insert into A values(3, 'cc')
insert into A values(4, 'dd')
insert into A values(5, 'ee')
create table B(id int, time int)
insert into B values(1, 1994)
insert into B values(2, 1994)
insert into B values(3, 1995)
insert into B values(4, 1996)
insert into B values(5, 1997)
insert into B values(1, 1995)
insert into B values(2, 1999)
insert into B values(3, 1997)
insert into B values(4, 1993)
insert into B values(5, 2000)
insert into B values(1, 2000)
insert into B values(3, 2000)
go
select
Name=case when time!>(select min(time) from b where b.ID=a.ID) then a.Name else '' end,--可用=max
t.[time]
from
(select [ID],[time]=min(time) from B group by ID
union all
select [ID],[time]=max(time) from B group by ID
)T
join
A on t.ID=a.ID
Name time
---------- -----------
aa 1994
2000
bb 1994
1999
cc 1995
2000
dd 1993
1996
ee 1997
2000
(所影响的行数为 10 行)
name time
---------- -----------
aa 1994
aa 2000
bb 1994
bb 1999
cc 1995
cc 2000
dd 1993
dd 1996
ee 1997
ee 2000
create table LUCKEEOA
(
id int ,
name varchar(10)
)
insert into LUCKEEOA
select 1 , 'aa' union all
select 2 , 'bb' union all
select 3 , 'cc' union all
select 4 , 'dd' union all
select 5 , 'ee'
create table LUCKEEOB
(
id int,
time varchar(10)
)
delete LUCKEEOB
insert into LUCKEEOB
select 1 , '1994' union all
select 2 , '1994' union all
select 3 , '1995' union all
select 4 , '1996' union all
select 5 , '1997' union all
select 1 , '1995' union all
select 2 , '1999' union all
select 3 , '1997' union all
select 4 , '1993' union all
select 5 , '2000' union all
select 1 , '2000' union all
select 3 , '2000'
select name ,max(time) as maxtime from LUCKEEOA A,LUCKEEOB B
where A.id = B.id group by name
union all
select name ,min(time) as mintime from LUCKEEOA A,LUCKEEOB B
where A.id = B.id group by name
order by name
declare @a table (id int,name varchar(10))
insert into @a select 1,'aa'
insert into @a select 2,'bb'
insert into @a select 3,'cc'
insert into @a select 4,'dd'
insert into @a select 5,'ee'
declare @b table (id int,time int)
insert into @b select 1,1994
insert into @b select 2,1994
insert into @b select 3,1995
insert into @b select 4,1996
insert into @b select 5,1997
insert into @b select 1,1995
insert into @b select 2, 1999
insert into @b select 3,1997
insert into @b select 4,1993
insert into @b select 5,2000
insert into @b select 1,2000
insert into @b select 3,2000
select * from (
select a.name,b.time from @a a left join (
select id,max(time) as time from @b group by id
)b on a.id=b.id
union all
select a.name,b.time from @a a left join (
select id,min(time) as time from @b group by id
)b on a.id=b.id ) temp order by name
create table A(id int, name varchar(10))
insert into A values(1, 'aa')
insert into A values(2, 'bb')
insert into A values(3, 'cc')
insert into A values(4, 'dd')
insert into A values(5, 'ee')
create table B(id int, time int)
insert into B values(1, 1994)
insert into B values(2, 1994)
insert into B values(3, 1995)
insert into B values(4, 1996)
insert into B values(5, 1997)
insert into B values(1, 1995)
insert into B values(2, 1999)
insert into B values(3, 1997)
insert into B values(4, 1993)
insert into B values(5, 2000)
insert into B values(1, 2000)
insert into B values(3, 2000)
go
select a.name , max(time) time from a,b where a.id = b.id group by a.name
union all
select a.name , min(time) time from a,b where a.id = b.id group by a.name
order by name , time
drop table A,B
/*
name time
---------- -----------
aa 1994
aa 2000
bb 1994
bb 1999
cc 1995
cc 2000
dd 1993
dd 1996
ee 1997
ee 2000
(所影响的行数为 10 行)
*/
select a.name , max(time) time from a,b where a.id = b.id group by a.name
union all
select a.name , min(time) time from a,b where a.id = b.id group by a.name
order by name , time
select a.id,[time]=b.m_t from ta a inner join
(select id,m_t=max(time),n_t=min(time) from tb group by id)b on a.id=b.id
union all
select a.id,[time]=b.n_t from ta a inner join
(select id,m_t=max(time),n_t=min(time) from tb group by id)b on a.id=b.id
select * from (
select name,max(time) time
from ta,tb
where ta.id=tb.id group by name
)aa
union all
select * from(
select name,min(time) time
from ta,tb
where ta.id=tb.id group by name
)bb