求一条SQL语句。。。。。
表1
ID name num
1 w1 60
2 w2 80
3 w3 100

表2
parentID num1
1 20
1 20
1 20
2 30
2 20


表1和表2 TID关联

要求建立下面视图
ID name num num1
1 w1 60 20
1 w1 60 20
1 w1 60 20
2 w2 80 30
2 w2 80 20
3 w3 100 0

[解决办法]
if object_id( 'pubs..tb1 ') is not null
drop table tb1
go

create table tb1(
ID varchar(10),
name varchar(10),
num int)

insert into tb1(ID,name,num) values( '1 ', 'w1 ', 60)
insert into tb1(ID,name,num) values( '2 ', 'w2 ', 80)
insert into tb1(ID,name,num) values( '3 ', 'w3 ', 100)
go

if object_id( 'pubs..tb2 ') is not null
drop table tb2
go

create table tb2(
parentID varchar(10),
num1 int)

insert into tb2(parentID,num1) values( '1 ', 20)
insert into tb2(parentID,num1) values( '1 ', 20)
insert into tb2(parentID,num1) values( '1 ', 20)
insert into tb2(parentID,num1) values( '2 ' , 30)
insert into tb2(parentID,num1) values( '2 ', 20)
go


select id1 = identity(int,1,1) , a.id , a.name , a.num , isnull(b.num1,0) num1
into test
from tb1 a
left join tb2 b on a.id = b.parentid

select a.* into test2 from test a,
(select id , min(id1) as id1 from test group by id) b
where a.id = b.id and a.id1 = b.id1

select * from
(
select id ,name , num = 0 , num1 from test where id1 not in (select id1 from test2)
union all
select id , name , num , num1 from test2
) t
order by id , num desc
drop table tb1,tb2,test,test2

id name num num1
---------- ---------- ----------- -----------
1 w1 60 20
1 w1 0 20
1 w1 0 20
2 w2 80 30
2 w2 0 20

3 w3 100 0

（所影响的行数为 6 行）


[解决办法]
借用下上的，只借用一表。

create table tb1(
ID varchar(10),
name varchar(10),
num int)

insert into tb1(ID,name,num) values( '1 ', 'w1 ', 60)
insert into tb1(ID,name,num) values( '2 ', 'w2 ', 80)
insert into tb1(ID,name,num) values( '3 ', 'w3 ', 100)
go
create table tb2(
parentID varchar(10),
num1 int)

insert into tb2(parentID,num1) values( '1 ', 20)
insert into tb2(parentID,num1) values( '1 ', 20)
insert into tb2(parentID,num1) values( '1 ', 20)
insert into tb2(parentID,num1) values( '2 ' , 30)
insert into tb2(parentID,num1) values( '2 ', 20)
go
Select ID = Identity(Int, 1, 1), * Into #T From tb2

Select
A.ID,
A.Name,
(Case When Not Exists (Select ID From #T Where parentID = B.parentID And ID < B.ID) Then A.num Else 0 End) As num,
IsNull(B.num1, 0) As num1
From
tb1 A
Left Join
#T B
On A.ID = B.parentID

Drop Table #T
GO
drop table tb1, tb2
--Result
/*
IDNamenumnum1
1w16020
1w1020
1w1020
2w28030
2w2020
3w31000
*/
[解决办法]
视图不好写，这里给个第三种用临时表的方法，效率一般
declare @t1 table(
ID varchar(10),
name varchar(10),
num int)
insert into @t1(ID,name,num) values( '1 ', 'w1 ', 60)
insert into @t1(ID,name,num) values( '2 ', 'w2 ', 80)
insert into @t1(ID,name,num) values( '3 ', 'w3 ', 100)
declare @t2 table(
parentID varchar(10),
num1 int)
insert into @t2(parentID,num1) values( '1 ', 20)
insert into @t2(parentID,num1) values( '1 ', 20)
insert into @t2(parentID,num1) values( '1 ', 20)
insert into @t2(parentID,num1) values( '2 ' , 30)
insert into @t2(parentID,num1) values( '2 ', 20)

select a.id,a.name,a.num,isnull(b.num1,0) as num1 into #t from @t1 a left join @t2 b on a.id=b.parentID order by a.id
declare @cid int,@lid int
select @cid=0,@lid=-1
update #t set @lid=@cid,@cid=id,num=case when @lid=@cid then 0 else num end
select * from #t
drop table #t