使用 SELECT 引用计算列中的当前行

发布于 2024-08-05 11:49:55 字数 934 浏览 2 评论 0原文

我有一个表格，表示合同的参数 - 包括它们通过附录随时间的变化。第一个附录是“特殊”附录，代表合同首次签订时的参数。

该表应如下所示：

ID ProjectID BeginDate   DeadlineMonths DeadlineDate
1  20        20-12-2006  24             <computed= 20-12-2006 + 24 months>
2  23        12-03-2007  12             <computed= 12-03-2007 + 12 months>
3  20        06-09-2007  36             <computed= **20-12-2006** + 36 months>

ProjectID 是 Projects 表的 FK，其主键也称为 ProjectID。

我希望 DeadlineDate 成为一个计算字段，计算方式如下：

DeadlineDate COMPUTE BY ((
    select first 1 AddMonth(contract.BeginDate, DeadlineMonths)
    from addendums contract
    where contract.projectid = projectid
    order by contract.BeginDate ))

问题是在 contract.projectid = projectid 中，第二个 ProjectID 必须引用正在计算的当前行，而不是 select 语句中的当前行（与 contract.projectid 相同）。

我正在使用火鸟。我需要表中的列，而不是 SELECT 语句中的列，因为使用数据库的应用程序中存在 ORM 问题。

原文

I have a table that represents the parameters of a contract - including their change over time through addendums. The first addendum is a "special" addendum representing the parameters when the contract was first signed.

Here's how the table should look like:

ID ProjectID BeginDate   DeadlineMonths DeadlineDate
1  20        20-12-2006  24             <computed= 20-12-2006 + 24 months>
2  23        12-03-2007  12             <computed= 12-03-2007 + 12 months>
3  20        06-09-2007  36             <computed= **20-12-2006** + 36 months>

ProjectID is a FK to the Projects table whose primary key is also called ProjectID.

I want DeadlineDate to be a calculated field, calculated like so:

DeadlineDate COMPUTE BY ((
    select first 1 AddMonth(contract.BeginDate, DeadlineMonths)
    from addendums contract
    where contract.projectid = projectid
    order by contract.BeginDate ))

The problem is that in contract.projectid = projectid the second ProjectID has to reference the current row being computed, not the current row in the select statement (which is the same as contract.projectid).

I'm using Firebird. I need the column in the table and NOT in a SELECT statement because of ORM issues in the application using the database.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遮云壑 2024-08-12 11:49:55

只需在字段前面加上当前表的表名即可：

DeadlineDate COMPUTED BY ((
select first 1 AddMonth(contract.BeginDate, DeadlineMonths)
from addendums contract
where contract.projectid = projects.projectid
order by contract.BeginDate ))

Just prefix the field with table name of the current table:

DeadlineDate COMPUTED BY ((
select first 1 AddMonth(contract.BeginDate, DeadlineMonths)
from addendums contract
where contract.projectid = projects.projectid
order by contract.BeginDate ))

回复收藏 0 原文

帥小哥 2024-08-12 11:49:55

您可以为您的查询创建一个视图并在 ORM 中使用它吗？

CREATE VIEW v_addendums
AS
SELECT  ID, ProjectID, BeginDate, DeadlineMonths, 
        (
        SELECT  first 1 AddMonth(contract.BeginDate, DeadlineMonths)
        FROM    addendums contract
        WHERE   contract.projectid = a.projectid
        ORDER BY
                contract.BeginDate
        )
FROM    addendums a

Can you create a view over your query and use it in the ORM?

CREATE VIEW v_addendums
AS
SELECT  ID, ProjectID, BeginDate, DeadlineMonths, 
        (
        SELECT  first 1 AddMonth(contract.BeginDate, DeadlineMonths)
        FROM    addendums contract
        WHERE   contract.projectid = a.projectid
        ORDER BY
                contract.BeginDate
        )
FROM    addendums a

回复收藏 0 原文

~没有更多了~