时间戳

首页 > 解决方案 > SQL Server如何判断一组值是否与另一组值不同

sql - SQL Server如何判断一组值是否与另一组值不同

问题描述

我试图确定一组值与另一组值是否不同或相同。

这看起来很简单，但我坚持解决方案。

这是数据...

declare @tv_mccvalues table(recnum int identity(1,1) primary key, mcc int, pursecandoid int, candoid int)

--CanDoId 801 the MCC values are the same
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 5001, 801)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(2234, 5001, 801)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 6001, 801)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(2234, 6001, 801)

--CanDoId 901 the MCC values are the different
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 7001, 901)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(2234, 7001, 901)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 8001, 901)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(5234, 8001, 901)

--CanDoId 1001 the MCC values are the same
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 9001, 1001)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(2234, 9001, 1001)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 10001, 1001)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(2234, 10001, 1001)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 11001, 1001)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(2234, 11001, 1001)

--CanDoId 1101 the MCC values are different
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 12001, 1101)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(2234, 12001, 1101)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 13001, 1101)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(2234, 13001, 1101)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 14001, 1101)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(6234, 14001, 1101)

--CanDoId 1201 the MCC values are the same
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 15001, 1201)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 16001, 1201)

--CanDoId 1201 the MCC values are different
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(1234, 17001, 1301)
insert into @tv_mccvalues(mcc, pursecandoid, candoid) values(7234, 18001, 1301)

主要的关键是坦率。

我正在尝试完成这样的查询...

declare @candoid int = 801

if exists(
             select pursecandoid, mcc, count(mcc) as count
             from @tv_mccvalues t
             where candoid = @candoid
             group by pursecandoid, mcc
             having count(mcc) > 1
        )
begin
   select 'they are different'
end
else
begin
   select 'the are the same'
end

任何帮助表示赞赏。

标签： sqlsql-serversql-server-2016

解决方案

好的，这是结果...

FIS 1    
Internet 0

我来自钦奈的同事想出了答案（谢谢 Abdur）。

这里是...

declare @candoid int = 1001

SELECT mcc
FROM @tv_mccvalues a
where candoid= @candoid
and  mcc != ALL
( 
   SELECT mcc 
   FROM @tv_mccvalues b 
   WHERE  PurseCandoid <> a.pursecandoid 
   and candoid = a.candoid
)

推荐阅读