mysql 矩阵运算_mysql – 这个矩阵如何在SQL中成倍增加？

完全披露,我是SQL的菜鸟

给定两个sparce矩阵A和B,定义为：

A(row_number,column_number,value)和B(row_number,column_number,value)

我不明白这个查询如何表示两个矩阵的乘法：

SELECT A.row_number, B.column_number, SUM(A.value * B.value)

FROM A, B

WHERE A.column_number = B.row_number

GROUP BY A.row_number, B.column_number

我的困惑在于SUM语法和GROUP BY / SELECT语法

所以对于我的GROUP BY / SELECT混淆,我不明白为什么表达式

SELECT语句后需要A.row_number和B.column_number

为什么我们必须在我们已经使用SELECT和WHERE时指定？对我来说,好像我们说我们想要使用那些表达式(A.row_number和B.column_number)进行SELECT,即使我们已经从WHERE返回了一个表. SELECT SELECT *会不会更有意义？我假设GROUP BY只是要求你输出它在SELECT语句中使用的表达式,但我不确定.

对于SUM,我只想澄清一下,SUM只使用了A.value和B.value来自WHERE正确返回的内容？否则,您将所有A.value与所有B.value相乘.

澄清其中任何一个都会非常有帮助.谢谢！

解决方法:

create table A

( column_number int,

row_number int,

value int

);

create table B

( column_number int,

row_number int,

value int

);

insert A (column_number,row_number,value) values (1,1,1),(1,2,2),(2,1,3),(2,2,4);

insert B (column_number,row_number,value) values (1,1,10),(1,2,20),(2,1,30),(2,2,40);

使用旧样式(非显式)的数据加入时不使用聚合或分组：

SELECT A.row_number as Ar, B.column_number as Bc,

A.value as Av,B.value as Bv,A.value*B.value as product

FROM A, B

WHERE A.column_number = B.row_number

+------+------+------+------+---------+

| Ar | Bc | Av | Bv | product |

+------+------+------+------+---------+

| 1 | 1 | 1 | 10 | 10 |

| 2 | 1 | 2 | 10 | 20 |

| 1 | 1 | 3 | 20 | 60 |

| 2 | 1 | 4 | 20 | 80 |

| 1 | 2 | 1 | 30 | 30 |

| 2 | 2 | 2 | 30 | 60 |

| 1 | 2 | 3 | 40 | 120 |

| 2 | 2 | 4 | 40 | 160 |

+------+------+------+------+---------+

看到上面的内容,下面的内容更加清晰：

SELECT A.row_number, B.column_number,sum(A.value * B.value) as theSum

FROM A, B

WHERE A.column_number = B.row_number

GROUP BY A.row_number, B.column_number

+------------+---------------+--------+

| row_number | column_number | theSum |

+------------+---------------+--------+

| 1 | 1 | 70 |

| 1 | 2 | 150 |

| 2 | 1 | 100 |

| 2 | 2 | 220 |

+------------+---------------+--------+

标签：mysql,sql,group-by,sum,matrix

来源： https://codeday.me/bug/20190609/1203847.html