比较 SQL Server 和 Azure SQL 数据库中的行

引言

在本文中，我将向您展示如何在 Azure SQL 数据库和 SQL Server 2016 中轻松比较两个行。目标是创建一个查询，该查询将接受两个记录的 ID，并显示哪些列不同以及它们的值为何。您可能有一些数据库工具可以比较表的架构和内容，但如果您需要在某些应用程序中显示差异，那么添加此逻辑可能会很棘手。

背景

想象一下这个问题——我们有一个在线商店，我们在其中销售产品。用户来到我们的网站并想要比较产品（如价格、速度等），有点像

这是一个非常常见的需求，但有时，在不进行一些复杂的透视的情况下，很难将表行垂直透视并使用 SQL 查询进行比较。在本文中，我们将看到一种比较两行单元格的方法。

使用 T-SQL 比较产品

比较两个产品的最简单方法是从表中选择两行并显示它们

SELECT * FROM Production.Product
WHERE ProductID IN (3,4)

但是，这在视觉上可能难以比较，因为Product表可能有许多列

产品 ID	名称	产品 数字	Make 标志	完成 商品 标志	Color	安全性 库存 信号强度	Reorder Point	标准版 成本	列表 价格	大小	SizeUnit 测量 代码	权重 单位 测量 代码	权重	DaysTo Manufacture	产品 Line	类	样式	产品 Subcategory ID	产品 ModelID	Sell StartDate	Sell EndDate	Discontinued 日期	Modified 日期
3	BB Ball Bearing	BE-2349	1	0	NULL	800	600	0.00	0.00	NULL	NULL	NULL	NULL	1	NULL	NULL	NULL	NULL	NULL	2008-04-30 00:00:00.000	NULL	NULL	2014-02-08 10:01:36.827
4	Headset Ball Bearings	BE-2908	0	0	黑色	800	600	0.00	0.00	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	2008-04-30 00:00:00.000	NULL	NULL	2014-02-08 10:01:36.827

如果我们能返回类似垂直组织的表格，其中只有差异，比较起来会不会更容易？

字段	产品 1	产品 2
ProductID	3	4
名称	BB Ball Bearing	Headset Ball Bearings
ProductNumber	BE-2349	BE-2908
MakeFlag	True	假
DaysToManufacture	1	0

好的，让我们看看解决方案。我将创建一个表值函数，该函数接受两个 ID 并返回一个具有三列的表——来自Product表的columnname，来自第一个产品行的值，以及来自第二个产品行的值。

DROP FUNCTION IF EXISTS
Production.CompareProducts
GO
CREATE FUNCTION
Production.CompareProducts (@id1 int, @id2 int)
returns table
as
return (
    select v1.[key] as Field, v1.value as [Product 1], v2.value as [Product 2]
    from
        openjson(
            (select * from Production.Product where ProductID = @id1
             for json path, include_null_values, without_array_wrapper)) v1
    inner loop join
        openjson(
            (select * from Production.Product where ProductID = @id2
             for json path, include_null_values, without_array_wrapper)) v2
        on v1.[key] = v2.[key]
    where v1.value <> v2.value
    or v1.value is null and v2.value is not null
    or v1.value is not null and v2.value is null
)

此代码使用了 Azure SQL 数据库和 SQL Server 2016 中新增的 JSON 函数。详细信息将在下方解释。
注意：您需要将数据库的兼容级别设置为 130，因为OPENJSON函数在较低的兼容级别上无法工作。
如果您在最新 SQL Server 2016 或 Azure SQL 数据库的AdventureWorks数据库中创建此函数，您将能够使用以下代码比较产品

select * from Production.CompareProducts(3,4)

此函数与Production.Product表一起使用，但您可以轻松地将其重写以适用于任何其他表。下面是该函数的“模板化”版本，您只需在此处放入表名<<TABLE>>和主键列<<KEY>>

DROP FUNCTION IF EXISTS
Compare<<TABLE>>
GO
CREATE FUNCTION
Compare<<TABLE>> (@id1 int, @id2 int)
returns table
as
return (
    select v1.[key] as Field, v1.value as [<<TABLE>> 1], v2.value as [<<TABLE>> 2]
    from
        openjson(
            (select * from <<TABLE>> where <<KEY>> = @id1
                      for json path, include_null_values, without_array_wrapper)) v1
    inner loop join
        openjson(
            (select * from <<TABLE>> where <<KEY>> = @id2
                      for json path, include_null_values, without_array_wrapper)) v2
        on v1.[key] = v2.[key]
    where v1.value <> v2.value
    or v1.value is null and v2.value is not null
    or v1.value is not null and v2.value is null
)

