有表1如下:
ID NAME
1 A
2 B
3 C
表2如下:
ID USER
1 01
1 02
1 01
2 02
2 03
3 02
我想得到以下的结果:
ID NAME USER
1 A 01
1 A 02
2 B 02
2 B 03
3 C 02
应该怎么写这个语句呢???
ID NAME
1 A
2 B
3 C
表2如下:
ID USER
1 01
1 02
1 01
2 02
2 03
3 02
我想得到以下的结果:
ID NAME USER
1 A 01
1 A 02
2 B 02
2 B 03
3 C 02
应该怎么写这个语句呢???
from 表1 a,表2 b where a.id=b.id
(
select distinct * from b
) as t join a on t.id = a.id
from tb1 left join tb2
on tb1.id =tb2.id
declare @表1 table (ID int,NAME varchar(1))
insert into @表1
select 1,'A' union all
select 2,'B' union all
select 3,'C'declare @表2 table (ID int,[USER] varchar(2))
insert into @表2
select 1,'01' union all
select 1,'02' union all
select 1,'01' union all
select 2,'02' union all
select 2,'03' union all
select 3,'02'select distinct a.*,b.NAME from @表2 a
left join @表1 b on a.ID=b.ID
/*
ID USER NAME
----------- ---- ----
1 01 A
1 02 A
2 02 B
2 03 B
3 02 C
*/
select distinct a.id,a.name,b.user
from 表1 a,表2 b where a.id=b.id
distinct a.id,a.name,b.user
from
a, b
where
a.id=b.id