更干净的sql查询

hiz5n14c 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(287)

我有两个使用外键联接的表，例如：预算\项目表列“id”和事务表列“父\ id”
每个表都有一些类似的列名，例如name和date。因此，当我查询表时，我会按如下方式提取数据：

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]

我的问题是，有没有更好的方法用更简洁的查询来实现这一点？我尝试了几种不同类型的join语句，但无法以我喜欢的方式输出。我绝不是sqlMaven，如果有任何建议或指导，我都会非常感激。

sql mysql

来源：https://stackoverflow.com/questions/53616913/cleaner-sql-query

2条答案

按热度按时间

fwzugrvs1#

是的，您可以使用内部联接，而不是在表中选择两个表 FROM 语句并在 WHERE 这样做：

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,
INNER JOIN myezbudget.transactions AS transactions ON budget_items.id = transactions.parent_id
WHERE
    budget_items.owner_id = ?`, [req.user.id]

您也可以尝试缩短表别名以使其更紧凑，但如果您希望它们具有描述性，则可以。另外，当列只存在于一个表中时，就不必一直指定表名，不过我认为这是个人的偏好。

赞(0）回复(0）举报 2021-06-18

3qpi33ja2#

我会：
使用现代的 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 = ?

赞(0）回复(0）举报 2021-06-18

我来回答

更干净的sql查询

2条答案

相关问题

热门标签

最新问答