按列查询组并返回JSON

Question

我有一个如下表：

手机<1><1>08444444444-------

id	mid	coupono	状态
1	1	0811111111		1	1
2	1	08222222222222	2	1
3	1	08222222222	3
	1	08222222222	4
5	1	08111111111	5		1
6	2	0833333333	6		1
7	2	0833333333	7		1
8	2		8	1
----	----	--------------	-------

要求Handphone是JSON中的对象键会使从SQL生成变得困难，而且随着时间的推移添加更多数据，在接收端也可能无法很好地扩展。

以下是一些基于透视的SQL，它将生成您想要的JSON。。。

create table dbo.PivotJsonStuff (
Id int,
[Mid] int,
Handphone varchar(11),
CouponNo int,
Status int
);
insert dbo.PivotJsonStuff (Id, [Mid], Handphone, CouponNo, Status)
values
(1, 1, '08111111111', 1, 1),
(2, 1, '08222222222', 2, 1),
(3, 1, '08222222222', 3, 1),
(4, 1, '08222222222', 4, 1),
(5, 1, '08111111111', 5, 1),
(6, 2, '08333333333', 6, 1),
(7, 2, '08333333333', 7, 1),
(8, 2, '08444444444', 8, 1);
select
[08111111111] = json_query([08111111111]),
[08222222222] = json_query([08222222222])
from (
select Handphone,
[JSON] = (
select PJS2.Id, PJS2.CouponNo, PJS2.Status
from dbo.PivotJsonStuff PJS2
where PJS2.Handphone = PJS1.Handphone
for json path
)
from dbo.PivotJsonStuff PJS1
group by Handphone
) src
pivot (max([JSON]) for Handphone in ([08111111111], [08222222222])) pvt
for json path, without_array_wrapper;

{
"08111111111": [
{
"Id": 1,
"CouponNo": 1,
"Status": 1
},
{
"Id": 5,
"CouponNo": 5,
"Status": 1
}
],
"08222222222": [
{
"Id": 2,
"CouponNo": 2,
"Status": 1
},
{
"Id": 3,
"CouponNo": 3,
"Status": 1
},
{
"Id": 4,
"CouponNo": 4,
"Status": 1
}
]
}