三个表 T1 T2 T3 内容大致如下。T1:
----------------------
ID UID
1 123
2 124
3 125
-------------------
ID主键、UID 必定不重复。T2:
-------------------------
ID UID GID
1 123 789
2 123 790
3 123 801
4 124 790
5 125 802
6 125 801
-------------------------
ID 主键 UID、GID 可能都存在重复, 但是 UID、GID 同时重复的记录不会存在。T3:
--------------------------
ID GID NAME
1 789 Aa
2 790 Ab
3 801 Ac
4 802 Ad
5 803 Ae
---------------------------
ID 主键 GID 必定不重复 NAME 有可能重复。
现在我想 获得最后效果是。根据 T1 中UID in (123,125)
T1.UID = T2.UID
取出 T2中 不重复的GID然后
T2.GID = T3.GID
取出 T3中的NAME。
希望用一条SQL 语句搞定。 谢谢各位了。
----------------------
ID UID
1 123
2 124
3 125
-------------------
ID主键、UID 必定不重复。T2:
-------------------------
ID UID GID
1 123 789
2 123 790
3 123 801
4 124 790
5 125 802
6 125 801
-------------------------
ID 主键 UID、GID 可能都存在重复, 但是 UID、GID 同时重复的记录不会存在。T3:
--------------------------
ID GID NAME
1 789 Aa
2 790 Ab
3 801 Ac
4 802 Ad
5 803 Ae
---------------------------
ID 主键 GID 必定不重复 NAME 有可能重复。
现在我想 获得最后效果是。根据 T1 中UID in (123,125)
T1.UID = T2.UID
取出 T2中 不重复的GID然后
T2.GID = T3.GID
取出 T3中的NAME。
希望用一条SQL 语句搞定。 谢谢各位了。
inner join t2
on t3.gid = t2.gid
inner join t1
on t1.uid = t2.uid
where t1.uid in ('123','125');
select t3.Name from t3
inner join
(
select distinct t2.gid from t2
inner join t1
on t1.uid = t2.uid
where t1.uid in ('123','125')
)as b
on t3.gid = b.gid
create table T1
(
ID int,
[UID] int
)insert into T1
select 1,123
union all
select 2,124
union all
select 3,125create table T2
(
ID int,
[UID] int,
GID int
)
insert into T2
select 1,123,789
union all
select 2,123,790
union all
select 3,123,801
union all
select 4,124,790
union all
select 5,125,802
union all
select 6,125,801 create table T3
(
ID int,
GID int,
[Name] varchar(10)
)
insert into T3
select
1,789,'Aa'
union all select
2,790,'Ab'
union all select
3,801,'Ac'
union all select
4,802,'Ad'
union all select
5,803,'Ae' select t3.name
from
(
select gid
from T1 left join T2 on t1.uid=t2.uid
group by gid
)a left join
T3 on a.gid=t3.gid
----------
Aa
Ab
Ac
Ad
from
(
select gid
from T1 left join T2 on t1.uid=t2.uid
where t1.uid in(123,125)
group by gid
)a left join
T3 on a.gid=t3.gid
----------
Aa
Ab
Ac
Ad
inner join
(select distinct t2.gid from t2
inner join t1
on t1.gid=t2.gid //查询不重复的t2.gid值并且t2.gid='123','125'
where t1.gid in('133','125') as b
on t3.gid=b.gid
(
ID int,
[UID] int
)insert into T1
select 1,123
union all
select 2,124
union all
select 3,125create table T2
(
ID int,
[UID] int,
GID int
)
insert into T2
select 1,123,789
union all
select 2,123,790
union all
select 3,123,801
union all
select 4,124,790
union all
select 5,125,802
union all
select 6,125,801 create table T3
(
ID int,
GID int,
[Name] varchar(10)
)
insert into T3
select
1,789,'Aa'
union all select
2,790,'Ab'
union all select
3,801,'Ac'
union all select
4,802,'Ad'
union all select
5,803,'Ae' select t3.name
from
(
select gid
from T1 left join T2 on t1.uid=t2.uid
group by gid
)a left join
T3 on a.gid=t3.gid
----------
Aa
Ab
Ac
Ad这个对的