有两个表T1 , T2 如下
T1 (fid是主键)
-------
fid |
-------
1 |
2 |
3 |
4 |
-------T2(id是主键,fid是外键)
--------------
id |fid |
--------------
1 |1 |
2 |1 |
3 |2 |
4 |2 |
--------------通过连接查询 select T1.fid, T2.id, T2.fid from T1 inner join T2 on T1.fid=T2.fid 得到如下结果
---------------------
fid |id |fid |
---------------------
1 |1 |1 |
1 |2 |1 |
2 |3 |2 |
2 |4 |2 |
---------------------能否通过特定的查询方式得到下述的结果???
---------------------
fid |id |fid |
---------------------
1 |1 |1 |
2 |3 |2 |
---------------------
就是说主键TI.fid要求在查询的结果中是唯一的,虽然有多个外键T2.fid
T1 (fid是主键)
-------
fid |
-------
1 |
2 |
3 |
4 |
-------T2(id是主键,fid是外键)
--------------
id |fid |
--------------
1 |1 |
2 |1 |
3 |2 |
4 |2 |
--------------通过连接查询 select T1.fid, T2.id, T2.fid from T1 inner join T2 on T1.fid=T2.fid 得到如下结果
---------------------
fid |id |fid |
---------------------
1 |1 |1 |
1 |2 |1 |
2 |3 |2 |
2 |4 |2 |
---------------------能否通过特定的查询方式得到下述的结果???
---------------------
fid |id |fid |
---------------------
1 |1 |1 |
2 |3 |2 |
---------------------
就是说主键TI.fid要求在查询的结果中是唯一的,虽然有多个外键T2.fid
create table volitation_Tl
(
int primary key not null
)insert into volitation_Tl
select 1 union all
select 2 union all
select 3 union all
select 4create table volitation_T2
(
id int primary key not null,
fid int
)insert into volitation_T2
select 1,1 union all
select 2,1 union all
select 3,2 union all
select 4,2
select * from volitation_Tl
select * from volitation_T2select B.*, A.* from volitation_Tl A , (select fid ,min(id) as id from volitation_T2 group by fid) B
where A.fid = B.fid
fid ¦id ¦fid ¦
---------------------
1 ¦1 ¦1 ¦
2 ¦3 ¦2 ¦
---------------------
fid id fid
1 1 1
2 3 2