代码之家 › 专栏 › 技术社区 › StepUp

通过比较查询结果查找ID

tsql sql-server sql

StepUp · 技术社区 · 7 年前

我有一个表变量 Names 其中包含以下数据:

SELECT * FROM Names

其结果是:

Name
-----
Jon
Adam 
Ben
Joseph

此外,还有一个函数 fn_GetNamesById(@Id) 它得到 Name 通过 id (一):

SELECT * FROM fn_GetColumns(1)

其结果是:

姓名
-----
乔恩
亚当
本
约瑟夫

另一个 身份证件 (2)将返回另一个结果

SELECT * FROM fn_GetColumns(2)

其结果是:

Name
-----
Adam 
Jon
Joseph
Ben

另一个 身份证件 (3)将返回另一个结果:

SELECT * FROM fn_GetColumns(3)

其结果是:

Name
-----
Marc
William 
Gordon
Wiktor
Felix

我要找的是身份证 UDF/Stored procedure 已命名 Find_ID(@IDs, @Names) 通过比较表变量的结果 姓名 函数的结果 fn_GetColumns(id) .

的参数 Stored Procedure/UDF 是:

@IDs TABLE(ID uniqueidentifier) -- possible IDs (1,2,3, ...1000)
@Names TABLE(ID uniqueidentifier) -- desired names

为了做到这一点,我写了一篇 stored procedure Find_ID 其中包含一个循环 WHILE 迭代ID .当前 身份证件 将投入使用 fn_getcolumns(id)数 然后是结果 fn_getcolumns(id)数 将与结果表变量进行比较 @Names .

但是,我不知道如何比较两个查询结果并获取最新信息 身份证件 如果查询结果相同:

-- My stored procedure
CREATE PROCEDURE [Find_ID]   (
@IDs [UWQ].[TY_MyType] READONLY,
@Names [UWQ].[TY_MyNames] READONLY

DECLARE @IDs TABLE(ID uniqueidentifier)
DECLARE @Names TABLE(ID uniqueidentifier)
DECLARE @Processed INT


DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @IDs)
DECLARE @VALUE VARCHAR(50);

--loop:
WHILE @COUNTER < @MAX
BEGIN

    --we are iterating through id = 1, 2, 3
    SET @VALUE = (SELECT ID FROM
        (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , 
            ID from @IDs) R 
         ORDER BY R.[index] OFFSET @COUNTER 
         ROWS FETCH NEXT 1 ROWS ONLY);

    // pseudo code: if  SELECT * FROM fn_GetColumns(1) is equal to SELECT * 
    // FROM Names THEN return @VALUE
    // pseudo code: else iterate to find id 
    // pseudo code: if there is no names then return NULL

    SET @COUNTER = @COUNTER + 1

END

能找到吗 身份证件 功能 fn_getcolumns(id)数 如果查询结果相同?

例如:

DECLARE @Names table 
(
    Names VARCHAR(50)
)
insert into @Names 
VALUES
    (Jon), (Adam) , (Ben), (Joseph)

DECLARE @Ids table 
(
    ID int
)
insert into @Ids 
VALUES
    (1),(2) , (3), (4)

EXEC Find_ID(@IDs, 
         @Names)

输出:1 --作为自定义项 fn_GetColumns(1) 返回相同的查询结果
喜欢 SELECT * FROM Names

2 回复 | 直到 7 年前

Gordon Linoff 7 年前

SELECT (CASE WHEN COUNT(*) = nn.cnt THEN 1 ELSE 0 END) as all_same
FROM Names n JOIN
     fn_GetColumns(@id) gc(name)
     ON n.name = gc.name CROSS JOIN
     (SELECT COUNT(*) as cnt FROM names) nn;

StepUp 7 年前

EXCEPT ID

 SELECT 
     @DesiredID = count(1) 
 FROM 
 (
     SELECT * FROM (SELECT * FROM @Names) InputNames
     EXCEPT
     SELECT * FROM (SELECT TOP 1000 Name 
     FROM fn_GetColumns(@VALUE) ORDER BY ColumnOrder) DesiredNames
 ) rn

 IF @DesiredID = 0
 BEGIN
     PRINT @DesiredID
     PRINT @VALUE
     BREAK
 END

DECLARE @IDs TABLE(ID uniqueidentifier)
DECLARE @Processed INT
INSERT INTO @IDs
SELECT 
f.ID 
FROM Fields f
GROUP BY f.ID

DECLARE @Names TABLE
(
    Name varchar(50)
)

INSERT INTO @Names
VALUES
('Jon'), ('Adam'), ('Ben'), ('Joseph')



 DECLARE @COUNTER INT = 0;
 DECLARE @MAX INT = (SELECT COUNT(*) FROM @IDs)
 DECLARE @VALUE VARCHAR(50);
 DECLARE @DesiredID VARCHAR(50);

 WHILE @COUNTER < @MAX
 BEGIN

     SET @VALUE = (SELECT ID FROM
         (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , ID from @IDs) R 
          ORDER BY R.[index] OFFSET @COUNTER 
          ROWS FETCH NEXT 1 ROWS ONLY);

          --PRINT @VALUE
         SELECT 
             @DesiredID = count(1) 
         FROM 
         (
             SELECT * FROM (SELECT * FROM @Names) InputNames
             EXCEPT
             SELECT * FROM (SELECT TOP 1000 Name 
             FROM fn_GetColumns(@VALUE) ORDER BY ColumnOrder) DesiredNames
         ) rn

         IF @DesiredID = 0
         BEGIN
             PRINT @DesiredID
             PRINT @VALUE
         BREAK
     END

     SET @COUNTER = @COUNTER + 1

END