tSql - 使用条件从查询结果中分配多个变量

Question

假设查询

SELECT RecordType, COUNT(*) RecordsCount FROM Records AS WHERE ... GROUP BY RecordType返回此表：

------------------------------
|RecordType   | RecordsCount |
------------------------------
|      1       |    15       |
------------------------------
|      2       |    10       |
------------------------------
|      3       |    8        |
------------------------------

我已经定义了这些变量：

DECLARE @paymentRecordCount INT = 0;
DECLARE @dataRecordCount INT = 0;

我正在寻找一种方法来立即设置两个变量 -@paymentRecordCount变量设置为条件where RecordType = 1的结果，并将@dataRecordCount变量设置为条件where RecordType = 3的结果。

到目前为止，我发现的唯一方法是多次计算选择查询，如下所示：select @dataRecordCount = RecordsCount from (select ... from ..) where RecordType = 3并对另一个变量执行相同的操作，例如：select @paymentRecordCount = RecordsCount from (select ... from ..) where RecordType = 1

有没有办法计算查询一次并将两个变量一起设置？

Answer 1

SELECT
@paymentRecordCount = SUM(CASE WHEN RecordType = 1 THEN 1 END),
@dataRecordCount    = SUM(CASE WHEN RecordType = 3 THEN 1 END)
FROM
Records
WHERE
... 
AND RecordType IN (1, 3)

Answer 2

您可以使用每RecordType一个子查询的语句，但是您必须考虑(如果(每个RecordType有多个值的情况：

declare @table Table (recordtype int, recordscount int)
insert @table values (1, 1);
declare @dataRecordCount int, @paymentRecordCount int;
SELECT
@paymentRecordCount = (SELECT RecordsCount FROM @table WHERE RecordType=1),
@dataRecordCount = (SELECT RecordsCount FROM @table WHERE RecordType=3);

SELECT @dataRecordCount, @paymentRecordCount;