Question

怎样用sql server的存储过程，把几张表的内容放在同一张临时表中？

有四张表，第一张表fruits表中存储有水果的ID和名称，第二张，第三张，第四张表中存储有2010年，2011年，2012年的价钱。

我想把上面的四张表合成下面的一张表，用sql server存储过程怎样实现？

Answer 1

CREATE OR REPLACE PROCEDURE PRO_NAME AS
BEGIN
INSERT INTO TEMP_NAME
SELECT A.ID AS ID,
A.NAME AS NAME,
B.PRICE AS PRICE2010,
C.PRICE AS PRICE2011,
D.PRICE AS PRICE2012
FROM FRUITS A, PRICE2010 B, PRICE2011 C, PRICE2012 D
WHERE A.ID = B.ID
AND A.ID = C.ID
AND A.ID = D.ID;
END;

Answer 2

select a.*,b.price as price2010,c.price as price2011,d.price as price2012
from fruits a
left join price2010 b on a.id = b.id
left join price2011 c on a.id = c.id
left join price2012 d on a.id = d.id

Answer 3

select fruits.id as 'id',fruits.name as 'name',price2010.price as 'price2010',price2011.price as 'price2011',price2012.price as 'price2012' from fruits,price2010,price2011,price2012 where fruits.id=price2010.id and fruits.id=price2011 and id,fruits.id=price2012.id

Answer 4

create procedure friutprice
as
insert into temp
(
select a.id,
,a.name
, (select b.price from price2010 b where b.id = a.id) as price2010
, (select c.price from price2011 c where c.id = a.id) as price2011
, (select d.price from price2012 d where d.id = a.id) as price2012
from fruits a
)
go
;
exec friutprice;

直接写入

如果有其他逻辑 比如说判断等 你可以在存储过程中自己添加，调试