sql - CASE WHEN EXIST 应该返回 1 时返回 0

Question

在我的 San_Imovel 表中，我有很多属性(property)，我想知道是否有一些重复(同一地址的属性(property))。所以，我得到一个地址，我确定在同一个地址中有很多属性，但在我的 CASE WHEN EXIST 中返回 0。

有人可以帮我吗？

在 CASE WHEN EXITS 中的 SELECT 语句中，如果我单独运行它，则返回 47 行。

查询

declare @cityName as varchar(100)
declare @neighborName as varchar(100)
declare @zipcode as varchar(8)
declare @complement as varchar(100)
declare @street as varchar(100)
declare @number as varchar(6)
set @cityName = 'Sao Paulo'
set @neighborName = 'Tupi'
set @zipcode = '31840050'
set @complement = '123'
set @street = 'Furquim werneck'
set @number = '5989828'
;WITH cte
AS (
    SELECT City_Id, State_Id, CityName
    FROM City
    WHERE (CityName COLLATE SQL_Latin1_General_CP1_CI_AI) LIKE '' + @cityName + ''
)
select 
CASE
WHEN EXISTS (
        SELECT San_Imovel.Imovel_Id 
        FROM San_Imovel
        WHERE San_Imovel.Number = @number 
        AND San_Imovel.ZipCode = @zipcode
        AND San_Imovel.Complement = @complement
        AND San_Imovel.Street = @street
        AND San_Imovel.City_Id = cte.City_Id
    ) 
    THEN 1
    ELSE 0
END AS ExistDuplicate
from cte

数据样本

Imovel_Id bigint
Number int
Complement varchar
Street varchar
City_Id int
Zipcode varchar

Imovel_Id     Number    Complement   Street            City_Id   ZipCode
433669        5989828   123          Furquim Werneck   2754      31840050
433670        5989828   123          Furquim Werneck   2754      31840050
433671        5989828   123          Furquim Werneck   2754      31840050

Answer 1

如果这是数据库中的数据类型，您应该将“数字”参数作为整数传递。

declare @number as int
set @number = 5989828