select a.ID,a.VAL,b.VALE from a left join b on a.ID=b.ID;
我不想要VALE 这列值重复 体现 select a.ID,a.VAL,b.VALE from a left join b on a.ID=b.ID;得到的结果是 1 a A 1 a A 2 b B 2 b B
就是想知道SQL的连接 能不能解决我不想要重复的问题 因为我还要合计 第二张表的值
测试数据有问题,我改改,看是不是楼主需要的代码:declare @ta table(id int, val varchar(20)) insert into @ta(id,val) select 1,'a' union all select 1,'a' union all select 2,'b' union all select 2,'b'declare @tb table(id int,VALE varchar(20)) insert into @tb(id,vale) select 1,'A' union all select 2,'B'select tb.id,tb.val,VALE=case when groupid =1 then VALE else '' end from ( select ta.*,tb.VALE,groupid=ROW_NUMBER()over(partition by vale order by VAle) from @ta ta left join @tb tb on ta.id=tb.id )tb(2 行受影响) id val VALE ----------- -------------------- -------------------- 1 a A 1 a 2 b B 2 b (4 行受影响)
--> --> (Roy)生成測試數據
declare @T table([ID] int,[VAL] nvarchar(1)) Insert @T select 1,N'a' union all select 1,N'a' union all select 2,N'b' union all select 2,N'b'
declare @T2 table([ID] int,[VALE] nvarchar(1)) Insert @T2 select 1,N'A' union all select 2,N'B'
Select a.*, [VALE]=case when a.row=1 then b.[VALE] else '' end from (select *,ROW_NUMBER()over(partition by ID order by ID)as row from @T) as a inner join @T2 as b on a.ID=b.ID
是这个意思 但是 ROW_NUMBER() SQL 2000 没有这个属性
第二张表应该是 ID VALE 1 A 2 B
--> --> (Roy)生成測試數據
declare @T table([ID] int,[VAL] nvarchar(1)) Insert @T select 1,N'a' union all select 1,N'a' union all select 2,N'b' union all select 2,N'b'
declare @T2 table([ID] int,[VALE] nvarchar(1)) Insert @T2 select 1,N'A' union all select 2,N'B'
if OBJECT_ID('Tempdb..#') is not null drop table # select *,identity(int,1,1) as row into # from @TSelect a.[ID],a.[VAL], [VALE]=case when a.row=c.row then b.[VALE] else '' end from # as a inner join @T2 as b on a.ID=b.ID inner join (select ID,MIN(row) as row from # group by ID)c on a.ID=c.ID /* ID VAL VALE 1 a A 1 a 2 b B 2 b */
select a.ID,a.VAL,b.VALE from a left join b on a.ID=b.ID;得到的结果是 1 a A
1 a A
2 b B
2 b B
insert into @ta(id,val)
select 1,'a'
union all
select 1,'a'
union all
select 2,'b'
union all
select 2,'b'declare @tb table(id int,VALE varchar(20))
insert into @tb(id,vale)
select 1,'A'
union all
select 2,'B'select tb.id,tb.val,VALE=case when groupid =1 then VALE else '' end
from (
select ta.*,tb.VALE,groupid=ROW_NUMBER()over(partition by vale order by VAle) from @ta ta left join @tb tb on ta.id=tb.id
)tb(2 行受影响)
id val VALE
----------- -------------------- --------------------
1 a A
1 a
2 b B
2 b (4 行受影响)
declare @T table([ID] int,[VAL] nvarchar(1))
Insert @T
select 1,N'a' union all
select 1,N'a' union all
select 2,N'b' union all
select 2,N'b'
declare @T2 table([ID] int,[VALE] nvarchar(1))
Insert @T2
select 1,N'A' union all
select 2,N'B'
Select a.*,
[VALE]=case when a.row=1 then b.[VALE] else '' end
from (select *,ROW_NUMBER()over(partition by ID order by ID)as row from @T) as a
inner join @T2 as b on a.ID=b.ID
ID VALE
1 A
2 B
declare @T table([ID] int,[VAL] nvarchar(1))
Insert @T
select 1,N'a' union all
select 1,N'a' union all
select 2,N'b' union all
select 2,N'b'
declare @T2 table([ID] int,[VALE] nvarchar(1))
Insert @T2
select 1,N'A' union all
select 2,N'B'
if OBJECT_ID('Tempdb..#') is not null
drop table #
select *,identity(int,1,1) as row into # from @TSelect a.[ID],a.[VAL],
[VALE]=case when a.row=c.row then b.[VALE] else '' end
from # as a
inner join @T2 as b on a.ID=b.ID
inner join (select ID,MIN(row) as row from # group by ID)c on a.ID=c.ID
/*
ID VAL VALE
1 a A
1 a
2 b B
2 b
*/
return defaultPath;
}