如何比較同一表中的兩行并使用存盤程序回傳資料作為回應

我必須使用同一個表中的兩個 Id 在兩行之間進行比較，我想獲取存盤程序中不匹配的列及其值，我需要以 JSON 格式回傳它。

     |Col1|Col2|Col3|Col4|
Id-1 |ABC |123 |321 |111 |
Id-2 |ABC |333 |321 |123|

輸出：

     |col2|col4|
Id-1 |123 |111 |
Id-2 |333 |123 |


JSON OUTPUT Expected

[
   {
      "ColumnName":"COL2",
      "Value1":"123",
      "Value2":"333"
   },
   {
      "ColumnName":"COL4",
      "Value1":"111",
      "Value2":"123"
   }
]

我沒有這方面的專業知識，但是我嘗試了下面的 SQL 代碼，但是我以一種非常好的方式需要它，并且在存盤程序中也需要它，它應該以 JSON 格式回傳，請幫助！

我已經嘗試過，請查看下面的鏈接，并提供示例和查詢。

SQL小提琴

uj5u.com熱心網友回復：

您需要取消旋轉所有列，然后將每一行彼此連接起來。

您可以使用手動旋轉所有內容 CROSS APPLY (VALUES

SELECT
  aId = a.id,
  bId = b.id,
  v.columnName,
  v.value1,
  v.value2
FROM @t a
JOIN @t b
    ON a.id < b.id
 -- alternatively
 -- ON a.id = 1 AND b.id = 2
CROSS APPLY (VALUES
   ('col1', CAST(a.col1 AS nvarchar(100)), CAST(b.col1 AS nvarchar(100))),
   ('col2', CAST(a.col2 AS nvarchar(100)), CAST(b.col2 AS nvarchar(100))),
   ('col3', CAST(a.col3 AS nvarchar(100)), CAST(b.col3 AS nvarchar(100))),
   ('col4', CAST(a.col4 AS nvarchar(100)), CAST(b.col4 AS nvarchar(100)))
) v (ColumnName, Value1, Value2)
WHERE EXISTS (SELECT v.Value1 EXCEPT SELECT v.Value2)
FOR JSON PATH;

使用WHERE EXISTS (SELECT a.Value1 INTERSECT SELECT a.Value2)意味著將正確考慮空值。

或者您可以使用SELECT t.* FOR JSON和取消旋轉使用OPENJSON

WITH allValues AS (
    SELECT
      t.id,
      j2.[key],
      j2.value,
      j2.type
    FROM @t t
    CROSS APPLY (
        SELECT t.*
        FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
    ) j1(json)
    CROSS APPLY OPENJSON(j1.json) j2
    WHERE j2.[key] <> 'id'
)
SELECT
  aId = a.id,
  bId = b.id,
  columnName = a.[key],
  value1 = a.value,
  value2 = b.value
FROM allValues a
JOIN allValues b ON a.[key] = b.[key]
    AND a.id < b.id
 -- alternatively
 -- AND a.id = 1 AND b.id = 2
WHERE a.type <> b.type
   OR a.value <> b.value
FOR JSON PATH;

資料庫<>小提琴

實際資料的 SQL 小提琴

uj5u.com熱心網友回復：

回答：

一個可能的解決方案是CROSS JOIN使用額外的APPLY運算子：

來自小提琴的測驗資料：

CREATE TABLE companies ( 
   Id int,
   company_name  VARCHAR(40),
   address_type  VARCHAR(40),
   address    VARCHAR(40)
);
INSERT INTO companies VALUES (1,'Company A','Billing','111 Street');
INSERT INTO companies VALUES (2,'Company A','Shipping','112 Street');

存盤程序：

CREATE PROCEDURE uspColumnsAsJson 
   @Id1 int,
   @Id2 int
AS
BEGIN
   SELECT a.ColumnName, a.Value1, a.Value2
   FROM (
      SELECT 
         c1.company_name AS company_name1, 
         c1.address_type AS address_type1, 
         c1.address AS address1, 
         c2.company_name AS company_name2, 
         c2.address_type AS address_type2, 
         c2.address AS address2 
      FROM companies c1
      CROSS JOIN companies c2
      WHERE c1.Id = @Id1 AND c2.Id = @Id2
   ) t
   CROSS APPLY (VALUES
      ('company_name', CONVERT(varchar(max), t.company_name1), CONVERT(varchar(max), t.company_name2)),
      ('address_type', CONVERT(varchar(max), t.address_type1), CONVERT(varchar(max), t.address_type2)),
      ('address', CONVERT(varchar(max), t.address1), CONVERT(varchar(max), t.address2))
   ) a (ColumnName, Value1, Value2)
   WHERE a.Value1 <> a.Value2
   FOR JSON AUTO
END

EXEC uspColumnsAsJson 1, 2

結果：

[
{"ColumnName":"address_type","Value1":"Billing","Value2":"Shipping"},
{"ColumnName":"address","Value1":"111 Street","Value2":"112 Street"}
]

更新：

如果要包含所有列，則需要基于系統目錄視圖的動態陳述句：

CREATE PROCEDURE uspColumnsAsJson 
   @Id1 int,
   @Id2 int
AS
BEGIN
   DECLARE @stmt nvarchar(max)
   DECLARE @prms nvarchar(max)
   DECLARE @err int

   -- APPLY part
   SELECT @stmt = STRING_AGG(
      CONCAT(
         N'(''',
         col.[name],
         N''', CONVERT(varchar(max), c1.',
         QUOTENAME(col.[name]),
         N'), CONVERT(varchar(max), c2.',
         QUOTENAME(col.[name]),
         N'))'
      ),
      N','
   )   
   FROM sys.columns col
   JOIN sys.tables tab ON col.object_id = tab.object_id
   JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
   WHERE (tab.[name] = 'companies') AND (sch.[name] = 'dbo') AND (col.[name] <> 'Id')
   
   -- Whole statement
   SET @stmt = CONCAT(
      N'SELECT a.ColumnName, a.Value1, a.Value2 ',
      N'FROM companies c1 ',
      N'CROSS JOIN companies c2 ',
      N'CROSS APPLY (VALUES ',
      @stmt, 
      N') a (ColumnName, Value1, Value2) ',
      N'WHERE (c1.Id = @Id1) AND (c2.Id = @Id2) AND (a.Value1 <> a.Value2) ',
      N'FOR JSON AUTO '
   )
   
   -- Execution
   SET @prms = N'@Id1 int, @Id2 int'
   EXEC @err = sp_executesql @stmt, @prms, @Id1, @Id2
   RETURN @err
END

標籤：sql sql-server 数据库 存储过程

上一篇：帶有多張桌子的預包裝房間資料庫

下一篇：如何從url中的id引數創建頁面