帮忙写个SQL语句

michael_monkey 2010-03-11 11:01:36
假如有表如下:
col_obj_id col_txt1 col_txt2 col_date
--------------------------------------------------------
1 aa bb 2010-3-1
1 bb cc 2010-3-2
1 cc dd 2010-3-3
2 AA BB 2010-3-4
2 FF GG 2010-3-9

要想得到如下结果:
col_obj_id col_txt1 col_txt2 col_date
--------------------------------------------------------
1 aa dd 2010-3-3
2 AA GG 2010-3-9

SQL语句如何写?
...全文
91 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
michael_monkey 2010-03-11
  • 打赏
  • 举报
回复
呵呵,好快,结贴了

fredrickhu第一个语句有语法错误
fredrickhu第二个语句正确,但看得有点累
jiangshun的结果不正确
happyflystone即正确又简洁

星星代表实力?——我是屁精

jiangshun 2010-03-11
  • 打赏
  • 举报
回复

--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([col_obj_id] int,[col_txt1] varchar(2),[col_txt2] varchar(2),[col_date] datetime)
insert [TB]
select 1,'aa','bb','2010-3-1' union all
select 1,'bb','cc','2010-3-2' union all
select 1,'cc','dd','2010-3-3' union all
select 2,'AA','BB','2010-3-4' union all
select 2,'FF','GG','2010-3-9'

select * from [TB] t where not exists(select 1 from tb where t.[col_obj_id]=[col_obj_id] and t.[col_date]>[col_date])

/*
col_obj_id col_txt1 col_txt2 col_date
----------- -------- -------- -----------------------
1 aa bb 2010-03-01 00:00:00.000
2 AA BB 2010-03-04 00:00:00.000

(2 行受影响)

*/

drop table TB
--小F-- 2010-03-11
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 happyflystone 的回复:]
select col_obj_id,min(col_txt1),max(col_txt2),max(col_date)
from ta
group by col_obj_id
[/Quote]
丫的 我傻了
-狙击手- 2010-03-11
  • 打赏
  • 举报
回复

select col_obj_id,min(col_txt1),max(col_txt2),max(col_date)
from ta
group by col_obj_id
--小F-- 2010-03-11
  • 打赏
  • 举报
回复
select
a.col_obj_id,a.col_txt1,b.col_txt2 ,b.col_date
from
(select col_obj_id ,min(col_txt1) as col_txt1 from tb group by col_obj_id)a
join
(select col_obj_id ,max(col_txt2) as col_txt2 ,max(col_date) as col_date from tb group by col_obj_id)b
on
a.col_obj_id=b.col_obj_id
--小F-- 2010-03-11
  • 打赏
  • 举报
回复
select
a.col_obj_id ,a.col_txt1,
from
(select col_obj_id ,min(col_txt1) as col_txt1 from tb group by col_obj_id)a
join
(select col_obj_id ,max(col_txt2) as col_txt2 ,max(col_date) as col_date from tb group by col_obj_id)b
on
a.col_obj_id=b.col_obj_id
东那个升 2010-03-11
  • 打赏
  • 举报
回复
说下为什么是这个结果

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