<i id='DHzJa'><tr id='DHzJa'><dt id='DHzJa'><q id='DHzJa'><span id='DHzJa'><b id='DHzJa'><form id='DHzJa'><ins id='DHzJa'></ins><ul id='DHzJa'></ul><sub id='DHzJa'></sub></form><legend id='DHzJa'></legend><bdo id='DHzJa'><pre id='DHzJa'><center id='DHzJa'></center></pre></bdo></b><th id='DHzJa'></th></span></q></dt></tr></i><div id='DHzJa'><tfoot id='DHzJa'></tfoot><dl id='DHzJa'><fieldset id='DHzJa'></fieldset></dl></div>

      1. <legend id='DHzJa'><style id='DHzJa'><dir id='DHzJa'><q id='DHzJa'></q></dir></style></legend>

        <small id='DHzJa'></small><noframes id='DHzJa'>

          <bdo id='DHzJa'></bdo><ul id='DHzJa'></ul>
        <tfoot id='DHzJa'></tfoot>

        如何将 SQL 字符串格式化为格式良好且更易于阅读的可读代码块

        How to format a SQL string into a readable block of code that is well formatted and more easy to read(如何将 SQL 字符串格式化为格式良好且更易于阅读的可读代码块)

          <bdo id='V7sD9'></bdo><ul id='V7sD9'></ul>
            <tbody id='V7sD9'></tbody>

                <tfoot id='V7sD9'></tfoot>
              • <i id='V7sD9'><tr id='V7sD9'><dt id='V7sD9'><q id='V7sD9'><span id='V7sD9'><b id='V7sD9'><form id='V7sD9'><ins id='V7sD9'></ins><ul id='V7sD9'></ul><sub id='V7sD9'></sub></form><legend id='V7sD9'></legend><bdo id='V7sD9'><pre id='V7sD9'><center id='V7sD9'></center></pre></bdo></b><th id='V7sD9'></th></span></q></dt></tr></i><div id='V7sD9'><tfoot id='V7sD9'></tfoot><dl id='V7sD9'><fieldset id='V7sD9'></fieldset></dl></div>

              • <small id='V7sD9'></small><noframes id='V7sD9'>

                <legend id='V7sD9'><style id='V7sD9'><dir id='V7sD9'><q id='V7sD9'></q></dir></style></legend>

                  本文介绍了如何将 SQL 字符串格式化为格式良好且更易于阅读的可读代码块的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  当我编写构建动态 SQL 语句的代码时,我经常使用诸如 MS Access 提供的查询构建器工具来生成语句的基础,然后我将在我的代码中使用该工具.

                  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.

                  但是,如果此 SQL 语句格式正确,则阅读代码会容易得多.任何人都可以就如何最好地将本质上是无格式的长 SQL 语句并将其转换为更易于阅读的漂亮文本字符串或漂亮代码块提供建议.

                  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?

                  它的关键是:

                  A.使用下面的aa"程序让阅读变得更容易.

                  A. using the following "aa" procedure which make reading so much easier.

                  B.如果您使用查询构建器(如在 MS Access 中)构建 SQL 字符串,最好配置一个实用程序来获取构建的 SQL 并重新格式化它,以便生成的 SQL 看起来非常像下面的那样.(我使用 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)

                  C.通过在每行之前添加 vbCrLf,当 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, "  ;"
                  

                  注意:

                  1. 表格连接的括号对齐

                  1. the brackets for the table joins line up

                  我使用大写别名来强调它们是别名,以便它们脱颖而出

                  I use uppercase aliases to re-inforce they are aliases and so that they stand out

                  有时我会添加空格来分隔关键区域(SELECT、WHERE、GROUP BY、FROM 等),通常是当它沿着一小段代码并且可能非常动态时(即当它有很多变量时改变它的构建方式)

                  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 字符串格式化为格式良好且更易于阅读的可读代码块的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Building a comma separated list?(建立一个逗号分隔的列表?)
                  Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column(尽管每列都使用了 varchar(MAX),但在导入 CSV 文件时 SQL Server 中出现错误)
                  How can I import an Excel file into SQL Server?(如何将 Excel 文件导入 SQL Server?)
                  Export table to file with column headers (column names) using the bcp utility and SQL Server 2008(使用 bcp 实用程序和 SQL Server 2008 将表导出到带有列标题(列名称)的文件)
                  Concat field value to string in SQL Server(将字段值连接到 SQL Server 中的字符串)
                  SQL Server Bulk insert of CSV file with inconsistent quotes(SQL Server 批量插入带有不一致引号的 CSV 文件)
                  • <bdo id='5VHvx'></bdo><ul id='5VHvx'></ul>
                    <legend id='5VHvx'><style id='5VHvx'><dir id='5VHvx'><q id='5VHvx'></q></dir></style></legend>

                          <tbody id='5VHvx'></tbody>
                          <tfoot id='5VHvx'></tfoot>
                          <i id='5VHvx'><tr id='5VHvx'><dt id='5VHvx'><q id='5VHvx'><span id='5VHvx'><b id='5VHvx'><form id='5VHvx'><ins id='5VHvx'></ins><ul id='5VHvx'></ul><sub id='5VHvx'></sub></form><legend id='5VHvx'></legend><bdo id='5VHvx'><pre id='5VHvx'><center id='5VHvx'></center></pre></bdo></b><th id='5VHvx'></th></span></q></dt></tr></i><div id='5VHvx'><tfoot id='5VHvx'></tfoot><dl id='5VHvx'><fieldset id='5VHvx'></fieldset></dl></div>

                          <small id='5VHvx'></small><noframes id='5VHvx'>