有两个表ta和tb:
ta:id name
1 aa
2 bb
3 cc
4 dd
5 eetb: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
ta:id name
1 aa
2 bb
3 cc
4 dd
5 eetb: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
解决方案 »
- SQLServer2005 修改端口号在哪里修改
- 求一条SQL语句:取出数字符合要求的数据
- 求 拼VB SQL insert into 字符串
- 求一条sql语句
- 又一有难度的select语句
- 多条记录的一个单据,如何顺序重排行号?
- sp_trace_setfilter做成的过滤条件profiler里看不到?(紧急)
- 疑惑,一直调试不了,请高手指教
- asp把excel整个存进sql server后,我要把他以html的形式显示出来。怎么做???
- 开发数据库软件的困惑,救各位大侠指点迷经
- exec 的问题
- Microsoft Visual Studio 2008 Team Foundation Server 安装问题!急!高分相赠。
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
(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
union all
select a.name , min(time) time from a,b where a.id = b.id group by a.name
order by name , time
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)
goselect 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 , timedrop 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 行)*/
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 nameaa 2000
aa 1994
bb 1994
bb 1999
cc 2000
cc 1995
dd 1993
dd 1996
ee 2000
ee 1997
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
name time
---------- -----------
aa 1994
aa 2000
bb 1994
bb 1999
cc 1995
cc 2000
dd 1993
dd 1996
ee 1997
ee 2000
如果记录只有一条时,大小都是一条,会重复
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
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
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
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.IDName time
---------- -----------
aa 1994
2000
bb 1994
1999
cc 1995
2000
dd 1993
1996
ee 1997
2000(所影响的行数为 10 行)