完全披露,我是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
2041
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
