问题

示例表结构:

 create table issues
(id int, title varchar(50), affectedclients varchar(max))

create table clients
(id int, name varchar(50))

insert into issues (id, title, affectedclients) values (1, 'Error when clicking save', '["1","2"]');
insert into issues (id, title, affectedclients) values (2, '404 error on url', '[3]');

insert into clients (id, name) values (1, 'Tesco');
insert into clients (id, name) values (2, 'Costa');
insert into clients (id, name) values (3, 'Boots');
insert into clients (id, name) values (4, 'Nandos');
 

我想运行查询,以便我可以以以下格式获取数据:

 Id   Title                        AffectedClients
1    Error when clicking save     Tesco, Costa
2    404 error on url             Boots
 

如何以最高性能的方式实现这一目标?

如果使用正常标准化的数据库非常容易,请提供一个例子。

  最佳答案

您需要使用具有显式模式定义的OPENJSON()来解析affectedclients列中的JSON文本.之后您需要聚合名称(使用FOR XML PATH for SQL Server 2016+或STRING_AGG() for SQL SQL Server 2017+).

数据:

 create table issues
(id int, title varchar(50), affectedclients varchar(max))
create table clients
(id int, name varchar(50))
insert into issues (id, title, affectedclients) values (1, 'Error when clicking save', '["1","2"]');
insert into issues (id, title, affectedclients) values (2, '404 error on url', '[3]');
insert into clients (id, name) values (1, 'Tesco');
insert into clients (id, name) values (2, 'Costa');
insert into clients (id, name) values (3, 'Boots');
insert into clients (id, name) values (4, 'Nandos');
 

SQL Server 2016的语句:

 SELECT 
   i.id, 
   i.title,
   [affectedclients] = STUFF(
      (
      SELECT CONCAT(', ', c.[name])
      FROM OPENJSON(i.affectedclients) WITH (id int '$') j
      LEFT JOIN clients c on c.id = j.id
      FOR XML PATH('')
      ), 1, 2, '')
FROM issues i
 

SQL Server 2017的语句:

 SELECT i.id, i.title, STRING_AGG(c.name, ', ') AS affectedclients
FROM issues i
CROSS APPLY OPENJSON(i.affectedclients) WITH (id int '$') j
LEFT JOIN clients c ON c.id = j.id
GROUP BY i.id, i.title
ORDER BY i.id, i.title
 

结果:

 -----------------------------------------------
id  title                       affectedclients
-----------------------------------------------
1   Error when clicking save    Tesco, Costa
2   404 error on url            Boots
 

  相同标签的其他问题

sqljsonsql-serversql-server-json