求一条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
