一个SQL查询问题，有SQL大虾帮我解决一下，不盛感谢！！
Field1 Field2 Field3
M1 AB BA
M1 BA AB
M2 CD DC
M2 DC CD
如何通过SQL语句查询成以下结构：
Field1 Field2 Field3
M1 AB AB
M1 BA AB
M2 CD CD
M2 DC CD
或者这样的结果也可以
Field1 Field2 Field3
M1 AB BA
M1 BA BA
M2 CD DC
M2 DC DC
[解决办法]
select m.* , n.field3 from
(
select Field1,Field2 from tb
) m
left join
(
select field1,min(field2) field3 from tb group by field1
) n
on m.field1 = n.field1
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(Field1 varchar(10),Field2 varchar(10),Field3 varchar(10))
insert into tb(Field1,Field2,Field3) values( 'M1 ', 'AB ', 'BA ')
insert into tb(Field1,Field2,Field3) values( 'M1 ', 'BA ', 'AB ')
insert into tb(Field1,Field2,Field3) values( 'M2 ', 'CD ', 'DC ')
insert into tb(Field1,Field2,Field3) values( 'M2 ', 'DC ', 'CD ')
select m.* , n.field3 from
(
select Field1,Field2 from tb
) m
left join
(
select field1,min(field2) field3 from tb group by field1
) n
on m.field1 = n.field1
drop table tb
/*
Field1 Field2 field3
---------- ---------- ----------
M1 AB AB
M1 BA AB
M2 CD CD
M2 DC CD
（所影响的行数为 4 行）
*/
[解决办法]
--换MAX就是你后面的要求.
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(Field1 varchar(10),Field2 varchar(10),Field3 varchar(10))
insert into tb(Field1,Field2,Field3) values( 'M1 ', 'AB ', 'BA ')
insert into tb(Field1,Field2,Field3) values( 'M1 ', 'BA ', 'AB ')
insert into tb(Field1,Field2,Field3) values( 'M2 ', 'CD ', 'DC ')
insert into tb(Field1,Field2,Field3) values( 'M2 ', 'DC ', 'CD ')
select m.* , n.field3 from
(
select Field1,Field2 from tb
) m
left join
(
select field1,max(field2) field3 from tb group by field1
) n
on m.field1 = n.field1
drop table tb
/*
Field1 Field2 field3
---------- ---------- ----------
M1 AB BA
M1 BA BA
M2 CD DC
M2 DC DC
（所影响的行数为 4 行）
*/
[解决办法]
select m.* , n.field3 from
(
select Field1,Field2 from tb
) m
left join
(
select field1,min(field2) field3 from tb group by field1
) n
on m.field1 = n.field1
[解决办法]
lin_now(林:答案学一学,题目看一看,分接一接!) ( )
楼上兄弟,你这个和我一模一样,如果换成SQL版里面另外一个人的话,你这样跟,肯定要挨骂了.
不过,我不会骂你.
