比较SQL中的值，如果匹配返回true else false

Question

我有一个应用程序，允许用户联系对方，把它看作一个交友网站。我正在增加一些安全的接触部分，用户可以限制谁可以接触他/她通过设置一个特定的年龄/身高范围和性别。

例如，我单击UserId-2配置文件，然后调用DB，传入ID或选定的配置文件，并将ID传递给它，然后调用传入这两个值的函数。职能如下：

ALTER FUNCTION [dbo].[Can_User_Contact] 
(
  @UserId1 int = 2, -- UserId
  @UserId2 int = 1 -- Requested by Id
)
  RETURNS bit
  AS
  BEGIN
  RETURN (

  SELECT CASE WHEN up.Id IS NULL THEN 0 ELSE 1 END AS does_data_match
  FROM [user].User_Settings us
  LEFT OUTER JOIN [User].[User_Profile] up ON us.UserId = @UserId1 
  LEFT OUTER JOIN [User].[User_Details] d ON up.Id = d.UserId
  AND up.id = @UserId2 
  AND d.Height between ISNULL(us.HeightFrom, d.height) and ISNULL(us.HeightTo, d.Height) 
  AND up.Age between ISNULL(us.AgeFrom, up.age) and ISNULL(us.AgeTo, up.Age)
  AND up.Gender = ISNULL(us.Gender, up.Gender)

)  
END 

在这个函数中，我将用户it 2保存在设置表中的值与我自己的值进行比较，方法是将用户配置文件表和用户详细信息表连接起来，这样做的问题是每次执行它时，我都会收到以下消息：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

设置表中有两条记录，如下所示：

​

​

我只对第二张唱片感兴趣。

有人能说明我可能做错了什么吗？

@UserId1是配置文件Id，@UserId2是请求查看它的用户。

请记住，@UserId1的设置表中可能没有记录，而且此功能是可选的。

Answer 1

假设每个用户都有一个针对user_setting、User_Profile和User_Details的条目，这应该是可行的。

根据您的结果，它看起来像是连接条件的问题。理想情况下，某些联接条件应该是WHERE子句(如up.id = @UserId2 )的一部分。

查询

  SELECT TOP 1 CASE WHEN us.UserId IS NULL THEN 0 ELSE 1 END AS does_data_match
  FROM [User].[User_Profile] up
  INNER JOIN [User].[User_Details] d ON up.Id = d.UserId
  LEFT OUTER JOIN  [user].User_Settings us
  ON us.UserId = @UserId1 
  AND d.Height between ISNULL(us.HeightFrom, d.height) and ISNULL(us.HeightTo, d.Height) 
  AND up.Age between ISNULL(us.AgeFrom, up.age) and ISNULL(us.AgeTo, up.Age)
  AND up.Gender = ISNULL(us.Gender, up.Gender)
 WHERE up.id = @UserId2
ORDER BY up.id ASC

注意:如果假设是正确的，那么理想情况下不需要TOP。只是为了额外的安全

Answer 2

SELECT查询将返回多个行。由于您对单个值感兴趣，所以如下所示：

ALTER FUNCTION [dbo].[Can_User_Contact] 
        (
          @UserId1 int = 2, -- UserId
          @UserId2 int = 1 -- Requested by Id
        )
        RETURNS bit
        AS
        BEGIN
          RETURN 
          (
            SELECT CASE WHEN EXISTS  
            (  
                SELECT 1 FROM [user].User_Settings us1
                  LEFT OUTER JOIN [User].[User_Profile] up ON up.id = @UserId2
                  LEFT OUTER JOIN [User].[User_Details] d ON up.Id = d.UserId 
                  LEFT OUTER JOIN [user].[User_Settings] us2 ON us2.UserId=up.id
              WHERE  
                  d.Height between ISNULL(us1.HeightFrom, d.height) and ISNULL(us1.HeightTo, d.Height) 
                  AND us2.Age between ISNULL(us1.AgeFrom, up.age) and ISNULL(us1.AgeTo, up.Age)
                  AND us1.Gender = ISNULL(us2.Gender, up.Gender)
                  AND up.Id IS NOT NULL 
                  AND  us1.UserId = @UserId1
            )   THEN 1 ELSE 0 END AS does_data_match  
        END