表"ORDER":

ID ORDERN_NUM
1 123
2 321
3 456

表"RECEIVERS":

FIRST_NAME ID LAST_NAME
Pete 1 Tyler
Sarah 1 Bowden
Bart 2 Simpson

表"PRODUCTS":

TYPE ID
Towel 1
Pen 1
Spoon 2

我们可以说"FIRST_NAME"和"TYPE"是各自表的主键,但这对这个问题并不重要.重要的是"ORDER"的主键是"ID",而"ID"是"PRODUCTS"和"RECEIVERS"中的外键.

我试图开发一个单一的查询,允许用户在"RECEIVERS.FIRST_NAME"上查询.然后,查询将连接所有三个表,并返回与"ID"=1相关的所有内容.因此,如果我要查询"Pete",将返回如下所示的对象:

[{
    "ID": "1",
    "ORDERN_NUM": "123",
    "RECEIVERS": [{
        "FIRST_NAME": "Pete",
        "LAST_NAME": "Tyler"
    },
    {
        "FIRST_NAME": "Sarah",
        "LAST_NAME": "Bowden"
    }],
    "PRODUCTS": [{
        "TYPE": "Towel"
    },
    {
        "TYPE": "Pen"
    }]
}]

到目前为止,我有这样的疑问:

SELECT COUNT(*), 
       JSON_ARRAYAGG(JSON_OBJECT('ID', o.ID, 'RECEIVERS', r.RECEIVERS, 'PRODUCTS', p.PRODUCTS))
FROM       ORDER AS o
INNER JOIN (SELECT ID,
                   JSON_ARRAYAGG(JSON_OBJECT('firstName', FIRST_NAME, 'lastName', LAST_NAME)) RECEIVERS
            FROM RECEIVERS
            GROUP BY ID) r on o.ID = r.ID
INNER JOIN (SELECT ID,
                   JSON_ARRAYAGG(JSON_OBJECT('type', TYPE)) PRODUCTS
            FROM PRODUCTS
            GROUP BY ID) p on o.ID = p.ID
WHERE RECEIVERS.FIRST_NAME = "Pete"

然而,当搜索FIRST_NAME = 'Pete'时,它确实会在嵌套数组中带回与Pete关联的所有"ORDER"S和所有"PRODUCTS",并在嵌套数组中带回Pete的详细信息,但它不会带回Sarah作为"RECEIVER".

我怎样才能做到这一点呢?

推荐答案

如果我没有理解错的话,问题出在查询的where子句中;它过滤属于Tyler的订单,而您似乎想要id与属于Tyler的订单具有相同id的订单.

您只需更改以下内容:

where r.FIRST_NAME = "Pete"

致:

where r.id in (select id from receivers where first_name = 'Pete')

在您的查询中:

select count(*), 
    json_arrayagg(json_object('ID', o.ID, 'RECEIVERS', r.RECEIVERS, 'PRODUCTS', p.PRODUCTS))
from orders as o
inner join (
    select id,
        json_arrayagg(json_object('firstName', FIRST_NAME, 'lastName', LAST_NAME)) RECEIVERS
    FROM receivers
    group by id
) r on o.id = r.id
  inner join (
    select id,
        JSON_ARRAYAGG(JSON_OBJECT('type', TYPE)) PRODUCTS
    FROM PRODUCTS
    group by id
) p on o.id = p.id
where r.id in (select id from receivers where first_name = 'Pete')

我们还可以用另一个连接来表达这一逻辑:

select count(*), 
    json_arrayagg(json_object('ID', o.ID, 'RECEIVERS', r.RECEIVERS, 'PRODUCTS', p.PRODUCTS))
from orders as o
inner join (
    select id,
        json_arrayagg(json_object('firstName', FIRST_NAME, 'lastName', LAST_NAME)) RECEIVERS
    FROM receivers
    group by id
) r on o.id = r.id
  inner join (
    select id,
        JSON_ARRAYAGG(JSON_OBJECT('type', TYPE)) PRODUCTS
    FROM PRODUCTS
    group by id
) p on o.id = p.id
inner join receivers as r1 on r1.id = o.id
where r1.first_name = 'Pete'

Mysql相关问答推荐

MySQL查询获取图书的唯一读取页面数

mysql使用LIKE查找二进制字段有问题

如何在sql中包含0作为计数?

使用mysql查询获取不关注user1的user3

计算同一个表中的两列,然后将两者都算作总数

Mysql 查询返回未定义的 node.js

仅当 SELECT 语句在 MySQL 中给出空集时才执行 UPDATE 语句

MySQL - 如何查询涉及前面计算值的表结果

如何使用 DBD::mysql 判断 MySQL 服务是否正在运行

如何从具有第一行列值的表中 Select 客户的最新记录

MySQL 使用了错误的索引

插入二进制数据会导致Data too long for column...

MYSQL:如何根据之前的相关记录获取记录

在 Presto Athena 中将字符串转换为数组

我将如何构建一个 SQL 查询来从交易表中 Select 第一次存款、第二次存款和额外存款

如何使用 SQL 数据库中的经纬度查找最近的位置?

MySQL 连接运算符

如何使用 XML_LOAD() 将 XML 文件导入 MySQL 数据库表;功能

MySQL连接查询使用like?

你能自动创建一个不强制外键约束的 mysqldump 文件吗?