gpt4 book ai didi

python - MySQL在同一张表上加入两次给出了一半的结果

转载 作者:行者123 更新时间:2023-11-30 21:31:55 27 4
gpt4 key购买 nike

我有 2 个表并进行连接查询,事情是在主表中我有 2 个带有 id 的列,需要用第二个表中的全名替换,这就是为什么我在同一个表上运行查询两次。事情是它没有大约一半的记录,如果我改变连接的位置然后下半部分出现。标识符 2 列中的数据类似于:(271, 272), (272, 271)

    query = "SELECT * FROM (SELECT * from transactions {2} LIMIT {3}, {4}) as transactions " \
"LEFT JOIN (SELECT account_number as dr, account_name as dr_n, acc_id, company_id, deleted FROM tb_accounts) as tb1 ON tb1.acc_id = transactions.dr_acc " \
"LEFT JOIN (SELECT account_number as cr, account_name as cr_n, acc_id, company_id, deleted FROM tb_accounts) as tb2 ON tb2.acc_id = transactions.cr_acc " \
"LEFT JOIN (SELECT document as doc_name, doc_id, company_id, deleted FROM documents ORDER BY documents.date DESC) as doc1 ON doc1.doc_id = transactions.document " \
"and doc1.company_id = {0} and doc1.deleted = 0 " \
"WHERE tb1.company_id = {0} and tb1.deleted = 0 and " \
"tb2.company_id = {0} and tb2.deleted = 0 and " \
"transactions.company_id = {0} and {1} transactions.deleted = 0".format(company_id, filter, sort, sn, en)

最佳答案

您的查询看起来很复杂,并且可以通过生成临时表以在连接中使用来执行得相当慢。

此外,我认为您在那里不需要LEFT JOIN。相反,它应该是 JOIN(INNER JOIN)。

问题的一个原因是张贴在顶部的 LIMIT 运算符。

我可以建议使用以下查询(看看我是如何把它放在 Python 中的——在数据库客户端中复制/粘贴和测试要方便得多):

query = """
SELECT *
FROM transactions as transactions
JOIN tb_accounts as tb1
ON tb1.acc_id = transactions.dr_acc AND tb1.deleted = 0 AND tb1.company_id = {0}
JOIN tb_accounts as tb2
ON tb2.acc_id = transactions.cr_acc AND tb2.deleted = 0 AND tb2.company_id = {0}
JOIN documents as doc1
ON doc1.doc_id = transactions.document
AND doc1.company_id = {0}
AND doc1.deleted = 0
WHERE transactions.company_id = {0}
AND transactions.deleted = 0
{1}
{2}
LIMIT {3}, {4}
""".format(company_id, filter, sort, sn, en)

为了优化上述查询的速度,以下索引会很有用:

CREATE INDEX idx_del_comp_acc
ON tb_accounts (deleted, company_id, acc_id);

CREATE INDEX idx_del_comp_doc
ON documents (deleted, company_id, doc_id);

CREATE INDEX idx_del_comp_dr_acc
ON transactions (deleted, company_id, dr_acc);

CREATE INDEX idx_del_comp_cr_acc
ON transactions (deleted, company_id, cr_acc);

在索引中按上述顺序排列列很重要。

此外,运行查询时还有哪些其他条件以及您将运行哪些其他查询也很重要。在这种情况下,您可以添加更多索引或重新设计上面的索引。

在查询前使用命令 EXPLAIN 以查看服务器将如何运行它以及将使用哪些索引(如果有)。

不过请记住,添加索引会增加 INSERT 和 UPDATE 请求的时间。通常,使用索引比插入速度稍慢的好处要多几倍。

关于python - MySQL在同一张表上加入两次给出了一半的结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55769498/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com