sql求写法
表1:
字段名:f1 f2
1 b1
2 b2
3 b3
表2:
字段名:f1 f2 f3
1 c1 1
1 c2 2
2 c3 1
我想两张表通过f1字段连成如下效果
1 b1 c2 2
2 b2 c3 1
3 b3 null null
表1:
字段名:f1 f2
1 b1
2 b2
3 b3
表2:
字段名:f1 f2 f3
1 c1 1
1 c2 2
2 c3 1
我想两张表通过f1字段连成如下效果
1 b1 c2 2
2 b2 c3 1
3 b3 null null
from t1
left join t2 on t1.f1=t2.f1
(select t.* from tb2 t where f3 = (select max(f3) from tb2 where f1 = t.f1)) n
on m.f1 = n.f1
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',1select 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 行受影响)*/
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
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)
goselect 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 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 行受影响)*/
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)
goselect 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
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',1SELECT 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 行)
-- 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 行受影响)
*/
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',1select 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
*/
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',1SELECT 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 行)
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
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 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)
表1:
字段名:f1 f2
1 b1
2 b2
3 b3
表2:
字段名:f1 f2 f3
1 c1 1
1 c2 2
1 c2 2(注意这里多一条,我也希望是如下结果)
2 c3 1
我想两张表通过f1字段连成如下效果
1 b1 c2 2
2 b2 c3 1
3 b3 null null
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',1SELECT 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 行)加个DISTINCT
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)
goselect 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
表1:
字段名:f1 f2
1 b1
2 b2
3 b3
表2:
字段名:f1 f2 f3
1 c1 2 (注意这里的数据1变为2)
1 c2 2
1 c2 2
2 c3 1
我想两张表通过f1字段连成如下效果
1 b1 c2 2
2 b2 c3 1
3 b3 null null
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',1select 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 行受影响)
*/
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)
goselect 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 你把方法看懂了,随你怎么变.