公司新来的漂亮学生妹妹同事问我,某个数据库表中有某个人有许多条记录,每条记录对应一个时间,她想找到用一条语句找到最早的时间和最近的时间两个记录,如下所示
name mdate cj
小芳 2007-1-1 58
小芳 2007-8-10 60
小芳 2008-2-22 80
小芳 2009-3-6 75就是要找到最早的时间2007-1-1和最近的时间2009-3-6两条记录,用一个语名实现,请问怎么实现呢?
name mdate cj
小芳 2007-1-1 58
小芳 2007-8-10 60
小芳 2008-2-22 80
小芳 2009-3-6 75就是要找到最早的时间2007-1-1和最近的时间2009-3-6两条记录,用一个语名实现,请问怎么实现呢?
from ta a
where not exists(select 1 from ta where name = a.name and mdate < a.mdate)
UNION ALL
SELECT * FROM TB T WHERE MDATE =(SELECT MAX(MDATE) FROM TB WHERE NAME=T.NAME)
UNION ALL
SELECT * FROM TB T WHERE MDATE =(SELECT MAX(MDATE) FROM TB WHERE NAME=T.NAME)
from ta a
where not exists(select 1 from ta where name = a.name and mdate < a.mdate)
or not exists(select 1 from ta where name = a.name and mdate > a.mdate)
go
create table [ta] (name nvarchar(4),mdate datetime,cj int)
insert into [ta]
select '小芳','2007-1-1',58 union all
select '小芳','2007-8-10',60 union all
select '小芳','2008-2-22',80 union all
select '小芳','2009-3-6',75
select *
from ta a
where not exists(select 1 from ta where name = a.name and mdate < a.mdate)
or not exists(select 1 from ta where name = a.name and mdate > a.mdate)
/*
name mdate cj
---- ----------------------- -----------
小芳 2007-01-01 00:00:00.000 58
小芳 2009-03-06 00:00:00.000 75(2 個資料列受到影響)*/
WHERE NOT EXISTS(SELECT 1 FROM T WHERE A.name=name AND mdate<A.mdate)
UNION ALL
SELECT * FROM T A
WHERE NOT EXISTS(SELECT 1 FROM T WHERE A.name=name AND mdate>A.mdate)
drop table tb;
create table tb(name varchar(10), mdate varchar(10), cj int)
insert into tb(name,mdate,cj)
select
'小芳','2007-01-01',58 union all select
'小芳','2007-08-10',60 union all select
'小芳','2008-02-22',80 union all select
'小芳','2009-03-06',75;select * from tb t1
where exists(
select name,max(mdate),min(mdate)
from tb t2
where t2.name=t1.name
group by name
having max(mdate)=t1.mdate or min(mdate)=t1.mdate )
--I will help you with my full ability.
drop table tb;
create table tb(name varchar(10), mdate varchar(10), cj int)
insert into tb(name,mdate,cj)
select
'小芳','2007-01-01',58 union all select
'小芳','2007-08-10',60 union all select
'小芳','2008-02-22',80 union all select
'小芳','2009-03-06',75 union all select
'小娟','2007-04-05',58 union all select
'小娟','2007-09-16',60 union all select
'小娟','2008-09-22',80 union all select
'小娟','2009-09-06',75 select * from tb t1
where exists(
select name,max(mdate),min(mdate)
from tb t2
where t2.name=t1.name
group by name
having max(mdate)=t1.mdate or min(mdate)=t1.mdate )
---------------------------------------------------------------
小芳 2007-01-01 58
小芳 2009-03-06 75
小娟 2007-04-05 58
小娟 2009-09-06 75
drop table tb;
create table tb(name varchar(10), mdate varchar(10), cj int)
insert into tb(name,mdate,cj)
select
'小芳','2007-01-01',58 union all select
'小芳','2007-08-10',60 union all select
'小芳','2008-02-22',80 union all select
'小芳','2009-03-06',75 union all select
'小骚货','2007-09-23',58 union all select
'小骚货','2007-01-10',60 union all select
'小骚货','2008-08-17',80 union all select
'小骚货','2009-08-29',75 union all select
'小娟','2007-04-05',58 union all select
'小娟','2007-09-16',60 union all select
'小娟','2008-09-22',80 union all select
'小娟','2009-09-06',75;select * from tb t1
where exists(
select name,max(mdate),min(mdate)
from tb t2
where t2.name=t1.name
group by name
having max(mdate)=t1.mdate or min(mdate)=t1.mdate )
---------------------------------------------------------------
小芳 2007-01-01 58
小芳 2009-03-06 75
小娟 2007-04-05 58
小娟 2009-09-06 75
小骚货 2007-01-10 60
小骚货 2009-08-29 75
create table tb(name varchar(10), mdate varchar(10), cj int)
insert into tb(name,mdate,cj)
select
'小芳','2007-01-01',58 union all select
'小芳','2007-08-10',60 union all select
'小芳','2008-02-22',80 union all select
'小芳','2009-03-06',75 union all select
'小骚货','2007-09-23',58 union all select
'小骚货','2007-01-10',60 union all select
'小骚货','2008-08-17',80 union all select
'小骚货','2009-08-29',75 union all select
'小娟','2007-04-05',58 union all select
'小娟','2007-09-16',60 union all select
'小娟','2008-09-22',80 union all select
'小娟','2009-09-06',75;select * from tb t1
where exists(
select name,max(mdate),min(mdate)
from tb t2
where t2.name=t1.name
group by name
having max(mdate)=t1.mdate or min(mdate)=t1.mdate )
and name='小骚货';--对不起,我不喜欢“小芳”,我喜欢“小骚货”,您要的数据,我没法给您!
create table tb(name varchar(10), mdate varchar(10), cj int)
insert into tb(name,mdate,cj)
select
'小芳','2007-01-01',58 union all select
'小芳','2007-08-10',60 union all select
'小芳','2008-02-22',80 union all select
'小芳','2009-03-06',75 union all select
'小骚货','2007-09-23',58 union all select
'小骚货','2007-01-10',60 union all select
'小骚货','2008-08-17',80 union all select
'小骚货','2009-08-29',75 union all select
'小娟','2007-04-05',58 union all select
'小娟','2007-09-16',60 union all select
'小娟','2008-09-22',80 union all select
'小娟','2009-09-06',75;select * from tb t1
where exists(
select name,max(mdate),min(mdate)
from tb t2
where t2.name=t1.name
group by name
having max(mdate)=t1.mdate or min(mdate)=t1.mdate )
and name='小骚货';--对不起,我不喜欢“小芳”,我喜欢“小骚货”,您要的数据,我没法给您!
---------------------------------------------------------------
小骚货 2007-01-10 60
小骚货 2009-08-29 75