高分求一条Sql语句

[savagewang1978]

表A内容如下
aaa
bbb
表B内容如下：
aaa ccc1 2006
aaa ccc2 2005
bbb ddd1 2006
bbb ddd2 2005

要求结果如下：
aaa ccc1
bbb ddd1

根据表B的时间倒序出来，取时间最近的一条

[azsoft]

fcuandy(要学的东西还很多): 说得不无道理，如果col3也有重复 就要用到IDENTITY 取值了

[losedxyz]

select a, b from 表B where rq=(select max(rq) from 表B) group by rq

[fcuandy]

这全是让人猜,
tbA中的col跟
tbB中的col1 group出来的
一模一样,还有我上面说的那个猜测(还是取第三字段为”2006”的记录),尽管可能性能很小,但都要想.
比如实际上他的tbA中可能是 aaa bbb ccc这样的记录呢.
这时的要求就是col1以tbB为准

或者
比如实际上他的tbB中还有可能一箱 ccc cccN 2007的记录呢?
这时的要求就是col1以tbA为准

又或者
他表A中col1有 ccc,
表B中col1有 ddd
这时他要求的是两表col1的交集呢?

不能说他示例数据有问题,至少数据有冗佘,几条示例数据刚好又...

[azsoft]

fcuandy(要学的东西还很多) ：有点远了。

楼主呢？出来澄清一下，哈哈。。。

[fcuandy]

今晚有点无聊.呵呵,不好意思.

[savagewang1978]

楼上的都不错，真是感激不尽，给了我很多启发，谢谢大家了

[hurricane74]

晕了,看不明白

[ych3000]

create table A
(
stu varchar (3)
)
go

create table B
(
stu varchar (3),
mm varchar (4),
dd int ,
)
insert into A(stu) values('aaa')
insert into A(stu) values('bbb')

insert into B(stu,mm,dd) values ('aaa','ccc2',2005)
insert into B(stu,mm,dd) values ('bbb','ddd1',2006)
insert into B(stu,mm,dd) values ('bbb','ddd2',2005)
insert into B(stu,mm,dd) values ('aaa','ccc1',2006)


select * from a
select * from b

select a.stu,b.mm from a left outer join b on a.stu = b.stu where dd=2006 order by mm asc

[FollowIT]

已经测试通过:
create table #tmp_a(
data_field_1 varchar(10)
)
create table #tmp_b(
data_field_1 varchar(10),
data_field_2 varchar(10),
data_field_3 varchar(20)
)

insert into #tmp_a values ('aaa')
insert into #tmp_a values ('bbb')
insert into #tmp_b values ('aaa','ccc1','2006')
insert into #tmp_b values ('aaa','ccc2','2005')
insert into #tmp_b values ('bbb','ddd1','2006')
insert into #tmp_b values ('bbb','ddd2','2005')

select #tmp_a.data_field_1,#tmp_b.data_field_2 from #tmp_a,#tmp_b
where #tmp_a.data_field_1 = #tmp_b.data_field_1
and #tmp_b.data_field_3 in (select max(t.data_field_3) from #tmp_b,#tmp_b t where #tmp_b.data_field_1 = t.data_field_1)
order by #tmp_b.data_field_3 desc

drop table #tmp_a
drop table #tmp_b

result:
aaa ccc1
bbb ddd1

[十一月猪]

select a.col1 , a.col2
from tab a
where not exists (select 1 from tab b where a.col1 = b.col1 and a.col3 < b.col3)

or

select a.col1 , a.col2
from tab a
where col3 = (select max(col3) from tab b where a.col1 = b.col1)

[xiaoxiangqing]

if exists(select 1 from sysobjects where object_id('A')=id and type='u')
drop table A

if exists(select 1 from sysobjects where object_id('B')=id and type='u')
drop table B
go

create table A(bh varchar(20) not null)
create table B(bh varchar(20) not null,content varchar(20) not null,year int not null)
go

insert A
select 'aaa' union all
select 'bbb'

insert B
select 'aaa','ccc1',2006 union all
select 'aaa','ccc2',2005 union all
select 'bbb','ddd1',2006 union all
select 'bbb','ddd2',2005
go

select * from A
/*
bh
aaa
bbb
*/
select * from B
/*
bh content year
aaa ccc1 2006
aaa ccc2 2005
bbb ddd1 2006
bbb ddd2 2005
*/

select a.bh,b.content from A join B on a.bh=b.bh join (select bh,max(year) year from B group by bh)c on b.bh=c.bh and b.year=c.year order by 1
/*
bh content
aaa ccc1
bbb ddd1
*/

[ls0611]

借上面楼主的代码：

declare @a table
(
col1 char(3)
)
insert into @a
select 'aaa' union all
select 'bbb'

declare @b table
(
col1 char(3),
col2 char(4),
col3 int
)
insert into @b
select 'aaa','ccc1',2006 union all
select 'aaa','ccc2',2005 union all
select 'bbb','ddd1',2006 union all
select 'bbb','ddd2',2005

select a.col1,min(a.col2) as col2 from @b a
where a.col1 in (select col1 from @a)
group by a.col1

[alps014]

表A内容如下
colum1
aaa
bbb
表B内容如下：
colum1 colum2 colum3
aaa ccc1 2006
aaa ccc2 2005
bbb ddd1 2006
bbb ddd2 2005

要求结果如下：
aaa ccc1
bbb ddd1
select colum1,max(colum3) into #tmp1 from Bgroup by colum1
select B.colum1,B.colum2 from B where colum1 in ( select colum1 from A)

[paoluo]

ls0611(allan) ，你的結果是對的，但是你的語句完全不符合樓主的邏輯的。

[ls0611]

不好意思，没仔细看。

[ls0611]

declare @a table
(
col1 char(3)
)
insert into @a
select 'aaa' union all
select 'bbb'

declare @b table
(
col1 char(3),
col2 char(4),
col3 int
)
insert into @b
select 'aaa','ccc1',2006 union all
select 'aaa','ccc2',2005 union all
select 'bbb','ddd1',2006 union all
select 'bbb','ddd2',2005

select a.col1,a.col2 from @b a
left join (select col1,max(col1+cast(col3 as char(6))) as tt from @b group by col1)b on
a.col1=b.col1
where a.col1 in (select col1 from @a) and a.col1+cast(a.col3 as char(6))=b.tt
仅供参考！

[wcj86009]

学习

[kaka009]

create table A
(
stu varchar (3)
)
go

create table B
(
stu varchar (3),
mm varchar (4),
dd int ,
)
insert into A(stu) values('aaa')
insert into A(stu) values('bbb')

insert into B(stu,mm,dd) values ('aaa','ccc2',2005)
insert into B(stu,mm,dd) values ('bbb','ddd1',2006)
insert into B(stu,mm,dd) values ('bbb','ddd2',2005)
insert into B(stu,mm,dd) values ('aaa','ccc1',2006)


select B.stu ,B.mm from B left join A on B.stu=A.stu where dd=2006 order by mm asc

[swallowjiang]

表A内容如下
colum1
aaa
bbb
表B内容如下：
colum1 colum2 colum3
aaa ccc1 2006
aaa ccc2 2005
bbb ddd1 2006
bbb ddd2 2005

要求结果如下：
aaa ccc1
bbb ddd1
select colum1,max(colum3) into #tmp1 from Bgroup by colum1
select B.colum1,B.colum2 from B where colum1 in ( select colum1 from A)

[swallowjiang]

樓上的后兩句是我寫的SQL