我有以下表格：
create table students
(
id int,
name varchar(10)
)
create table subjects
(
subjectId int,
studentId int,
subject varchar(12)
)
create table marks
(
studentId int,
subjectId int,
marks int
)
create table sports
(
sportId int,
studentId int,
name varchar(12)
)
使用以下資料：
insert into students values(1, 'Rusty');
insert into subjects values(1, 1, 'math')
insert into subjects values(2, 1, 'science')
insert into marks values(1,1,50)
insert into marks values(1,2,60)
insert into sports values(1, 1, 'soccer')
insert into sports values(2, 1, 'baseball')
我想在 SQL Server 中撰寫一個查詢以獲得以下輸出：
studentId = 1
{
"id": 1,
"name": "Rusty",
"subjects" : [
{
"name": "math",
"marks": 50
},
{
"name": "science",
"marks": 60
}
],
"sports": [
{
"name": "soccer"
},
{
"name": "baseball"
}
]
}
我嘗試了以下查詢
select *
from students s
join subjects su on (s.id = su.studentId)
join sports sp on (s.id = sp.studentId)
where s.id = 1
for json auto
這是輸出：
[
{
"id": 1,
"name": "Rusty",
"su": [
{
"subjectId": 1,
"studentId": 1,
"subject": "math",
"sp": [
{
"sportId": 1,
"studentId": 1,
"name": "soccer"
}
]
},
{
"subjectId": 1,
"studentId": 1,
"subject": "science",
"sp": [
{
"sportId": 1,
"studentId": 1,
"name": "soccer"
}
]
},
{
"subjectId": 1,
"studentId": 1,
"subject": "math",
"sp": [
{
"sportId": 1,
"studentId": 1,
"name": "baseball"
}
]
},
{
"subjectId": 1,
"studentId": 1,
"subject": "science",
"sp": [
{
"sportId": 1,
"studentId": 1,
"name": "baseball"
}
]
}
]
}
]
uj5u.com熱心網友回復：
對于您想要的輸出，您可以使用相關子查詢sports并使用FOR JSON PATHsubjects生成自己的 JSON，并通過JSON_QUERY (Transact-SQL)將該資訊作為嵌套物件陣列包含在您的主 JSON 輸出中，例如：
/*
* Data setup...
*/
create table students (
id int,
name varchar(10)
);
create table subjects (
subjectId int,
studentId int,
subject varchar(12)
);
create table marks (
studentId int,
subjectId int,
marks int
);
create table sports (
sportId int,
studentId int,
name varchar(12)
);
insert into students (id, name) values
(1, 'Rusty');
insert into subjects (subjectId, studentId, subject) values
(1, 1, 'math'),
(2, 1, 'science');
insert into marks (studentId, subjectId, marks) values
(1,1,50),
(1,2,60);
insert into sports (sportId, studentId, name) values
(1, 1, 'soccer'),
(2, 1, 'baseball');
/*
* Example query...
*/
select
students.id,
students.name,
json_query(( --<<-- doubled brakcets
select
subjects.subject,
marks.marks
from subjects
join marks
on marks.subjectId = subjects.subjectId
and marks.studentId = subjects.studentId
where subjects.studentId = students.id
for json path
)) as [subjects],
json_query(( --<<-- doubled brackets
select
sports.name
from sports
where sports.studentId = students.id
for json path
)) as [sports]
from students
where students.id = 1
for json path, without_array_wrapper;
這會產生 JSON 輸出：
{
"id": 1,
"name": "Rusty",
"subjects": [
{
"subject": "math",
"marks": 50
},
{
"subject": "science",
"marks": 60
}
],
"sports": [
{
"name": "soccer"
},
{
"name": "baseball"
}
]
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/489862.html
上一篇：如何創建一年中每一天都有空白行的SSRSTablix？
下一篇：在SQL中查找遞增或遞減1的值