SQL Server等效于使用JOIN以避免重复的列 [英] SQL Server equivalent to Using for a JOIN to avoid duplicate columns

问题描述

我正在寻找一种在PK上连接关系表的方法，类似于MySQL使用"，以便消除重复的列.

I'm looking for a way to join relational tables on a PK similar to MySQL "using" so that I can eliminate duplicate columns.

这里是一个例子:

select * 
from MovieHasDirector
join Director on MovieHasDirector.directorID = Director.directorID
where Director.name
like '%Steven Spiel%'

返回:

3818    976 976 Steven Spielberg
3962    976 976 Steven Spielberg
4317    976 976 Steven Spielberg
4715    976 976 Steven Spielberg

当我真正想要的是:

3818    976 Steven Spielberg
3962    976 Steven Spielberg
4317    976 Steven Spielberg
4715    976 Steven Spielberg

您可以看到重复的DirectorID列消失了.在MySQL中，您可以使用using(directorID)代替ON DirectorID = DirectorID

You can see the duplicate directorID column is gone. In MySQL you can do this with the using(directorID) instead of the ON directorID = directorID

我当然要这样做，而不必手动指定Select MovieHasDirector.movi​​eID，Director.*我希望返回的记录与相同的列重叠.我该怎么办?

Of course I am trying to do this without having to manually specify the Select MovieHasDirector.movieID, Director.* I want the returned records to overlap the columns that are the same. How can I do this?

推荐答案

在SQL Server中无法做到这一点，必须指定要返回的列名:

You cannot do that in SQL Server, you have to specify column names that you want to return:

select MovieHasDirector.column1, MovieHasDirector.column2, Director.column1
from MovieHasDirector
join Director on MovieHasDirector.directorID = Director.directorID
where Director.name
like '%Steven Spiel%'

您可以使用表别名使其变得更简单:

You can use table aliases to make it a bit easier:

select M.column1, M.column2, D.column1
from MovieHasDirector M
join Director D on M.directorID = D.directorID
where D.name
like '%Steven Spiel%'

这篇关于SQL Server等效于使用JOIN以避免重复的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！