更正一点,语句应该是:
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由于已经在显示过了,所以不要再显示出来
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由于已经在显示过了,所以不要再显示出来
(
[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
(
[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)godrop 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 行)
--*/
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
(
[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])
godrop 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 行)--*/