求一sql语句

[LUCKEEO]

有两个表ta和tb：
ta：

id name
1 aa
2 bb
3 cc
4 dd
5 ee

tb：

id time
1 1994
2 1994
3 1995
4 1996
5 1997
1 1995
2 1999
3 1997
4 1993
5 2000
1 2000
3 2000

如何分别取出最大值和最小值，得到tc：
name time
aa 1994
aa 2000
bb 1994
bb 1999
cc 1995
cc 2000
dd 1993
dd 1996
ee 1997
ee 2000

[chuifengde]

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

• 打赏
• 举报

回复 赞

[kk19840210]

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

• 打赏
• 举报

回复 赞

[dawugui]

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

• 打赏
• 举报

回复 赞

[dawugui]

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 行）

*/

• 打赏
• 举报

回复 赞

[wzy_love_sly]

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

aa 2000
aa 1994
bb 1994
bb 1999
cc 2000
cc 1995
dd 1993
dd 1996
ee 2000
ee 1997

• 打赏
• 举报

回复 赞

[fwacky]

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 

• 打赏
• 举报

回复 赞

[fwacky]

name       time        
---------- ----------- 
aa         1994
aa         2000
bb         1994
bb         1999
cc         1995
cc         2000
dd         1993
dd         1996
ee         1997
ee         2000

• 打赏
• 举报

回复 赞

[LUCKEEO]

谢谢楼上的各位大侠，已经可以了。

• 打赏
• 举报

回复 赞

[中国风]

好像用union
如果记录只有一条时，大小都是一条，会重复

• 打赏
• 举报

回复 赞

[中国风]

把连接union all 改为 union ，这样会合并重复记录

• 打赏
• 举报

回复 赞

[LUCKEEO]

我用的是潇洒老乌龟大侠提供的代码：
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

如果要将第二个重复的name设为空，得到以下结果，该怎么修改呢？
name time
aa 1994
2000
bb 1994
1999
cc 1995
2000
dd 1993
1996
ee 1997
2000

• 打赏
• 举报

回复 赞

[tim_spac]

select name, time
from ta a
join (
select id, time=min(time) from tb group by id
union all
select id, time=max(time) from tb group by id
) as b on a.id=b.id

• 打赏
• 举报

回复 赞

[中国风]

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 行）

• 打赏
• 举报

回复 赞

[paradise885]

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 ID,Max(time) from (
select a.id, b.time,a.name from a inner join b on a.id=b.id
) t group by ID
union all
select ID,Min(time) from (
select a.id, b.time,a.name from a inner join b on a.id=b.id
) t group by ID

• 打赏
• 举报

回复 赞