select m.*,
(select top 1 x3 from tb where x1 = m.x1 order by x2),
(select top 1 x3 from tb where x1 = m.x1 order by x2 desc)
from
(
select distinct x1 from tb
) m
(select top 1 x3 from tb where x1 = m.x1 order by x2),
(select top 1 x3 from tb where x1 = m.x1 order by x2 desc)
from
(
select distinct x1 from tb
) m
insert into tb values('a' , 0 , 'data1')
insert into tb values('a' , 1 , 'data2')
insert into tb values('a' , 33, 'data3')
insert into tb values('b' , 0 , 'data4')
insert into tb values('b' , 2 , 'data5')
insert into tb values('b' , 66, 'data6')
goselect m.*,
(select top 1 x3 from tb where x1 = m.x1 order by x2) [min],
(select top 1 x3 from tb where x1 = m.x1 order by x2 desc) [max]
from
(
select distinct x1 from tb
) mdrop table tb /*
x1 min max
---------- ---------- ----------
a data1 data3
b data4 data6(所影响的行数为 2 行)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([x1] varchar(1),[x2] int,[x3] varchar(5))
insert [tb]
select 'a',0,'data1' union all
select 'a',1,'data2' union all
select 'a',33,'data3' union all
select 'b',0,'data4' union all
select 'b',2,'data5' union all
select 'b',66,'data6'
---查询---
select
distinct
x1,
(select top 1 x3 from tb where x1=t.x1 order by x2) as minx3,
(select top 1 x3 from tb where x1=t.x1 order by x2 desc) as maxx3
from tb t
---结果---
x1 minx3 maxx3
---- ----- -----
a data1 data3
b data4 data6(所影响的行数为 2 行)
insert into tb values('a' , 0 , 'data1')
insert into tb values('a' , 1 , 'data2')
insert into tb values('a' , 33, 'data3')
insert into tb values('b' , 0 , 'data4')
insert into tb values('b' , 2 , 'data5')
insert into tb values('b' , 66, 'data6')
go
select m.x1,m.x3 [min],n.x3 [max]
from
(select * from tb a where not exists(select 1 from tb where x1=a.x1 and x3<a.x3)) m
left join
(select * from tb a where not exists(select 1 from tb where x1=a.x1 and x3>a.x3)) n
on m.x1=n.x1drop table tbx1 min max
---------- ---------- ----------
a data1 data3
b data4 data6(2 行受影响)
declare @表1 table (x1 varchar(1),x2 int,x3 varchar(5))
insert into @表1
select 'a',0,'data1' union all
select 'a',1,'data2' union all
select 'a',33,'data3' union all
select 'b',0,'data4' union all
select 'b',2,'data5' union all
select 'b',66,'data6'select * from @表1 a
where not exists(select 1 from @表1 where x1=a.x1 and x2>a.x2)
or
not exists(select 1 from @表1 where x1=a.x1 and x2<a.x2)
insert into tb values('a' , 0 , 'data1')
insert into tb values('a' , 1 , 'data2')
insert into tb values('a' , 33, 'data3')
insert into tb values('b' , 0 , 'data4')
insert into tb values('b' , 2 , 'data5')
insert into tb values('b' , 66, 'data6')
go
select m.x1,m.x3 [min],n.x3 [max]
from
(select x1,min(x3) x3 from tb group by x1) m
left join
(select x1,max(x3) x3 from tb group by x1) n
on m.x1=n.x1drop table tbx1 min max
---------- ---------- ----------
a data1 data3
b data4 data6(2 行受影响)
declare @tb table (x1 nvarchar(10),x2 int,x3 nvarchar(10))
insert into @tb select 'a',0,'data1'
union all select 'a',1,'data2'
union all select 'a',33,'data3'
union all select 'b',0,'data4'
union all select 'b',2,'data5'
union all select 'b',66,'data6'
select x1,MIN(x3),MAX(x3) from @tb group by x1x1
---------- ---------- ----------
a data1 data3
b data4 data6(2 行受影响)
declare @表1 table (x1 varchar(1),x2 int,x3 varchar(5))
insert into @表1
select 'a',0,'data1' union all
select 'a',1,'data2' union all
select 'a',33,'data3' union all
select 'b',0,'data4' union all
select 'b',2,'data5' union all
select 'b',66,'data6'select a.x1,a.x3,b.x3 from
(select * from @表1 a where not exists(select 1 from @表1 where x1=a.x1 and x2>a.x2))a,
(select * from @表1 a where not exists(select 1 from @表1 where x1=a.x1 and x2<a.x2))b
where a.x1=b.x1
create table tb(x1 varchar2(10), x2 int, x3 varchar2(10))
insert into tb values('a' , 0 , 'data1')
insert into tb values('a' , 1 , 'data2')
insert into tb values('a' , 33, 'data3')
insert into tb values('b' , 0 , 'data4')
insert into tb values('b' , 2 , 'data5')
insert into tb values('b' , 66, 'data6')
goselect m.x1 , m.x3 min , n.x3 max from
(select t.* , row_number() over(partition by x1 order by x2) px from tb t) m,
(select t.* , row_number() over(partition by x1 order by x2 desc) px from tb t) n
where m.x1 = n.x1 and m.px = 1 and n.px = 1drop table tb/*
X1 MIN MAX
---------- ---------- ----------
a data1 data3
b data4 data6 2 rows selected.
*/