sql - SQL Server 查询与按字段错误分组

Question

我有下表MyTable：

 id │ value_two │ value_three │ value_four 
────┼───────────┼─────────────┼────────────
  1 │ a         │ A           │ AA
  2 │ a         │ A2          │ AA2
  3 │ b         │ A3          │ AA3
  4 │ a         │ A4          │ AA4
  5 │ b         │ A5          │ AA5

我想查询按 .{ value_three, value_four }分组的对象数组value_two。value_two应该单独出现在结果中。结果应如下所示：

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

在 postgress 我可以这样做：

SELECT value_two
     , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
FROM   mytable
GROUP  BY value_two

但在 SQL SERVER 我得到'json_array_elements' is not a recognized built-in function name.

在 SQL SERVER 上与此等效的是什么。有人能帮助我吗？

标签： sqljsonsql-serversql-server-2016sql-query-store

Answer 1

如果您使用的是 SQL Server 2016+，您可以尝试使用FOR JSON：

桌子：

SELECT *
INTO Data
FROM (VALUES
   (1, 'a', 'A',  'AA'),
   (2, 'a', 'A2', 'AA2'),
   (3, 'b', 'A3', 'AA3'),
   (4, 'a', 'A4', 'AA4'),
   (5, 'b', 'A5', 'AA5')
) v (id, value_two, value_three, value_four)

陈述：

SELECT DISTINCT d.value_two, j.value_four
FROM Data d
OUTER APPLY (
   SELECT value_three, value_four
   FROM Data
   WHERE value_two = d.value_two
   FOR JSON AUTO
) j (value_four)

结果：

value_two value_four
a         [{"value_three":"A","value_four":"AA"},{"value_three":"A2","value_four":"AA2"},{"value_three":"A4","value_four":"AA4"}]
b         [{"value_three":"A3","value_four":"AA3"},{"value_three":"A5","value_four":"AA5"}]