34,576
社区成员
发帖
与我相关
我的任务
分享
create table tb1(f1 int, f2 varchar(10))
insert into tb1 values(1 , 'b1')
insert into tb1 values(2 , 'b2')
insert into tb1 values(3 , 'b3')
create table tb2(f1 int, f2 varchar(10), f3 int)
insert into tb2 values(1 , 'c1' , 2)
insert into tb2 values(1 , 'c2' , 2)
insert into tb2 values(1 , 'c2' , 2)
insert into tb2 values(2 , 'c3' , 1)
go
select m.f1 , m.f2 , n.f2 , n.f3 from tb1 m left join
(select distinct t.* from tb2 t where not exists (select 1 from tb2 where f1 = t.f1 and f2 > t.f2)) n
on m.f1 = n.f1 order by m.f1
/*
f1 f2 f2 f3
----------- ---------- ---------- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(所影响的行数为 3 行)
*/
drop table tb1 , tb2
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([f1] int,[f2] varchar(2))
insert [t1]
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([f1] int,[f2] varchar(2),[f3] int)
insert [t2]
select 1,'c1',2 union all
select 1,'c2',2 union all
select 1,'c2',2 union all
select 2,'c3',1
select t1.*,t2.f2,t2.f3
from t1
left join (select f1,max(f2) as f2,max(f3) as f3 from t2 group by f1)t2
on t1.f1=t2.f1
--测试结果:
/*
f1 f2 f2 f3
----------- ---- ---- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(3 行受影响)
*/
create table tb1(f1 int, f2 varchar(10))
insert into tb1 values(1 , 'b1')
insert into tb1 values(2 , 'b2')
insert into tb1 values(3 , 'b3')
create table tb2(f1 int, f2 varchar(10), f3 int)
insert into tb2 values(1 , 'c1' , 1)
insert into tb2 values(1 , 'c2' , 2)
insert into tb2 values(1 , 'c2' , 2)
insert into tb2 values(2 , 'c3' , 1)
go
select m.f1 , m.f2,n.f2 , n.f3 from tb1 m left join
(select distinct t.* from tb2 t where f3 = (select max(f3) from tb2 where f1 = t.f1)) n
on m.f1 = n.f1 order by m.f1
/*
f1 f2 f2 f3
----------- ---------- ---------- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(所影响的行数为 3 行)
*/
select m.f1 , m.f2 , n.f2 , n.f3 from tb1 m left join
(select distinct t.* from tb2 t where not exists (select 1 from tb2 where f1 = t.f1 and f3 > t.f3)) n
on m.f1 = n.f1 order by m.f1
/*
f1 f2 f2 f3
----------- ---------- ---------- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(所影响的行数为 3 行)
*/
drop table tb1 , tb2
if object_id('[A]') is not null drop table [A]
go
create table [A]([f1] int,[f2] varchar(2))
insert [A]
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'
if object_id('[B]') is not null drop table [B]
go
create table [B]([f1] int,[f2] varchar(2),[f3] int)
insert [B]
select 1,'c1',1 union all
select 1,'c2',2 union all
select 1,'c2',2 union all
select 2,'c3',1
SELECT A.*,B.F2,B.F3
FROM A
LEFT JOIN
(SELECT DISTINCT T.* FROM B T WHERE NOT EXISTS(SELECT 1 FROM B WHERE F1=T.F1 AND F3>T.F3))AS B
ON A.F1=B.F1
(所影响的行数为 3 行)
(所影响的行数为 4 行)
f1 f2 F2 F3
----------- ---- ---- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(所影响的行数为 3 行)
select a.f1, a.f2, b.f2, b.f3 from 表1 a left join 表2 b
on a.f1=b.f1 where not exists (select 1 from 表2 where f1=b.f1 and f3>b.f3)
select m.f1 , n.f2 , n.f3 from tb1 m left join
(select t.* from tb2 t where f3 = (select max(f3) from tb2 where f1 = t.f1)) n
on m.f1 = n.f1 order by m.f1
select tab1.f1, tab1.f2, t2.f2, t2.f3
from tab1 left join
(select * from tab2 t where not exists (select 1 from tab2 where f1=t.f1 and f3>t.f3)) t2
on tab1.f1=t2.f1
if object_id('[A]') is not null drop table [A]
go
create table [A]([f1] int,[f2] varchar(2))
insert [A]
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'
if object_id('[B]') is not null drop table [B]
go
create table [B]([f1] int,[f2] varchar(2),[f3] int)
insert [B]
select 1,'c1',1 union all
select 1,'c2',2 union all
select 2,'c3',1
SELECT A.*,B.F2,B.F3
FROM A
LEFT JOIN
(SELECT T.* FROM B T WHERE NOT EXISTS(SELECT 1 FROM B WHERE F1=T.F1 AND F3>T.F3))AS B
ON A.F1=B.F1
/*
(所影响的行数为 3 行)
(所影响的行数为 3 行)
f1 f2 F2 F3
----------- ---- ---- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(所影响的行数为 3 行)
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([f1] int,[f2] varchar(2))
insert [t1]
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([f1] int,[f2] varchar(2),[f3] int)
insert [t2]
select 1,'c1',1 union all
select 1,'c2',2 union all
select 2,'c3',1
select t1.*,t2.f2,t2.f3
from t1
left join
(select * from t2 t where not exists(select 1 from t2 where t.f1=t2.f1 and t.f3<t2.f3)) t2 on t1.f1=t2.f1
/*
f1 f2 f2 f3
----------- ---- ---- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-19 10:28:02
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[表1]
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([f1] int,[f2] varchar(2))
insert [表1]
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'
--> 测试数据:[表2]
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([f1] int,[f2] varchar(2),[f3] int)
insert [表2]
select 1,'c1',1 union all
select 1,'c2',2 union all
select 2,'c3',1
--------------开始查询--------------------------
;with f as
(
select a.*,b.f2 as bf2,b.f3 from 表1 a left join 表2 b on a.f1=b.f1
)
select * from f t where not exists(select 1 from f where f1=t.f1 and f2=t.f2 and f3>t.f3)
----------------结果----------------------------
/* f1 f2 bf2 f3
----------- ---- ---- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(3 行受影响)
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([f1] int,[f2] varchar(2))
insert [A]
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'
if object_id('[B]') is not null drop table [B]
go
create table [B]([f1] int,[f2] varchar(2),[f3] int)
insert [B]
select 1,'c1',1 union all
select 1,'c2',2 union all
select 2,'c3',1
SELECT A.*,B.F3
FROM A
LEFT JOIN
(SELECT T.* FROM B T WHERE NOT EXISTS(SELECT 1 FROM B WHERE F1=T.F1 AND F3>T.F3))AS B
ON A.F1=B.F1
(所影响的行数为 3 行)
(所影响的行数为 3 行)
f1 f2 F3
----------- ---- -----------
1 b1 2
2 b2 1
3 b3 NULL
(所影响的行数为 3 行)
create table tb1(f1 int, f2 varchar(10))
insert into tb1 values(1 , 'b1')
insert into tb1 values(2 , 'b2')
insert into tb1 values(3 , 'b3')
create table tb2(f1 int, f2 varchar(10), f3 int)
insert into tb2 values(1 , 'c1' , 1)
insert into tb2 values(1 , 'c2' , 2)
insert into tb2 values(2 , 'c3' , 1)
go
select m.f1 , m.f2,n.f2 , n.f3 from tb1 m left join
(select t.* from tb2 t where f3 = (select max(f3) from tb2 where f1 = t.f1)) n
on m.f1 = n.f1 order by m.f1
/*
f1 f2 f2 f3
----------- ---------- ---------- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(所影响的行数为 3 行)
*/
select m.f1 , m.f2 , n.f2 , n.f3 from tb1 m left join
(select t.* from tb2 t where not exists (select 1 from tb2 where f1 = t.f1 and f3 > t.f3)) n
on m.f1 = n.f1 order by m.f1
/*
f1 f2 f2 f3
----------- ---------- ---------- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(所影响的行数为 3 行)
*/
drop table tb1 , tb2
select t1.*,t2.f2,t2.f3
from t1
left join t2 on t1.f1=t2.f1
and not exists(select 1 from t2 t where t.f1=t2.f1 and t.f3>t2.f3)
--测试结果:
/*
f1 f2 f2 f3
----------- ---- ---- -----------
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(3 行受影响)
*/
create table tb1(f1 int, f2 varchar(10))
insert into tb1 values(1 , 'b1')
insert into tb1 values(2 , 'b2')
insert into tb1 values(3 , 'b3')
create table tb2(f1 int, f2 varchar(10), f3 int)
insert into tb2 values(1 , 'c1' , 1)
insert into tb2 values(1 , 'c2' , 2)
insert into tb2 values(2 , 'c3' , 1)
go
select m.f1 , n.f2 , n.f3 from tb1 m left join
(select t.* from tb2 t where f3 = (select max(f3) from tb2 where f1 = t.f1)) n
on m.f1 = n.f1 order by m.f1
/*
f1 f2 f3
----------- ---------- -----------
1 c2 2
2 c3 1
3 NULL NULL
(所影响的行数为 3 行)
*/
select m.f1 , n.f2 , n.f3 from tb1 m left join
(select t.* from tb2 t where not exists (select 1 from tb2 where f1 = t.f1 and f3 > t.f3)) n
on m.f1 = n.f1 order by m.f1
/*
f1 f2 f3
----------- ---------- -----------
1 c2 2
2 c3 1
3 NULL NULL
(所影响的行数为 3 行)
*/
drop table tb1 , tb2
SELECT A.*,B.F3 FROM A
LEFT JOIN (SELECT B.* FROM B T WHERE NOT EXISTS(SELECT 1 FROM B WHERE F1=T.F1 AND F3>T.F3))AS B
ON A.F1=B.F1
select a.f1,a.f2,b.f2,max(b.f3) from 表1 a left join 表2 b on a.f1=b.f1
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([f1] int,[f2] varchar(2))
insert [t1]
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([f1] int,[f2] varchar(2),[f3] int)
insert [t2]
select 1,'c1',1 union all
select 1,'c2',2 union all
select 2,'c3',1
select t1.*,t2.f2,t2.f3
from t1
left join t2 on t1.f1=t2.f1
--测试结果:
/*
f1 f2 f2 f3
----------- ---- ---- -----------
1 b1 c1 1
1 b1 c2 2
2 b2 c3 1
3 b3 NULL NULL
(4 行受影响)
*/
select a.f1,a.f2,b.f2,max(b.f3) from 表1 a left join 表2 b