表big
bigid bigname
1 big1
2 big2表small
smallid smallname bigid
11 small1 1
12 small2 1
13 small3 2
14 small4 2我想要输出的结果是:
bigid smallid bigname smallname
1 11 big1 small1
2 13 big2 small3也就是输出bigid不能重复....
bigid bigname
1 big1
2 big2表small
smallid smallname bigid
11 small1 1
12 small2 1
13 small3 2
14 small4 2我想要输出的结果是:
bigid smallid bigname smallname
1 11 big1 small1
2 13 big2 small3也就是输出bigid不能重复....
a.bigid,
b.smallid,
a.bigname,
b.smallname
from
big a,
small b
where
a.bigid=b.bigid
and
not eixists(select 1 from small where bigid=b.bigid and smallid<b.smallid)
from big a
left join small b on a.bigid = b.bigid
where not exists(select 1 from small where bigid = b.bigid and smallid < b.smallid)
if object_id('[big]') is not null drop table [big]
go
create table [big]([bigid] int,[bigname] varchar(4))
insert [big]
select 1,'big1' union all
select 2,'big2'
if object_id('[small]') is not null drop table [small]
go
create table [small]([smallid] int,[smallname] varchar(6),[bigid] int)
insert [small]
select 11,'small1',1 union all
select 12,'small2',1 union all
select 13,'small3',2 union all
select 14,'small4',2
---查询---
select
a.bigid,
b.smallid,
a.bigname,
b.smallname
from
big a,
small b
where
a.bigid=b.bigid
and
not exists(select 1 from small where bigid=b.bigid and smallid<b.smallid)---结果---
bigid smallid bigname smallname
----------- ----------- ------- ---------
1 11 big1 small1
2 13 big2 small3(所影响的行数为 2 行)
from big a,(select * from small c where not exists(select 1 from small where bigid=c.bigid and smallid>c.smallid) b
insert into big values(1,'big1')
insert into big values(2,'big2')create table small(smallid int,smallname varchar(10),bigid int)
insert into small values(11,'small1',1)
insert into small values(12,'small2',1)
insert into small values(13,'small3',2)
insert into small values(14,'small4',2)
select * from small a left join big b on a.bigid= b.bigid
where not exists(select 1 from small c
left join big d on c.bigid= d.bigid where a.bigid=c.bigid
and a.smallid >c.smallid)
/*
smallid smallname bigid bigid bigname
----------- ---------- ----------- ----------- ----------
11 small1 1 1 big1
13 small3 2 2 big2(2 行受影响)
*/
from big a,(select * from small c where not exists(select 1 from small where bigid=c.bigid and smallid>c.smallid) b