时间戳

首页 > 解决方案 > SQL 视图/查询通过 json 字段连接 2 个表之间的数据

sql - SQL 视图/查询通过 json 字段连接 2 个表之间的数据

问题描述

示例表结构：

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

我怎样才能以最高效的方式实现这一点？

如果使用正确规范化的数据库很容易做到这一点，那么请提供一个示例。

标签： sqljsonsql-serversql-server-json

解决方案

您需要使用OPENJSON()显式架构定义来解析列JSON中的文本affectedclients。之后，您需要聚合名称（FOR XML PATH用于 SQL Server 2016+ 或STRING_AGG()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

推荐阅读