引用当前行

发布于 2024-08-02 07:19:39 字数 481 浏览 3 评论 0原文

我有以下表格:

TableA

ID | SomeInt
1     55
1     66
2     77

TableB

ID | OtherInt
1    ComputedBy Field
2    ComputedBy Field

计算字段需要从 tableA 返回总和,其中 TableB.ID = TableA.ID,但如果我说:

SELECT SUM(SomeInt) from TableA where ID = TableA.Id

其中第一个 ID 是当前表的 ID,则 dbms 会说“之间的模糊字段名称table Table1 和 Table2"

那么,如何从当前行获取 ID,以便它不会混合字段名称。

像 This.ID、Self.ID、CurrentRow.ID 或任何 sql 使用的东西

编辑:显然我可以使用当前表的名称来引用列

I have the following tables :

TableA

ID | SomeInt
1     55
1     66
2     77

TableB

ID | OtherInt
1    ComputedBy Field
2    ComputedBy Field

The computed by field needs to return the sum from tableA where TableB.ID = TableA.ID, but if I say:

SELECT SUM(SomeInt) from TableA where ID = TableA.Id

where the first ID would be the current table's ID, the dbms says "Ambiguous field name between table Table1 and Table2"

So, how do I get the ID from the current row so that it doesnt mix the field name.

Something like This.ID, Self.ID, CurrentRow.ID or whatever sql uses

EDIT: Apparently I could use Current table's name to reference the column

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

扫码二维码加入Web技术交流群

发布评论

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

评论(5

战皆罪 2024-08-09 07:19:39

简单的答案

select tb.id
      ,sum(ta.someint)
  from TableA ta
       inner join TableB tb
               on ta.id = tb.id
group by tb.id

这是一个完全解决您的问题借口 tsql 语法的示例。

declare @TableA table(id int
                         ,someint int)

insert @TableA values(1,55)
insert @TableA values(1,66)
insert @TableA values(2,77)

declare @TableB table(id int)

insert @TableB values(1)

select tb.id
      ,sum(ta.someint)
  from @TableA ta
       inner join @TableB tb
               on ta.id = tb.id
group by tb.id

Simple answer

select tb.id
      ,sum(ta.someint)
  from TableA ta
       inner join TableB tb
               on ta.id = tb.id
group by tb.id

This is a fully worked out example of your problem excuse tsql syntax.

declare @TableA table(id int
                         ,someint int)

insert @TableA values(1,55)
insert @TableA values(1,66)
insert @TableA values(2,77)

declare @TableB table(id int)

insert @TableB values(1)

select tb.id
      ,sum(ta.someint)
  from @TableA ta
       inner join @TableB tb
               on ta.id = tb.id
group by tb.id
黑凤梨 2024-08-09 07:19:39
SELECT B.ID, SUM(A.SomeInt) AS Total 
From TableA A, TableB B where A.ID = B.Id
GROUP BY B.ID
SELECT B.ID, SUM(A.SomeInt) AS Total 
From TableA A, TableB B where A.ID = B.Id
GROUP BY B.ID
橪书 2024-08-09 07:19:39
 SELECT TableB.ID, SUM(TableA.SomeInt) 
    FROM TableB 
        LEFT OUTER JOIN TableA ON
            TableB.ID = TableA.ID
    GROUP BY TableB.ID

 SELECT TableB.ID, SUM(TableA.SomeInt) 
    FROM TableB 
        LEFT OUTER JOIN TableA ON
            TableB.ID = TableA.ID
    GROUP BY TableB.ID

风吹雪碎 2024-08-09 07:19:39

您是否试图使 TableB 具有 TableA 值的总和? ID 是否已经存在(只需更新)或者您需要从头开始填充 TableB?我不明白你的总体目标是什么,这会影响答案。

EG

  • Update w/ group by
  • Insert
  • 触发器根据以下内容更新 TableB
    更改表A
  • 简单选择...

让我知道您的目标是什么,我们可以获得理想的答案。

Are you trying to make TableB have the sum of TableA's values? Do the ID's already exist (just need to be updated) or do you need to populate TableB from scratch? I don't understand what your overall goal is, and that would affect the answer.

E.G.

  • Update w/ group by
  • Insert
  • Trigger that updates TableB based on
    changes to tableA
  • Simple select...

Let me know what your goal is and we can get an ideal answer.

失眠症患者 2024-08-09 07:19:39

您可以简单地将 "TableB" 本身设置为计算视图:

CREATE VIEW TableB AS SELECT ID, SUM(SomeInt) AS OtherInt FROM TableA;

SELECT * FROM TableB;
 id | otherint 
----+----------
  2 |       77
  1 |      121
(2 rows)

You might simply make "TableB" itself a computed view:

CREATE VIEW TableB AS SELECT ID, SUM(SomeInt) AS OtherInt FROM TableA;

SELECT * FROM TableB;
 id | otherint 
----+----------
  2 |       77
  1 |      121
(2 rows)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文