如何处理由查询定义的变量的多行？

Question

我正在尝试编写一个查询，该查询使用变量输入子查询。

查询：

DECLARE @Code VARCHAR(5)
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

SET @Code = (SELECT Code
FROM LOOKUP.Table1
WHERE Key = @Key)

SET @Sys = (SELECT DISTINCT System
FROM LOOKUP.Table1
WHERE Key = @Key)


SELECT
@Code AS Code,
(SELECT DISTINCT 
        COUNT(ID) 
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H] 
    WHERE [Type] NOT LIKE 'X%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS Total,

(SELECT
        COUNT(Type) 
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H] 
    WHERE [Type] LIKE 'Y%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS TypeA,

(SELECT
        COUNT(Type) 
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H] 
    WHERE [Type] LIKE 'N%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS TypeB

当[Key]用于[Code]的多个值时，就会出现问题：

MSG512、级别16、状态1、第7行子查询返回的值超过一个。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时，这是不允许的。

有些有多达5个值。当SET @Code返回一个以上的值时，我想不出如何使这个查询工作。

Answer 1

它可能有助于改变你从一开始就处理问题的方式。有很多子查询，它们都访问相同的远程表，并且几乎都具有相同的WHERE条件。与其将最终结果看作是一个单独的片段集合，不如从一个大数据集的角度来考虑它，并且您希望在每一行移动一次时总结出有多少行符合特定的条件。

假设问题中的代码没有过度简化，您应该能够执行以下操作，它使用一个表变量来保存每个给定“键”的多个“代码”值。一旦我们捕获了这些信息，我们就可以简单地将其加入到远程表中。我们加入了所有子查询所共有的条件，然后将唯一的条件放在IIF()中。

另外，我在DISTINCT查找中去掉了@Sys，因为它只是在那里，因为SELECT在一个不必要的子查询中。虽然您可以使用TOP 1而不是DISTINCT，但是没有理由强制使用一种类型，因为每次返回的值都是相同的，因此流程不会注意到@Sys可能被覆盖5次(相同的值)。当然，在原始查询中没有使用@Sys变量，所以也许可以完全删除它？

DECLARE @Codes TABLE (Code VARCHAR(5) NOT NULL);
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

INSERT INTO @Codes (Code)
  SELECT Code
  FROM   LOOKUP.Table1
  WHERE  [Key] = @Key;

-- @Sys is not used in the final query. Should this be removed?
SELECT @Sys = [System] -- multiple rows may return,
FROM LOOKUP.Table1     -- but they all have the same value
WHERE [Key] = @Key;


SELECT cd.Code,
       SUM(IIF(th.[Type] NOT LIKE 'X%', 1, 0)) AS [Total],
       SUM(IIF(th.[Type] LIKE 'Y%', 1, 0)) AS [TypeA],
       SUM(IIF(th.[Type] LIKE 'N%', 1, 0)) AS [TypeB]
FROM   @Codes cd
INNER JOIN [LINKED-SERVER].[DB].[SCHEMA].[Table_H] th
        ON th.[Key] = @Key
       AND th.[Code] = cd.[Code]
GROUP BY th.[Code];

Answer 2

我会使用openquery模式。例：

select * from openquery(<<LINKED_SERVER_NAME>>,'your sql query goes here')

它为您提供了更大的灵活性，因为您可以在链接服务器定义中指定的服务器上运行动态sql查询。

Answer 3

您可以使用top 1来确保它只输出1项(例如下面的例子)，但是最好添加进一步的筛选，以便只带来真正需要的内容。

SET @Code = (SELECT top 1 Code
FROM LOOKUP.Table1
WHERE Key = @Key)