有两个表
A:
colunm1 colunm2 colunm3
a1 a1 1
b1 b1 2
a1 c1 3
f1 f1 1
B:
colunm1 colunm2
a1 8
e2 9
能用查到这样一个结果吗
colunm1 colunm2 colunm3 colunm4
a1 a1 1 8
b1 b1 2 null
a1 c1 3 null
f1 f1 1 null
目前我使用LEFT JOIN,则colunm4中的值都会因为colunm1中的a1重复了两次所以都查询出来都又两个8,B表中的colunm1不会重复,需要在连接的时候,只出现一次,不知道如何进行查询?请各位指教有什么办法实现。
A:
colunm1 colunm2 colunm3
a1 a1 1
b1 b1 2
a1 c1 3
f1 f1 1
B:
colunm1 colunm2
a1 8
e2 9
能用查到这样一个结果吗
colunm1 colunm2 colunm3 colunm4
a1 a1 1 8
b1 b1 2 null
a1 c1 3 null
f1 f1 1 null
目前我使用LEFT JOIN,则colunm4中的值都会因为colunm1中的a1重复了两次所以都查询出来都又两个8,B表中的colunm1不会重复,需要在连接的时候,只出现一次,不知道如何进行查询?请各位指教有什么办法实现。
from a a1
left join b
on a1.colunm1=b.colunm1
and a1.colunm2=(select min(colunm2) from a where colunm1=a1.colunm2)
select a.*,b.colunm2 as colunm4
from a a1
left join b
on a1.colunm1=b.colunm1
and a1.colunm2=(select min(colunm2) from a where colunm1=a1.colunm1)or:
select a.*,b.colunm2 as colunm4
from a a1
left join b
on a1.colunm1=b.colunm1
and not exists (select 1 from a where colunm1=a1.colunm1 and colunm2<a1.colunm2)
select a.*,b.colunm2 as colunm4
from a a1 /*此 a a1语句代表什么,好像会报错*/
left join b
on a1.colunm1=b.colunm1
and not exists (select 1 from a where colunm1=a1.colunm1 and colunm2<a1.colunm2)
/*是否为select 1 * from a where colunm1=a1.colunm1*/
colunm1 varchar(10),
colunm2 varchar(10),
colunm3 int
)
go
create table b (
colunm1 varchar(10),
colunm2 int
)
insert a select 'a1' , 'a1' , 1
union all select 'b1' , 'b1' , 2
union all select 'a1' , 'c1' , 3
union all select 'f1' , 'f1' , 1
go
insert b select 'a1', 8
insert b select 'e2', 9 select c.*,b.colunm2 from
(select top 1 * from a where exists(select * from b where colunm1=a.colunm1))c inner join
b on c.colunm1=b.colunm1 union select *,null from a where not exists
( select * from (select top 1 * from a where exists(select * from b where colunm1=a.colunm1))c where c.colunm1=
a.colunm1 and c.colunm2=a.colunm2
)
from a a1 /*此 a a1语句代表什么,好像会报错*/
left join b
on a1.colunm1=b.colunm1
and not exists (select 1 from a where colunm1=a1.colunm1 and colunm2<a1.colunm2)
/*是否为select 1 * from a where colunm1=a1.colunm1*/
-------------------------------------------------------------
a1 是表a 的别名
select a.*,b.colunm2 as colunm4
from a a1 /*此 a a1语句代表什么,好像会报错*/==>a a1 <==>a as a1 表示表别名
left join b
on a1.colunm1=b.colunm1
and not exists (select 1 from a where colunm1=a1.colunm1 and colunm2<a1.colunm2)
/*是否为select 1 * from a where colunm1=a1.colunm1*/==>是为了区分两个现样的表名.
===========>报什么错,楼主贴出来,上面二楼语句是没有语法错误的.
declare @A table(colunm1 varchar(20),colunm2 varchar(20),colunm3 int)
insert @A select
'a1' ,'a1', 1 union all select
'b1' ,'b1', 2 union all select
'a1' ,'c1', 3 union all select
'f1' ,'f1', 1
declare @b TABLE(colunm1 VARCHAR(20),colunm2 VARCHAR(20))
INSERT @b SELECT
'a1', 8 union all select
'e2', 9 SELECT a.*,b.colunm2 as colunm4 from @A a left join @B b on a.colunm1=b.colunm1
and a.colunm2=(select min(colunm2) from @A where colunm1=a.colunm1)--or
SELECT a.*,b.colunm2 as colunm4 from @A a left join @B b on a.colunm1=b.colunm1
and not exists (select 1 from @A where colunm1=a.colunm1 and colunm2<a.colunm2)/*
結果:
colunm1 colunm2 colunm3 colunm4
-------------------- -------------------- ----------- --------------------
a1 a1 1 8
b1 b1 2 NULL
a1 c1 3 NULL
f1 f1 1 NULL
*/
有两个表
A:
colunm1 colunm2 colunm3
a1 2 1
b1 4 2
a1 5 3
f1 6 1
B:
colunm1 colunm2
a1 8
e2 9
能用查到这样一个结果吗
colunm1 colunm2 colunm3 colunm4
a1 2 1 8
b1 4 2 null
a1 5 3 null
f1 6 1 null
目前我使用LEFT JOIN,则colunm4中的值都会因为colunm1中的a1重复了两次所以都查询出来都又两个8,B表中的colunm1不会重复,需要在连接的时候,只出现一次,不知道如何进行查询?请各位指教有什么办法实现。
insert @A select
'a1' ,'2', 1 union all select
'b1' ,'4', 2 union all select
'a1' ,'5', 3 union all select
'f1' ,'6', 1 union all select
'b1' ,'2', 8
declare @b TABLE(colunm1 VARCHAR(20),colunm2 VARCHAR(20))
INSERT @b SELECT
'a1', 8 union all select
'b1', 11 union all select
'e2', 9 --select *,RANK() OVER(PARTITION by colunm1 order by colunm1,colunm2,colunm3) as RowNo from @ASELECT a.colunm1,a.colunm2,a.colunm3,case when RowNo=1 then b.colunm2 else null end as colunm4
from (select *,RANK() OVER(partition by colunm1 order by colunm1,colunm2,colunm3) as RowNo from @A) a
left join @B b on a.colunm1=b.colunm1
/*
結果:
colunm1 colunm2 colunm3 colunm4
-------------------- -------------------- ----------- --------------------
a1 2 1 8
a1 5 3 NULL
b1 2 8 11
b1 4 2 NULL
f1 6 1 NULL
*/
insert @A select
'a1' ,'a1', 1 union all select
'b1' ,'b1', 2 union all select
'a1' ,'c1', 3 union all select
'f1' ,'f1', 1
declare @b TABLE(colunm1 VARCHAR(20),colunm2 VARCHAR(20))
INSERT @b SELECT
'a1', 8 union all select
'e2', 9 select a.*,b.colunm2 as colunm4 from @A a left join @b b on a.colunm1 = b.colunm1 and
a.colunm2 =(select colunm1 from @b where colunm1 = a.colunm1)