代码之家 › 专栏 › 技术社区 › Homan

是否有方法按与其子记录关联的最早日期对父记录进行排序?

sql

Homan · 技术社区 · 5 年前

我有两张表,一张发票表和一张工作日表。发票有很多工作日。

Workday架构具有:

发票编号:整数,日期:日期

假设发票模式目前在表中只有一个ID。

在我的应用程序中,我想打印出所有预加载的发票和所有工作日。对于每个发票,子工作日应按日期排序。尽管发票上没有日期字段,我想知道是否可以在所有相应的子工作日中按最早的日期对发票进行排序?

目标是能够生成一个看起来像:

Invoice date range: Jan 02, 2019 - Jan 03, 2019
Workdays: 
Jan 02, 2019
Jan 03, 2019

Invoice date range: Jan 05, 2019 - Jan 06, 2019
Workdays:
Jan 05, 2019
Jan 06, 2019

我正在使用这样的查询:从invoice inner join workdays on workdays.invoice_id=invoice.id order by workdays.date

但有可能发票出现故障,因为发票上的订单没有指定。我不知道其他任何方法来指定发票上的排序顺序,而不将最早的workday.date缓存到新列的父级(发票)中,然后按该额外列排序。

更新:我使用Postgres和MySQL。“视图”不是指数据库(物化)视图,而是指模型、视图、控制器、视图层。

1 回复 | 直到 5 年前

Michael Buen 5 年前

比如:

select 
    i.id, i.comment,
    min(wd.date) as invoice_range_from, max(wd.date) as invoice_range_to
from invoice i
left join workdays wd on i.id = wd.invoice_id
group by i.id
order by min(wd.date), max(wd.date)

如果您希望在一次服务器往返中检索父级子级,请使用RDBMS的JSON或XML功能,特别是如果您的客户机应用程序仍然使用JSON,则可以使用以下功能。例如.,

现场试验: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=0b1ce002c4380a3542387209c3c43fae

select 
    i.id, i.comment,
    min(wd.date) as invoice_from, max(wd.date) as invoice_to,
    json_agg(json_build_object('date', wd.date)) as workdays_data
from invoice i
left join workdays wd on i.id = wd.invoice_id
group by i.id
order by min(wd.date), max(wd.date)

输出:

id  comment invoice_from    invoice_to  workdays_data
1   Hello   2019-01-02      2019-01-03  [{"date" : "2019-01-02"}, {"date" : "2019-01-03"}]
2   Hola    2019-01-05      2019-01-06  [{"date" : "2019-01-05"}, {"date" : "2019-01-06"}]

否则,您将不得不使用ORM的批处理功能。如果没有可用的ORM,则需要手动使查询最小化服务器往返。

如果希望尽全力使用RDBMS的JSON功能一次性呈现树状数据,您可以:)

现场试验: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=1ed3105719ed033ba568e01b3d97c234

with a as 
(
    select 
        i.id, i.comment,
        min(wd.date) as invoice_range_from, max(wd.date) as invoice_range_to,
        json_agg(json_build_object('date', wd.date)) as workdays_data
    from invoice i
    left join workdays wd on i.id = wd.invoice_id
    group by i.id
    order by min(wd.date), max(wd.date)
)
select json_agg(a.*) from a;

输出:

[
    {
        "id": 1,
        "comment": "Hello",
        "invoice_range_from": "2019-01-02",
        "invoice_range_to": "2019-01-03",
        "workdays_data": [
            {
                "date": "2019-01-02"
            },
            {
                "date": "2019-01-03"
            }
        ]
    },
    {
        "id": 2,
        "comment": "Hola",
        "invoice_range_from": "2019-01-05",
        "invoice_range_to": "2019-01-06",
        "workdays_data": [
            {
                "date": "2019-01-05"
            },
            {
                "date": "2019-01-06"
            }
        ]
    }
]

架构:

create table invoice
(
  id int primary key,
  comment text not null
);


create table workdays
(
  invoice_id int not null references invoice(id),
  id int not null generated by default as identity primary key,
  date date not null
);


insert into invoice(id, comment) values
(1, 'Hello'),
(2, 'Hola');

insert into workdays(invoice_id, date) values
(1, '2019-1-2'),
(1, '2019-1-3'),
(2, '2019-1-5'),
(2, '2019-1-6');