T-SQL-如何编写条件联接 [英] T-SQL - How to write a conditional join

问题描述

我有一个带有许多参数的存储过程.我想编写查询，以便它与某些表联接，但前提是特定参数具有值.以以下示例为例:我有一个Person表.还有一个包含个人地址的地址表和一个包含个人组的组表.两者都是与Person表的一对多关系.我的存储过程有一个@AddressID参数和一个@GroupID参数.

I have a stored procedure with a number of parameters. I would like to write my query so that it joins with certain tables but only if a particular parameter has a value. Take the following example: I have a Person table. There is also an Address table which holds Person Addresses and a Groups table that holds Person Groups. Both are one to many relationships with the Person table. My stored procedure has an @AddressID parameter and a @GroupID parameter.

查询总是只返回Person表中的字段.如果两个参数都没有值，则查询应返回Person表中的所有记录.如果提供了@AddressID参数，则它应仅返回在地址表中具有匹配记录的记录，而忽略组"表.如果提供了@GroupID参数，则它应仅返回在Groups表中具有匹配记录的记录，而忽略Addresses表.如果同时提供了两个参数，则它应仅在两个表中显示具有匹配记录的记录.有道理吗?

The query always just returns fields from the Person table. If neither parameter has a value then the query should return all records from the Person table. If the @AddressID parameter is supplied then it should return only records that have a matching record in the Address table and ignore the Groups table. If the @GroupID parameter is supplied then it should return only records that have a matching record in the Groups table and ignore the Addresses table. If both parameters are supplied then it should only show records that have a matching record in both tables. Make sense?

有没有一种简单的方法可以使我丢失?

Is there a simple way to do this that I am missing?

谢谢， 科里

推荐答案

如果我正确理解，听起来您的加入条件相当于
ON ((@AddressID IS NOT NULL) AND (alias.column = @AddressID))，同样也适用于组联接.

If I understand correctly it sounds like your join conditions would be the equivalent of
ON ((@AddressID IS NOT NULL) AND (alias.column = @AddressID)) and likewise for the group join.

我有时使用此条件连接.

I use this conditional join at times.

这篇关于T-SQL-如何编写条件联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！