SQL表/子查询别名约定 [英] SQL Table / Sub-Query Alias Conventions

问题描述

多年来，我一直在各种DBMS(Oracle，SQL Server，MySQL，Access等)上编写SQL，而一直困扰着我的一件事是，表似乎缺乏命名约定&子查询别名.

我一直都读过表别名是解决问题的方法，尽管我并不总是使用它们，但是当我这样做时，我总是被卡在要使用的名称之间.我已经从使用描述性名称变为单个字符，例如"t"，"s"或"q"，然后再返回.以我刚刚编写的此MS Access查询为例，即使使用相对简单的查询，我对使用的别名仍然不完全满意，我仍然认为它不那么容易阅读: /p>

SELECT stkTrans.StockName
    , stkTrans.Sedol
    , stkTrans.BookCode
    , SUM(IIF(stkTrans.TransactionType="S", -1 * stkTrans.Units, 0)) AS [Sell Shares]
    , SUM(IIF(stkTrans.TransactionType="B", stkTrans.Units, 0)) AS [Buy Shares]
    , SUM(IIF(stkTrans.TransactionType="B", -1 * stkTrans.Price, 0) * stkTrans1.Min_Units) + SUM(IIF(stkTrans.TransactionType="S", stkTrans.Price, 0) * stkTrans1.Min_Units) AS [PnL]
    , "" AS [Comment]
FROM tblStockTransactions AS stkTrans 
INNER JOIN (SELECT sT1.BookCode
                    , sT1.Sedol
                    , MIN(sT1.Units) AS [Min_Units]
            FROM tblStockTransactions sT1
            GROUP BY sT1.BookCode, sT1.Sedol
            HAVING (SUM(IIF(sT1.TransactionType="S", 1, 0)) > 0
            AND SUM(IIF(sT1.TransactionType="B", 1, 0)) > 0)) AS stkTrans1 ON (stkTrans.BookCode = stkTrans1.BookCode) AND (stkTrans.Sedol = stkTrans1.Sedol)
GROUP BY stkTrans.BookCode, stkTrans.StockName, stkTrans.Sedol;

您怎么看?我以为我会把它扔在那里，看看其他人对此有什么感觉.

解决方案

我不知道任何跨数据库命名表/查询别名的规范样式规则，尽管我知道Oracle建议使用三到四个字符的缩写. /p>

我通常会 避免使用单字母缩写， 除外，因为查询非常简单，以至于对于那些必须维护代码的人来说，它们应该是完全明确的.每个查询超过两个或三个表.

我通常也将避免使用长别名名，这些别名应符合数据库表命名约定的一般样式，因为它可能会弄不清楚什么是数据库表名以及什么是别名.

在所提供的示例中，内联视图内的别名sT1完全没有必要，因为在该内联视图内仅可访问一个表.这样一来，一个表就被连接到查询中的一个内联视图(基于同一个表)-在这种情况下，我将使用s作为该表的别名，并使用s1作为该内联视图的别名(表示它正在查询相同的基础数据库表.

I've been writing SQL for a number of years now on various DBMS (Oracle, SQL Server, MySQL, Access etc.) and one thing that has always struck me is the seemingly lack of naming convention when it comes to table & sub-query aliases.

I've always read that table alises are the way to go and although I haven't always used them, when I do I'm always stuck between what names to use. I've gone from using descriptive names to single characters such as 't', 's' or 'q' and back again. Take for example this MS Access query I've just written, I'm still not entirely happy with the aliases I'm using even with a relatively simple query as this, I still don't think it's all that easy to read:

SELECT stkTrans.StockName
    , stkTrans.Sedol
    , stkTrans.BookCode
    , SUM(IIF(stkTrans.TransactionType="S", -1 * stkTrans.Units, 0)) AS [Sell Shares]
    , SUM(IIF(stkTrans.TransactionType="B", stkTrans.Units, 0)) AS [Buy Shares]
    , SUM(IIF(stkTrans.TransactionType="B", -1 * stkTrans.Price, 0) * stkTrans1.Min_Units) + SUM(IIF(stkTrans.TransactionType="S", stkTrans.Price, 0) * stkTrans1.Min_Units) AS [PnL]
    , "" AS [Comment]
FROM tblStockTransactions AS stkTrans 
INNER JOIN (SELECT sT1.BookCode
                    , sT1.Sedol
                    , MIN(sT1.Units) AS [Min_Units]
            FROM tblStockTransactions sT1
            GROUP BY sT1.BookCode, sT1.Sedol
            HAVING (SUM(IIF(sT1.TransactionType="S", 1, 0)) > 0
            AND SUM(IIF(sT1.TransactionType="B", 1, 0)) > 0)) AS stkTrans1 ON (stkTrans.BookCode = stkTrans1.BookCode) AND (stkTrans.Sedol = stkTrans1.Sedol)
GROUP BY stkTrans.BookCode, stkTrans.StockName, stkTrans.Sedol;

What do you think? Thought I would throw it out there to see what everyone else's feelings are about this.

解决方案

I don't know of any canonical style rules for naming table/query aliases across databases, although I understand that Oracle recommends abbreviations of three to four characters.

I would generally steer clear of single letter abbreviations, except where the query is sufficiently simple that these should be completely unambiguous to anyone having to maintain the code - typically no more than two or three tables per query.

I would also generally avoid long alias names that conform to the general style of your database table-naming conventions, since it can become unclear what is a database table name and what is an alias.

In the example provided, the alias sT1 inside the inline view is utterly unnecessary, as there is only one table being accessed within that inline view. That leaves one table being joined to one inline view (based on the same table) in the query - in these circumstances, I would use s as the alias for the table, and s1 as the alias for the inline view (to indicate that it was querying the same underlying database table).

这篇关于SQL表/子查询别名约定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！