declare @id varchar(10) declare @name1 varchar(50) declare @name2 varchar(50) declare @name3 varchar(50)create table #table3 ( id varchar(10), name1 varchar(50), name2 varchar(50), name3 varchar(50) )declare cur cursor for select id from a open cur Fetch next From Cur into @id while @@fetch_status=0 Beginselect @name1=[name] from a where id=@id select @name2=expenseB from b where id=@id select @name3=expenseC from c where id=@idinsert into #table3(id,[name1],[name2],[name3]) values (@id,@name1,@name2,@name3) Fetch Next From Cur Into @id end close cur Deallocate Cur
--楼主试试union all + 行转列 select name ,[expreseB] = case when [type] = 'B' then exprese end ,[expreseC] = case when [type] = 'C' then exprese end from ( select a.name, [exprese] = b.expreseB, [type] = 'B' from a left join b on a.id = b.id union all select a.name, [exprese] = b.expreseC, [type] = 'C' from a left join b on a.id = b.id ) a
--copy快了,修改一下 --楼主试试union all + 行转列 select name ,[expreseB] = case when [type] = 'B' then exprese end ,[expreseC] = case when [type] = 'C' then exprese end from ( select a.name, [exprese] = b.expreseB, [type] = 'B' from a left join b on a.id = b.id union all select a.name, [exprese] = c.expreseC, [type] = 'C' from a left join c on a.id = c.id ) a
select a.name ,b.expensB,c.expenseC from a outer apply(select expensB from b where a.id = b.id) b outer apply(select expensB from c where a.id = c.id) c
select a.name ,b.expensB,c.expenseC from a outer apply(select expensB from b where a.id = b.id) b outer apply(select expensC from c where a.id = c.id) cMODIFY
from a
inner join b on a.id = b.id
inner join c on a.id =c.id
a.name ,b.expensB,c.expenseC
from
a,b,c
where
a.id=b.id
and
b.id=c.id
from a left join b on a.id=b.id
left join c on a.id=c.id
from a
join
b
on a.id = b.id
join
c
on a.id =c.id
left join b on a.id = b.id
left join c on a.id = c.id
From [a],[b],[c]
Where [a.ID]=[B.ID]
and [b.id]=[c.ID]
From [a],[b],[c]
Where [a.ID]=[B.ID]
and [b.id]=[c.ID]
得到的结果如下不能得到null
A 1.00 1.00
A 2.00 1.00
declare @name1 varchar(50)
declare @name2 varchar(50)
declare @name3 varchar(50)create table #table3
(
id varchar(10),
name1 varchar(50),
name2 varchar(50),
name3 varchar(50)
)declare cur cursor for
select id from a
open cur
Fetch next From Cur into @id
while @@fetch_status=0
Beginselect @name1=[name] from a where id=@id
select @name2=expenseB from b where id=@id
select @name3=expenseC from c where id=@idinsert into #table3(id,[name1],[name2],[name3]) values (@id,@name1,@name2,@name3) Fetch Next From Cur Into @id
end
close cur
Deallocate Cur
--楼主试试union all + 行转列
select
name
,[expreseB] = case when [type] = 'B' then exprese end
,[expreseC] = case when [type] = 'C' then exprese end
from
(
select a.name, [exprese] = b.expreseB, [type] = 'B' from a left join b on a.id = b.id
union all
select a.name, [exprese] = b.expreseC, [type] = 'C' from a left join b on a.id = b.id
) a
--copy快了,修改一下
--楼主试试union all + 行转列
select
name
,[expreseB] = case when [type] = 'B' then exprese end
,[expreseC] = case when [type] = 'C' then exprese end
from
(
select a.name, [exprese] = b.expreseB, [type] = 'B' from a left join b on a.id = b.id
union all
select a.name, [exprese] = c.expreseC, [type] = 'C' from a left join c on a.id = c.id
) a
from a
outer apply(select expensB from b where a.id = b.id) b
outer apply(select expensB from c where a.id = c.id) c
select a.name ,b.expensB,c.expenseC
from a
outer apply(select expensB from b where a.id = b.id) b
outer apply(select expensC from c where a.id = c.id) cMODIFY
select a.name,
expenseB = (select expenseB from b where b.id = a.id) ,
expenseC = (select expenseC from c where c.id = a.id)
from a不过你最好:最好给出完整的表结构,测试数据,计算方法和正确结果.发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
十分感谢13楼,此法正解
name expenseB expensec
TT 32.00 NULL
TT 21.00 NULL
TT NULL 60.00
终于达到目的了