如何在SQL字符串格式化成code可读块，十分格式化，更易于阅读 [英] How to format a SQL string into a readable block of code that is well formatted and more easy to read
问题描述
在我写code，它建立了一个动态SQL语句我经常使用查询生成器工具，如提供的MS Access来生成然后，我会在我的code使用的语句的基础。
When I am writing code that builds a dynamic SQL statement I often use a query builder tool such as that provided by MS Access to generate the basis of the statement that I will then use in my code.
然而，就这样更容易阅读的code。如果这条SQL语句以及格式化。任何人都可以提供咨询意见如何最好采取什么本质上是一个长的未格式化的SQL语句，并把它变成一个很好的文本字符串或code一个很好的块更易于阅读。
However, it is so much easier to read the code if this SQL statement is well formatted. Can anyone offer advise about how best to take what is essentially a long unformatted SQL statement and turn it into a nice text string or a nice block of code that is easier to read.
推荐答案
我已经使用这种技术在过去，这是这样做的最好方法是什么？
I have used this technique in the past, is this the best method of doing this?
的关键是：
一个。使用下面的AA过程，它使阅读变得更轻松。
A. using the following "aa" procedure which make reading so much easier.
乙。如果你建立一个使用查询生成器（如在MS Access）的SQL字符串，这是很好的配置工具来采取内置SQL并将其重新格式化，这样产生的SQL看起来pretty的多少完全一样如下。 （我用www.sqlinform.com但也有其他人，或者你可以做手工）
B. If you build the SQL string using a query builder (like in MS Access), it is good to configure a utility to take the built SQL and reformat it so that the resulting SQL looks pretty much exactly like that below. (I use www.sqlinform.com but there are others, or you can do it manually)
℃。通过加入vbCrLf beofre每一行中，当SQL字符串是建立在VBA结果字符串可以输出到即时窗口易于阅读，因为这将有线制动器，并很好地排队。 （这不是火箭科学）
C. By adding vbCrLf beofre each line, when the SQL string is built in VBA the resulting string can be output to the immediate window an easily read as it will have line brakes and line up nicely. (It's not rocket science)
Public Sub aa(ByRef a As String, ByVal b As String)
' This is deliberately not a function, although it behaves a bit like one
a = a & vbCrLf & b
End Sub
' The function is called in code like this:
Dim a as string
a = ""
aa a, " SELECT CUR.txtLevel AS [Current] "
aa a, " , NLPMi.strFullName AS [Full Name] "
aa a, " , NLPMi.DOB AS [Birthday] "
aa a, " , NLPMi.Surname AS [Surname Name] "
aa a, " , TOOLS.txtWCMTool "
aa a, " FROM ( ( ( tblPeopleWCMSKILLSByYear AS SKILLS"
aa a, " LEFT JOIN tblSkillLevels AS CUR "
aa a, " ON SKILLS.bytCurrentID = CUR.atnSkillLevelID
aa a, " ) "
aa a, " INNER JOIN [qrylstNames-LPMi] AS NLPMi "
aa a, " ON SKILLS.intPeopleID = NLPMi.atnPeopleRecID
aa a, " )"
aa a, " INNER JOIN tblWCMTools AS TOOLS "
aa a, " ON SKILLS.intWCMSkillID = TOOLS.atnWCMToolID"
aa a, " ) "
aa a, " WHERE ( ( (SKILLS.bytYearID) = YEAR(DATE())-2012 ) "
aa a, " AND CUR.txtLevel >= " & MyVariable1 & " "
aa a, " AND TOOLS.txtWCMTool = '" & MyVariable2 & "'"
aa a, " ) "
aa a, " ORDER BY NLPMi.strFullName"
aa a, " ", " & MyVariable3 & ""
aa a, " ;"
需要注意的是：
Note that:
-
在括号内为表连接排队
the brackets for the table joins line up
我用大写的别名重新INFORCE他们是别名，使他们脱颖而出
I use uppercase aliases to re-inforce they are aliases and so that they stand out
有时候我添加空格分开的关键领域（SELECT，WHERE，GROUP BY，距离等），通常当它沿着code位和可能是非常动态的（即当它有很多那改变它是如何建立的变量）
Sometimes I add spaces to separate the key areas (SELECT, WHERE, GROUP BY, FROM etc), usually when it is along bit of code and possible is very dynamic (ie when it has a lot of variables that change how it is built )
在SELECT，ORDER BY和GROUP BY子句，我喜欢把需要列之间的列名前在同一行上的逗号。
In the SELECT, ORDER BY and GROUP BY clauses, I favour putting the commas that are needed between columns in front of the column name on the same line.
我不喜欢数据库表有他们的名字，即strFullName类型。但是，这不是我的架构！
I don't like database tables have the type in their name ie strFullName. However, this is not my schema!
哈维
这篇关于如何在SQL字符串格式化成code可读块，十分格式化，更易于阅读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