如果此函数能帮到您，您可以不理解底层 JSON 函数就使用它。如果您想了解它是如何工作的，那么您可以继续阅读有关 Azure SQL 数据库中 JSON 函数的故事。

Azure SQL 数据库/SQL Server 2016 中的 JSON

Azure SQL 数据库/SQL Server 2016 提供了新的函数和运算符，使您能够将 SQL 结果格式化为 JSON 文本，并将 JSON 解析为表格式。两个主要功能是

1. FOR JSON子句可以添加到任何SELECT语句之后，它将返回 SQL 查询的结果作为 JSON 文本。
2. OPENJSON表值函数，它解析 JSON 文本并返回一个包含键值对的表。

在接下来的部分中，我们将简要介绍这些功能的工作方式。如果您需要更多详细信息，我推荐我之前的文章：Friday the 13th - JSON is coming to SQL Server。

将表数据格式化为 JSON

FOR JSON子句用于将 SQL 查询的结果输出为 JSON 文本。例如，如果我们读取Product表并希望获得 JSON 文本作为结果，您可以编写以下查询

select top 5 ProductID, Name, Color
from Production.Product
FOR JSON PATH

结果是，您将获得类似以下 JSON 的内容，而不是一组表行

[
 {"ProductID":1,"Name":"Adjustable Race"},
 {"ProductID":2,"Name":"Bearing Ball"},
 {"ProductID":3,"Name":"BB Ball Bearing"},
 {"ProductID":4,"Name":"Headset Ball Bearings","Color":"Black"},
 {"ProductID":316,"Name":"Blade"}
]

FOR JSON会将结果集转换为 JSON 数组，每一行将生成为数组中的 JSON 对象，每个单元格：值将生成为对象中的 JSON 键值对。
FOR JSON可能用于将 SQL 查询的结果返回给某些返回 JSON 的 REST 服务——请参阅使用 ASP.NET Core Web API 和 Azure SQL 数据库构建 REST 服务中的示例。

包含 NULL 值

默认情况下，SQL 数据库会从 JSON 输出中抑制所有NULL值。在前面的示例中，您可能会注意到只有ProductId 4 具有Color键，因为其他产品在该单元格中具有null值。如果您想拥有所有键值对，可以包含INCLUDE_NULL_VALUES选项，FOR JSON将生成“column name”:null如果底层单元格具有NULL值，例如

select top 5 ProductID, Name, Color
from Production.Product
FOR JSON PATH, INCLUDE_NULL_VALUES

此查询的结果可能看起来像

[
 {"ProductID":1,"Name":"Adjustable Race","Color":null},
 {"ProductID":2,"Name":"Bearing Ball","Color":null},
 {"ProductID":3,"Name":"BB Ball Bearing","Color":null},
 {"ProductID":4,"Name":"Headset Ball Bearings","Color":"Black"},
 {"ProductID":316,"Name":"Blade","Color":null}
]

正如您可能注意到的，每个 JSON 对象都有"Color":null属性。我需要在Compare函数中使用此选项，因为我需要比较null和其他值。

删除数组包装器

FOR JSON将生成 JSON 数组作为结果。但是，如果您需要返回单个 JSON 对象行，您可能不希望得到一个包含单个元素的数组。为了移除返回的 JSON 周围的数组括号，您可以指定WITHOUT_ARRAY_WRAPPER选项

select ProductID, Name, Color from Product
where ProductId = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

此查询的结果可能看起来像

{"ProductID":1,"Name":"Adjustable Race"}

您也可以在同一查询中组合INCLUDE_NULL_VALUES和WITHOUT_ARRAY_WRAPPER选项。如果您同时添加这两个选项，结果可能是

{"ProductID":1,"Name":"Adjustable Race","Color":null}

FOR JSON使您能够将查询结果转换为 JSON 文本并自定义格式。现在我们将看到反向操作——OPENJSON。

解析 JSON

在 Azure SQL 数据库和 SQL Server 2016 中，您可以使用新的OPENJSON函数，该函数解析 JSON 文本并将其转换为表格式。您可以使用标准的 SQL 语言读取OPENJSON函数的结果行。

set @json = '{"ProductID":4,"Name":"Headset Ball Bearings",
              "ProductNumber":"BE-2908","Color":"Black"}'

select [key], value
from OPENJSON(@json)

