SQL的查询JOIN

[Sundery]

create table t1
(
[id] int identity(1,1) primary key,
Cargo char(1),
CargoNumber char(1),
InStockNumber int
)
insert into t1 values('A','B',10)
insert into t1 values('A','C',10)
insert into t1 values('A','D',10)
-------------------------------------------
create table t2
(
[id] int foreign key references t1([id]),
opDate datetime,
opNumber int
)
insert into t2 values(1,'2005-1-1',7)
insert into t2 values(2,'2005-1-1',3)
insert into t2 values(2,'2005-1-1',2)
insert into t2 values(2,'2005-1-1',4)


select a.cargo,a.cargonumber,a.instocknumber,b.opDate,b.opNumber
from t1 as a inner join t2 as b on (a.[id] = b.[id])


我希望的结果是
第3，第4行的A,C由于已经在显示过了，所以不要再显示出来

[Sundery]

更正一点，语句应该是：
select a.cargo,a.cargonumber,a.instocknumber,b.opDate,b.opNumber
from t1 as a left join t2 as b on (a.[id] = b.[id])


由于第3，第4行的A,C由于已经在显示过了，所以不要再显示出来

[xluzhong]

create table t1
(
[id] int identity(1,1) primary key,
Cargo char(1),
CargoNumber char(1),
InStockNumber int
)
insert into t1 values('A','B',10)
insert into t1 values('A','C',10)
insert into t1 values('A','D',10)
-------------------------------------------
create table t2
(
[id] int foreign key references t1([id]),
opDate datetime,
opNumber int
)
insert into t2 values(1,'2005-1-1',7)
insert into t2 values(2,'2005-1-1',3)
insert into t2 values(2,'2005-1-1',2)
insert into t2 values(2,'2005-1-1',4)

select
a.cargo,
a.cargonumber,
a.instocknumber,
b.opDate,
b.opNumber
from
t1 a
left join
(select * from t2 c where opnumber = (select top 1 opnumber from t2 where id=c.id order by newid())) b
on
(a.[id] = b.[id])

drop table t2
drop table t1

[zjcxc]

create table t1
(
[id] int identity(1,1) primary key,
Cargo char(1),
CargoNumber char(1),
InStockNumber int
)
insert into t1 values('A','B',10)
insert into t1 values('A','C',10)
insert into t1 values('A','D',10)
-------------------------------------------
create table t2
(
[id] int foreign key references t1([id]),
opDate datetime,
opNumber int
)
insert into t2 values(1,'2005-1-1',7)
insert into t2 values(2,'2005-1-1',3)
insert into t2 values(2,'2005-1-1',2)
insert into t2 values(2,'2005-1-1',4)


select a.cargo,a.cargonumber,a.instocknumber,b.opDate,b.opNumber
from t1 as a inner join t2 as b on (a.[id] = b.[id])
and b.opNumber=(select top 1 opNumber from t2 where id=a.id)

go

drop table t2,t1

/*--结果

cargo cargonumber instocknumber opDate opNumber
----- ----------- ------------- ------------------------ ---------
A B 10 2005-01-01 00:00:00.000 7
A C 10 2005-01-01 00:00:00.000 3

（所影响的行数为 2 行）
--*/

[zjcxc]

需要t2表中有能与id配置,唯一确定一条记录的字段,上述处理中,使用了opNumber

[zjcxc]

create table t1
(
[id] int identity(1,1) primary key,
Cargo char(1),
CargoNumber char(1),
InStockNumber int
)
insert into t1 values('A','B',10)
insert into t1 values('A','C',10)
insert into t1 values('A','D',10)
-------------------------------------------
create table t2
(
[id] int foreign key references t1([id]),
opDate datetime,
opNumber int
)
insert into t2 values(1,'2005-1-1',7)
insert into t2 values(2,'2005-1-1',3)
insert into t2 values(2,'2005-1-1',2)
insert into t2 values(2,'2005-1-1',4)


select cargo=case when b.opNumber is null or b.opNumber=(select top 1 opNumber from t2 where id=a.id)
then a.cargo else '' end
,cargonumber=case when b.opNumber is null or b.opNumber=(select top 1 opNumber from t2 where id=a.id)
then a.cargonumber else '' end
,a.instocknumber,b.opDate,b.opNumber
from t1 as a left join t2 as b on (a.[id] = b.[id])


go

drop table t2,t1

/*--结果

cargo cargonumber instocknumber opDate opNumber
----- ----------- ------------- ------------------------- ---------
A B 10 2005-01-01 00:00:00.000 7
A C 10 2005-01-01 00:00:00.000 3
10 2005-01-01 00:00:00.000 2
10 2005-01-01 00:00:00.000 4
A D 10 NULL NULL

（所影响的行数为 5 行）

--*/

[yangys]

^_^

不愧是邹建！

[Sundery]

不好意思啊，其实我要的结果是这个样子的：

A B 10 2005-01-01 00:00:00.000 7
A C 10 2005-01-01 00:00:00.000 3
10 2005-01-01 00:00:00.000 2
10 2005-01-01 00:00:00.000 4
A D 10 NULL NULL

[天地客人]

UP

[Sundery]

Thanks:)