mysql - SQL 内连接使用

Question

我有一个连接许多表的查询，所有这些表都有一个名为 businessetityid 的列。所以这个列重复了很多次。有没有简单的方法来处理这个问题。这是我的查询:

SELECT * FROM person p 
inner join personemailaddress e on p.BusinessEntityID=e.BusinessEntityID
inner join personpassword ps on p.BusinessEntityID=ps.BusinessEntityID
inner join personphone ph on p.BusinessEntityID=ph.BusinessEntityID
inner join 
( 
select BusinessEntityID from  businessentityaddress b
inner join address a on b.AdressID=a.AdressID
)  ad on ad.BusinessEntityID=p.BusinessEntityID

INTO OUTFILE 'D:\forMongo.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

我只想在 mongodb.csv 文件中获取一个 businessentityid 列。

Answer 1

根据 JOIN Syntax 记录(强调):

The following list provides more detail about several effects of current join processing versus join processing in older versions. The term “previously” means “prior to MySQL 5.0.12.”

• The columns of a NATURAL join or a USING join may be different from previously. Specifically, redundant output columns no longer appear, and the order of columns for SELECT * expansion may be different from before.

因此，如果您使用 USING 而不是 ON 指定连接条件，重复的列将被删除:

SELECT *
FROM   person             p
  JOIN personemailaddress e  USING (BusinessEntityID)
  JOIN personpassword     ps USING (BusinessEntityID)
  JOIN personphone        ph USING (BusinessEntityID)
  JOIN (
         SELECT BusinessEntityID
         FROM   businessentityaddress b
           JOIN address               a USING (AdressID)
       ) USING (BusinessEntityID)
INTO   OUTFILE 'D:\\forMongo.csv'
FIELDS TERMINATED BY ','
       ENCLOSED   BY '"'
LINES  TERMINATED BY '\n'