Table 3-1: Transactions
Quarter Product Partner List Price
Q1-2006 A Reseller XYZ $125
Q1-2006 B Reseller PQ $50
Q1-2006 C Reseller PQ $60
Q2-2006 A Reseller PQ $120
Q2-2006 B Reseller XYZ $50
Q2-2006 C Reseller XYZ $55Table 3-2: Product FamiliesProduct Family
A Low End
B Mid Range
C High End我想把这两个表结合一下,弄成一个新表,叫uniontable,可是总是报错“Column names in each table must be unique. Column name 'product' in table 'uniontable' is specified more than once.”我已经在product前面加上表明了啊,怎么还会有这个问题呢?
create table Transaction1
(
transaction1 int primary key identity(1,1),
quarter1 varchar(20) ,
product varchar(20),
partner1 varchar(20),
listprice int
);
drop table Transaction1;
insert into Transaction1 values
('q1-2006','A','XYZ',125),
('q1-2006','B','PQ',50),
('q1-2006','C','PQ',60),
('q1-2006','A','PQ',120),
('q1-2006','B','XYZ',50),
('q1-2006','C','XYZ',55);
select * from Transaction1;create table product_family
(
product varchar(20) primary key,
family varchar(20)
);
insert into product_family values
('A','Low'),
('B','Mid'),
('C','High');
select * from product_family
select * into uniontable
from Transaction1,product_family where Transaction1.product=product_family.product;
into uniontable
from Transaction1 T
left join product_family PF on T.product = PF.product
product 名称重复
select a.Quarter,a.product as p1,a.XX,..,b.product as p2,b.XX into uniontable
from Transaction1 a,product_family b where a.product=b.product;
from Transaction1,product_family where Transaction1.product=product_family.product;把*换成具体的列名称,使用as重名列
直接把通过关联把数据插入新表
select Quarter, Product, Partner, List, Price, a.Product ,Family into uniontable
from Transaction1 a ,product_family b where a.product=b.product;
from Transaction1 a ,product_family b where a.product=b.product