高手帮忙,表之间关联的问题
我有三个表a,b,c
a为主表,B,C与a为多对一的关系,,就是与A主关键字的记录,在B,C中各有多条..
有没有办法写一个关联,a为主记录,与B,C中的其中一条相对应.
我就是直接写关聊,得到的收果就是
id tempfi1 tempfi2
11222512273
11222512273
11222512273
21222512273
31222512273
41222512273
41222512273
会有重复的ID,,,请问这个关联应该怎么写
[解决办法]
select id1 = identity(int,1,1) , * into B1 from B
select id1 = identity(int,1,1) , * into C1 from C
select a.* , p.* , q.* from a,
(
select m.* from b1 m,
(select id , min(id1) as id1 from b1 group by id) n
where m.id = n.id and m.id1 = n.id1
) p,
(
select m.* from c1 m,
(select id , min(id1) as id1 from c1 group by id) n
where m.id = n.id and m.id1 = n.id1
) q
where a.id = p.id and a.id = q.id
以上方法如果你的B,C表能确定某个字段能取到最大或最小值,可以不要临时表.
[解决办法]
先 把 bc两表 处理再 join
example :
select * from a
join (select distinct * from b ) bb on a.id =bb.id
join (select distinct * from C ) cc on a.id =cc.id
