问题描述
我正在寻找一种在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.movieID,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以避免重复的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!