需要帮助进行sql查询,错误消息subquery返回的行数超过1行

xvw2m8pv  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(406)

我有两个表,我想通过检索基于匹配voteid的分配(预算)来更新开支的运行余额
我正在使用phpmyadmin mysql
apache/2.4.43(win64)openssl/1.1.1g php/7.4.6
数据库客户端版本:libmysql-mysqlnd 7.4.6
php扩展:mysqli文档curl文档mbstring文档
php版本:7.4.6
服务器类型:mariadb
到目前为止,这是我的sql语句

SELECT allocation 
FROM vote 
INNER JOIN expenditure ON vote.voteid = expenditure.voteid 
GROUP BY vote.voteid;

SET @csum = (SELECT allocation 
             FROM vote 
             INNER JOIN expenditure ON vote.voteid = expenditure.voteid 
             GROUP BY vote.voteid);

UPDATE expenditure 
SET balance = (@csum = @csum - expenses) OVER (partition BY voteid ORDER BY expenditureid) AS balance;

错误消息:
子查询返回多于1行
表1

| voteid | allocation |
+--------+------------+
| 1      | 50000      |
| 2      | 10000      |
| 3      | 34000      |
| 4      | 70000      |

表2

| expenditureid | voteid | expenses |
    +---------------+--------+----------+
    | 1             | 2      | 300      |
    | 2             | 2      | 650      |
    | 3             | 4      | 900      |
    | 4             | 4      | 1200     |
    | 5             | 3      | 34000    |

预期结果

+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| expenditureid | voteid | expenses | balance (running difference) | calculation (sample column should not be included)  |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              | 10000                                               |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 1             | 2      | 300      | 9700                         | (10000-300)                                        |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 2             | 2      | 650      | 9050                         | (9700-650)                                          |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              |                                                     |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              | 70000                                               |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 3             | 4      | 900      | 69100                        | (70000-900)                                         |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 4             | 4      | 1200     | 67900                        | (69100-1200)                                        |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              |                                                     |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              | 34000                                               |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 5             | 3      | 4000     | 30000                        | (34000-4000)                                        |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
smdnsysy

smdnsysy1#

根据您所显示的内容,总分配不是一列。
你应该做的是:

SET @csum = (SELECT sum(vote.allocation) AS total_allocation 
             FROM vote 
             INNER JOIN expenditure ON vote.voteid = expenditure.voteid 
             GROUP BY vote.voteid);

相关问题