求一sql语句

2007-12-12 05:05:13 205 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

保姆级教程：在Ubuntu 20.04上搞定pybind11，解决‘找不到头文件’的坑本文提供了一份详细的Ubuntu 20.04下pybind11配置指南，重点解决常见的'找不到头文件'问题。从环境准备、安装策略到三种专业解决方案（修改CMakeLists.txt、环境变量方案、绝对路径包含），帮助开发者高效搭建C++与Python的桥梁。阅读详情

有两个表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

...全文

sylar学习系列一开发环境 Centos7最小系统 gcc 9.1 cmake 环境可能存在的一些问题可能存在的问题，linux最小系统没有一些常用命令，如果我们处于外网我们可以yum安装,但却遇到以下问题 can not find a valid baseurl for repo: base/7/x86-64 可修改网络配置 vi /etc/sysconfig/network-scripts/ifcfg-ens123 # 不是每个主机都是ens123 把ONBOOT=no，改为ONBOOT=yes 重启网络阅读详情

LUCKEEO

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

求一sql语句

相关推荐