日期:2014-05-18  浏览次数:20454 次

两个表的关联
表A:
aid bid
1 1
2 1
3 1
4 2
5 2

表B:
bid bkey
1 1000
2 2000
3 3000

表A和表B是一对多的关系,aid是表A的主键,bid是表B的主键。

现在想得到视图C如下:
aid bkey
1 1000
2 0
3 0
4 2000
5 0

也就是表A中,按照bid group by以后只有一条记录关联到表B的bkey,其他的显示0,有没有牛人可以告诉我该怎么做啊?
我试过用case when (aid在group by bid后的min(aid)中就显示bkey否则为0),可以实现,但是过了5分钟还是在select啊,要疯了,四千条数据而已。




------解决方案--------------------
SELECT A.AID,CASE WHEN A.BID=(SELECT MIN(BID) FROM A WHERE A.AID=AID) THEN B.BKEY ELSE 0 END FROM A,B WHERE A.BID=B.BID
------解决方案--------------------
SQL code
create table A (aid int,bid int)
insert into  A values(1 ,1)
insert into  A values(2, 1)
insert into  A values(3, 1)
insert into  A values(4 ,2)
insert into  A values(5 ,2)
create  table B(bid int,bkey int)
insert into  B values(1, 1000)
insert into  B values(2 ,2000)
insert into  B values(3 ,3000)
  ;with ct as 
 (
SELECT a.aid, b.bkey ,rn=ROW_NUMBER()over(partition by bkey order by getdate()) FROM  A a join  B b on a.bid=b.bid 
)
select * from (
select aid ,bkey from ct  where rn=1
union all
select aid ,'0' from ct where rn<>1)s order by aid


 drop table A  
  drop table B


aid         bkey
----------- -----------
1           1000
2           0
3           0
4           2000
5           0