代码之家 › 专栏 › 技术社区 › Eamonn McEvoy

将存储过程的结果分组为JSON-Azure SQL

sql-server sql

Eamonn McEvoy · 技术社区 · 7 年前

我有一个存储过程,返回的数据大致是这种格式

ID | Field1 | Field2 | Group  | Description
------------------------------------------------
1  |   A    |   A    |   g1   |  g1A description
1  |   A    |   A    |   g2   |  g2A description
2  |   B    |   B    |   g1   |  g1B description
2  |   B    |   B    |   g2   |  g2B description
3  |   C    |   C    |   g1   |  g1C description
3  |   C    |   C    |   g2   |  g2C description


SELECT
  a.ID,
  a.Field1,
  b.Field2,
  c.Group,
  c.Description
FROM A as a
  INNER JOIN B b ...
  INNER JOIN C c ...

在代码中,我将数据集转换为JSON

[
  {
    ID: 1,
    Field1: A,
    Field2: A,
    Groups: {
      g1: g1A description,
      g2: g2A description,
    }
  }, {
    ID: 2,
    Field1: B,
    Field2: B,
    Groups: {
      g1: g1B description,
      g2: g2B description,
    }
  }, {
    ID: 1,
    Field1: C,
    Field2: C,
    Groups: {
      g1: g1C description,
      g2: g2C description,
    }
  }
]

我不喜欢这个过程在每一行中都返回大部分重复的数据。是否有某种方法可以在SQL中执行分组,以便返回的数据集类似于

ID | Field1 | Field2 | Groups  
------------------------------------------------
1  |   A    |   A    | 'Groups: { g1: g1A description, g2: g2A description }'
2  |   B    |   B    | 'Groups: { g1: g1B description, g2: g2B description }'
3  |   C    |   C    | 'Groups: { g1: g1C description, g2: g2C description }'

或

 ID | Field1 | Field2 |        g1       |        g2
----------------------------------------------------------
 1  |   A    |   A    | g1A description | g2A description
 2  |   B    |   B    | g1B description | g2B description
 3  |   C    |   C    | g1C description | g2C description

根据D-Shih的回答,我得出了以下结论:

   SELECT
       a.ID,
       a.Field1,
       b.Field2,
       (
         SELECT 
           MAX(CASE WHEN [Group] = 'g1' THEN Description END) g1, 
           MAX(CASE WHEN [Group] = 'g2' THEN Description END) g2
         FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
       ) as Groups
   FROM A as a
      INNER JOIN B b ...
      INNER JOIN C c ...


    ID | Field1 | Field2 | Groups  
   ------------------------------------------------
    1  |   A    |   A    | '{ "g1": "g1A description", "g2": "g2A description" }'
    2  |   B    |   B    | '{ "g1": "g1B description", "g2": "g2B description" }'
    3  |   C    |   C    | '{ "g1": "g1C description", "g2": "g2C description" }'

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=4bf8a009fe91bbbd0f3246723a1f9eb4

1 回复 | 直到 7 年前

D-Shih 7 年前

SELECT ID,
      Field1,
      Field2,
      MAX(CASE WHEN [Group] = 'g1' THEN Description END) g1, 
      MAX(CASE WHEN [Group] = 'g2' THEN Description END) g2
FROM T
GROUP BY ID,Field1,Field2

SQLFIDDLE

注意

T