OPENJSON将在 [key] 和 value 列的表中返回输入 JSON 文本中的所有键值对

键	value
ProductID	4
名称	Headset Ball Bearings
ProductNumber	BE-2908
Color	黑色

这是标准表结果，可以使用任何标准的 T-SQL 查询进行过滤或转换。

如何使用 JSON 函数比较产品？

现在我们将逐步解释如何使用这些函数来解决比较问题。FOR JSON子句使您能够将单行转换为一组键值对

SELECT *
FROM Production.Product
WHERE ProductID = 3
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

我使用了INCLUDE_NULL_VALUES和WITHOUT_ARRAY_WRAPPER选项，因为我需要所有值（包括 nulls），并且我需要一个 JSON 对象。

我们得到一组键值对，其中每个键是列名，每个值是该行中的单元格。

{"ProductID":3,"Name":"BB Ball Bearing","ProductNumber":"BE-2349","MakeFlag":true,
"FinishedGoodsFlag":false,"Color":null,"SafetyStockLevel":800,"ReorderPoint":600,
"StandardCost":0.0000,"ListPrice":0.0000,"Size":null,"SizeUnitMeasureCode":null,
"WeightUnitMeasureCode":null,"Weight":null,"DaysToManufacture":1,"ProductLine":null,
"Class":null,"Style":null,"ProductSubcategoryID":null,"ProductModelID":null,
"SellStartDate":"2008-04-30T00:00:00","SellEndDate":null,"DiscontinuedDate":null,
"rowguid":"9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E","ModifiedDate":"2014-02-08T10:01:36.827"}

现在如果我们向OPENJSON发送此 JSON 文本，会发生什么？

SELECT [key], value
FROM OPENJSON ( (SELECT *
                 FROM Production.Product
                 WHERE ProductID = 3
                 FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) ) 

OPENJSON将解析FOR JSON子句生成的 JSON 文本，并返回一个 (key,value) 结构的表

键	value
ProductID	4
名称	Headset Ball Bearings
ProductNumber	BE-2908
Color	黑色
...	...

如果您将此结果与从单行选择数据的原始 SQL 语句进行比较，您会发现我们得到了一个转置表（列名和值是垂直组织的）。如果我们对两个不同的行使用此代码两次，我们将得到两个垂直的、转置的表。

现在，我们可以按 [key] 列联接这两个二维表，并按 [key] 列匹配这两个表的键值对

SELECT p1.[key], p1.value, p2.value
FROM OPENJSON ( (SELECT *
                        FROM Production.Product
                        WHERE ProductID = 3
                        FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) ) p1
INNER LOOP JOIN
    OPENJSON ( (SELECT *
                        FROM Production.Product
                        WHERE ProductID = 4
                        FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) ) p2
ON p1.[key] = p2.[key]

此查询将第一个OPENJSON的所有行与第二个OPENJSON的行通过列名（[key] 列）联接，并返回类似以下内容

键	value	value
ProductID	3	4
名称	BB Ball Bearing	Headset Ball Bearings
ProductNumber	BE-2349	BE-2908
MakeFlag	true	false
FinishedGoodsFlag	false	false
Color	NULL	黑色
SafetyStockLevel	800	800
ReorderPoint	600	600
StandardCost	0.0000	0.0000
ListPrice	0.0000	0.0000
…	…	…

现在我们有了所有匹配的行，匹配正确，所以我们只需要添加一个where子句来删除相同的值。
这是一个技巧——我们不能简单地说p1.value <> p2.value来返回差异！如果其中一个值是NULL，此条件将不起作用。当 SQL Server 比较NULL和value时，它不会返回false。相反，它返回 UNKNOWN 值，此行将不会显示在结果中。这就是为什么我们需要一个更复杂的条件来检查值是否不同，或者其中一个值是否是null。

where v1.value <> v2.value
or v1.value is null and v2.value is not null
or v1.value is not null and v2.value is null

最后，我们只需要泛化这个查询，将常量 3 和 4 替换为参数@id1和@id2，将其包装为表值函数，我们就得到了一个比较产品的函数。

结论

Azure SQL 数据库和新 SQL Server 2016 中的 JSON 函数使您能够轻松地将关系数据转换为 JSON，反之亦然。作为一项副作用，您可以使用它们来轻松地序列化和反序列化查询结果。

这是 Azure SQL 数据库中 JSON 功能的一个不寻常的用法，但我希望它能帮到您。

历史

• 2016 年 6 月 16 日：初始版本