导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

关于查询 急~~~~~

heqiang133 2007-12-24 09:04:06
code st_date name tsl
1001 2003-12-31 kkk 0.04
1001 2005-12-10 kkk 0.03
1001 2006-12-1 kkk 0.03
10030 2004-1-1 ccc 0.03
10030 2005-1-12 ccc 0.02
1002 2004-1-10 zzz 0.01

用SQL语句查出下边的数据

1001 2006-12-1 kkk 0.03
10030 2005-1-12 ccc 0.02
1002 2004-1-10 zzz 0.01

方法越简练越好
...全文
51 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wzy_love_sly 2007-12-24
select * from tb a where not exists
(select 1 from tb where code = a.code and st_date > a.st_date)
回复
cxmcxm 2007-12-24
select * from 表名 a where not exists(select * from 表名 whree code=a.code and st_date>a.st_date)
回复
中国风 2007-12-24

select * from t a where st_date=(select max(st_date) from t where code=a.code)
回复
Limpire 2007-12-24
select *
from table a
where not exists(select 1 from table where code = a.code and st_date > a.st_date)
回复
-狙击手- 2007-12-24
declare @t table(code varchar(6),st_date datetime,[name] varchar(4),tsl numeric(12,2))
insert @t select '1001','2003-12-31','kkk',0.04
insert @t select '1001','2005-12-10','kkk',0.03
insert @t select '1001','2006-12-1','kkk',0.03
insert @t select '10030','2004-1-1','ccc',0.03
insert @t select '10030','2005-1-12','ccc',0.02
insert @t select '1002','2004-1-10','zzz',0.01


select *
from @t a
where not exists(select 1 from @t where code = a.code and st_date > a.st_date)
/*


code st_date name tsl
------ ------------------------------------------------------ ---- --------------
1001 2006-12-01 00:00:00.000 kkk .03
10030 2005-01-12 00:00:00.000 ccc .02
1002 2004-01-10 00:00:00.000 zzz .01

(所影响的行数为 3 行)

*/
回复
liangCK 2007-12-24
create table tb
(
code int,
st_date datetime,
name varchar(10),
tsl decimal(10,2)
)
insert tb select 1001, '2003-12-31' ,'kkk', 0.04
insert tb select 1001 , '2005-12-10' ,'kkk' , 0.03
insert tb select 1001 , '2006-12-1' ,'kkk' ,0.03
insert tb select 10030 , '2004-1-1' , 'ccc' ,0.03
insert tb select 10030 ,'2005-1-12', 'ccc' ,0.02
insert tb select 1002 ,'2004-1-10', 'zzz' ,0.01

select code,min(st_date) st_date,name,cast(min(tsl) as varchar) tsl
from tb
group by code,name
order by tsl desc

drop table tb

/*
code st_date name tsl
----------- ------------------------- ---------- ------------------------------
1001 2003-12-31 00:00:00.000 kkk 0.03
10030 2004-01-01 00:00:00.000 ccc 0.02
1002 2004-01-10 00:00:00.000 zzz 0.01

(所影响的行数为 3 行)
*/
回复
-狙击手- 2007-12-24
select *
from table a
where not exists(select 1 from table where code = a.code and st_date < a.st_date)
回复
heqiang133 2007-12-24
没人会吗?
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告