求一sql语句

LUCKEEO 2007-12-12 05:05:13
有两个表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
...全文
72 14 打赏 收藏 举报
写回复
14 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
paradise885 2007-12-13
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
  • 打赏
  • 举报
回复
中国风 2007-12-13
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 行)


  • 打赏
  • 举报
回复
tim_spac 2007-12-13
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
  • 打赏
  • 举报
回复
LUCKEEO 2007-12-13
我用的是潇洒老乌龟大侠提供的代码:
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
  • 打赏
  • 举报
回复
中国风 2007-12-12
把连接union all 改为 union ,这样会合并重复记录
  • 打赏
  • 举报
回复
中国风 2007-12-12
好像用union
如果记录只有一条时,大小都是一条,会重复
  • 打赏
  • 举报
回复
LUCKEEO 2007-12-12
谢谢楼上的各位大侠,已经可以了。
  • 打赏
  • 举报
回复
fwacky 2007-12-12

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

  • 打赏
  • 举报
回复
fwacky 2007-12-12

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

  • 打赏
  • 举报
回复
wzy_love_sly 2007-12-12
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
  • 打赏
  • 举报
回复
dawugui 2007-12-12
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 行)

*/
  • 打赏
  • 举报
回复
dawugui 2007-12-12
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
  • 打赏
  • 举报
回复
kk19840210 2007-12-12
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
  • 打赏
  • 举报
回复
chuifengde 2007-12-12
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
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2007-12-12 05:05
社区公告
暂无公告