mysql - 更清晰的 SQL 查询

Question

我有 2 个使用外键连接的表，例如:budget_items 表列“id”和交易表列“parent_id”

每个表都有一些相似的列名，例如名称和日期。因此，当我查询表时，我将按如下方式提取数据:

SELECT
                        budget_items.id AS BI_id,
                        budget_items.owner_id as BI_owner,
                        budget_items.name AS BI_name, 
                        budget_items.date AS BI_date,
                        budget_items.amount AS BI_amount,
                        budget_items.type AS BI_type,
                        transactions.id as TRANS_id, 
                        transactions.parent_id as TRANS_parent,
                        transactions.owner_id as TRANS_owner,
                        transactions.amount as TRANS_amount,
                        transactions.date as TRANS_date,
                        transactions.type as TRANS_type,
                        transactions.processed as TRANS_processed,
                        transactions.name AS TRANS_name 
                    FROM
                        myezbudget.budget_items budget_items,
                        myezbudget.transactions transactions
                    WHERE
                        budget_items.id = transactions.parent_id AND
                        budget_items.owner_id = ?`, [req.user.id]

我的问题是，有没有更好的方法来使用更简洁的查询来做到这一点？我尝试了几种不同类型的连接语句，但无法按照我喜欢的方式输出。我绝不是 SQL 专家，如果有任何建议或指导，我将不胜感激。

Answer 1

我会:

• 使用现代 JOIN 语法。
• 使用较短的表别名。

查询应该如下所示:

SELECT
    i.id AS BI_id,
    i.owner_id as BI_owner,
    i.name AS BI_name, 
    i.date AS BI_date,
    i.amount AS BI_amount,
    i.type AS BI_type,
    t.id as TRANS_id, 
    t.parent_id as TRANS_parent,
    t.owner_id as TRANS_owner,
    t.amount as TRANS_amount,
    t.date as TRANS_date,
    t.type as TRANS_type,
    t.processed as TRANS_processed,
    t.name AS TRANS_name 
FROM myezbudget.budget_items i
JOIN myezbudget.transactions t on i.id = t.parent_id 
WHERE i.owner_id = ?