计算字段的总和

发布于 2024-11-08 22:16:36 字数 573 浏览 0 评论 0原文

我有两个表：

table1 (a,b,c)
table2 (a,d,f)

我想为每个 b 获取相应 d 和 f 的乘法之和

表 1 数据

a   b      c
-------------
1   hello  3
2   bye    4

表 2 数据

a  d  f
--------
1  5  3
1  2  4
2  1  3
2  2  3

结果： hello:5* 3+2*4 ，再见：1*3+2*3
我的问题是：

  SELECT t1.a, 
         t2.d * t2.f AS m, 
         SUM(m)   
    FROM table1 AS t1, 
         table2 AS t2 
   WHERE t1.a = t2.a 
GROUP BY t1.b

那么这里出了什么问题？在 mysql 中，我得到#1054 - “字段列表”中的未知列“m”

原文

I have two tables:

table1 (a,b,c)
table2 (a,d,f)

I want to get for each b to get sum of multiplication of corresponding d and f

table 1 data

a   b      c
-------------
1   hello  3
2   bye    4

table 2 data

a  d  f
--------
1  5  3
1  2  4
2  1  3
2  2  3

result: hello:5*3+2*4 , bye:1*3+2*3
My query is:

  SELECT t1.a, 
         t2.d * t2.f AS m, 
         SUM(m)   
    FROM table1 AS t1, 
         table2 AS t2 
   WHERE t1.a = t2.a 
GROUP BY t1.b

So what is wrong here? In mysql I get #1054 - Unknown column 'm' in 'field list'

分享到QQ

分享到微博

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

发布评论

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

指尖上的星空 2024-11-15 22:16:36

尝试：（

SELECT t1.a, t2.d*t2.f AS m, SUM(t2.d*t2.f)
FROM table1 AS t1, table2 AS t2 
WHERE t1.a=t2.a GROUP BY t1.b

即展开别名）

Try:

SELECT t1.a, t2.d*t2.f AS m, SUM(t2.d*t2.f)
FROM table1 AS t1, table2 AS t2 
WHERE t1.a=t2.a GROUP BY t1.b

(I.e. expand the alias)

回复收藏 0 原文

瞳孔里扚悲伤 2024-11-15 22:16:36

您不能在同一个 SELECT 子句中引用列别名 - 您要么需要重现公式：

  SELECT t1.a, 
         t2.d * t2.f AS m, 
         SUM(t2.d * t2.f)   
    FROM table1 AS t1 
    JOIN table2 AS t2 ON t1.a = t2.a 
GROUP BY t1.b

..或者使用派生表/内联视图：

SELECT x.a,
       x.m, 
       SUM(x.m)
  FROM (SELECT t1.a, 
               t2.d * t2.f AS m
          FROM table1 AS t1 
          JOIN table2 AS t2 ON t1.a = t2.a 
      GROUP BY t1.b) x

最早的 MySQL 允许引用列别名是 GROUP BY< /code> 子句（HAVING 和 ORDER BY 也支持列别名）。但大多数其他数据库仅支持早于 ORDER BY 子句。

You can't refer to a column alias in the same SELECT clause - you either need to reproduce the formula:

  SELECT t1.a, 
         t2.d * t2.f AS m, 
         SUM(t2.d * t2.f)   
    FROM table1 AS t1 
    JOIN table2 AS t2 ON t1.a = t2.a 
GROUP BY t1.b

..or use a derived table/inline view:

SELECT x.a,
       x.m, 
       SUM(x.m)
  FROM (SELECT t1.a, 
               t2.d * t2.f AS m
          FROM table1 AS t1 
          JOIN table2 AS t2 ON t1.a = t2.a 
      GROUP BY t1.b) x

The earliest MySQL allows referring to column aliases is the GROUP BY clause (HAVING and ORDER BY also support column aliases). But most other databases only support as early as the ORDER BY clause.

回复收藏 0 原文

~没有更多了~