mysql - MySQL select 语句缺少一些字段
问题描述
我有以下语句用于从 MySQL DB 中选择一些字段
select finance_budget_issue.budget_date, SUM(finance_budget_issue.amount) AS amount, finance_vote.office_id as vote_office_id, finance_office.office_head as head,
finance_office.office_name AS office_name,
finance_budget.ref_no, finance_budget_issue.view_status, tbl_signature.office_head as sign_office_head, tbl_signature.name AS name,
tbl_signature.post AS post, tbl_signature.sign_id
from finance_budget_issue
inner join finance_budget on finance_budget.budget_id=finance_budget_issue.budget_id
left join finance_vote on finance_budget_issue.vote_id=finance_vote.vote_id
left join finance_vote_description on finance_vote.description=finance_vote_description.vote_description_id
left join finance_office on finance_budget_issue.office=finance_office.office_id
left join tbl_signature on finance_office.office_id=tbl_signature.office_id
该语句工作正常,但没有超出以下字段
tbl_signature.office_head as sign_office_head,
tbl_signature.name AS name,
tbl_signature.post AS post
可能出了什么问题?我认为我使用了不正确的联接。任何人都可以帮忙吗?
表格如下:
财务办公室
+----+-----------+-------------+------+
| id | office_id | office_name | head |
+----+-----------+-------------+------+
| 1 | 48 | A | SS |
| 2 | 69 | B | VV |
+----+-----------+-------------+------+
财务投票
+---------+-----------+----------------+
| vote_id | office_id | vote |
+---------+-----------+----------------+
| 1 | 48 | 320-1-2-1-1001 |
| 2 | 48 | 320-2-2-2-2002 |
| 3 | 69 | 319-1-2-1-1001 |
| 4 | 69 | 319-1-2-2-1102 |
| 5 | 30 | 318-1-1-2-1101 |
+---------+-----------+----------------+
tbl_signature
+---------+-----------+---------+------------+-------------+
| sign_id | office_id | name | post | office_head |
+---------+-----------+---------+------------+-------------+
| 1 | 48 | Noel | Accountant | Manager |
| 2 | 69 | Jhon | Accountant | Manager |
| 3 | 30 | Micheal | Accountant | Manager |
+---------+-----------+---------+------------+-------------+
财务预算
+-----------+--------+-------------+
| budget_id | ref_no | budget_date |
+-----------+--------+-------------+
| 1 | Acc/01 | 2020-01-20 |
| 2 | Acc/02 | 2020-01-22 |
+-----------+--------+-------------+
财务预算问题
+----+-----------+--------+---------------+-----------------+
| id | budget_id | amount | budget_status | transfer_status |
+----+-----------+--------+---------------+-----------------+
| 1 | 1 | 75000 | issues | Approved |
| 2 | 1 | 22000 | issues | Approved |
| 3 | 2 | 65000 | issues | Approved |
+----+-----------+--------+---------------+-----------------+
期望的输出
+--------+----------------+------+--------+------------------+------+------------+
| amount | vote_office_id | head | ref_no | sign_office_head | name | post |
+--------+----------------+------+--------+------------------+------+------------+
| 75000 | 48 | SS | Acc/01 | Manager | Noel | Accountant |
| 22000 | 48 | SS | Acc/01 | Manager | Noel | Accountant |
| 65000 | 69 | VV | Acc/02 | Manager | Jhon | Accountant |
+--------+----------------+------+--------+------------------+------+------------+
生成的输出(不正确)
+--------+----------------+------+--------+------------------+------+------+
| amount | vote_office_id | head | ref_no | sign_office_head | name | post |
+--------+----------------+------+--------+------------------+------+------+
| 75000 | 48 | SS | Acc/01 | | | |
| 22000 | 48 | SS | Acc/01 | | | |
| 65000 | 69 | VV | Acc/02 | | | |
+--------+----------------+------+--------+------------------+------+------+
解决方案
这更容易阅读:
SELECT i.budget_date
, SUM(i.amount) amount
, v.office_id vote_office_id
, o.office_head head
, o.office_name
, b.ref_no
, i.view_status
, s.office_head sign_office_head
, s.name
, s.post
, s.sign_id
FROM finance_budget_issue i
JOIN finance_budget b
ON b.budget_id = i.budget_id
LEFT
JOIN finance_vote v
ON v.vote_id = i.vote_id
LEFT
JOIN finance_vote_description d
ON d.vote_description_id = v.description
LEFT
JOIN finance_office o
ON i.office = o.office_id
LEFT
JOIN tbl_signature s
ON s.office_id = o.office_id
您有一个聚合函数(和非聚合列)但没有 GROUP BY 子句;那是行不通的。您有一个 LEFT JOINed 表,您不能从中选择任何列;这是没有意义的。
如需进一步帮助,请参阅为什么我应该为在我看来是一个非常简单的 SQL 查询提供 MCRE
推荐阅读
- jquery - jQuery 在单击事件中的 2 个函数之间切换
- java - 设置刻度();不在 andriod studio 3.6.3 中工作
- android - VideoView 给出 FileNotFoundException
- numpy - 使用 np.diff() 生成索引增量值并使用日期/时间索引数组
- java - 如何从 .properties 文件中读取字符串
- django - 是否有任何与 buildpack 不兼容的可用解决方案应用程序
- typescript - webpack compile 在新计算机上给出 ts-loader 错误
- c - 如何在 C 编程中将零填充元素放入数组中?
- python - 如何将模型对象的字段和值转换为字典的键和值
- amazon-web-services - 如何对 AWS SNS 警报电子邮件进行排序?