我有一张这样的桌子:
EVENT
-------------
user_id thumbnail_name
0 event/abc.jpg
0 event/efg.jpg
0 event/xyz.jpg
1 event/lmn.jpg
1 event/opq.jpg
2 event/rsf.jpg
如何按user_id分组并检索任意缩略图? 预期产出:
[ {user_id: 0, thumbnail_name: event/abc.jpg},
{user_id: 1, thumbnail_name: event/lmn.jpg},
{user_id: 2, thumbnail_name: event/rsf.jpg},
]
这在SQL中是可能的吗?更不用说Django ORM了?
用于SQl Server中的JSON AUTO
SELECT *
FROM EVENT
FOR JSON AUTO
如果您希望只返回一行,那么您可以在FOR JSON AUTO后面添加Without_Array_Wrapper,并在AUTO关键字后面添加逗号,您将得到一个对象而不是数组:
SELECT *
FROM EVENT
FOR JSON AUTO, Without_Array_Wrapper;
在MySQL中
SELECT json_object('user_id', user_id, 'thumbnail_name', thumbnail_name)
FROM EVENT;
元首修改
SELECT JSON_ARRAYAGG(JSON_OBJECT('user_id', user_id, 'thumbnail_name', thumbnail_name)) from EVENT;