使用标准的SQL，我如何做一个有标题的报告?

Using Standard SQL, how do I make a report with a top title?

使用标准的SQL，我如何做一个有标题的报告?

Last year I participated in a Database competition. The description said I could use MySQL, Oracle, or MS SQL Server. I choose MySQL because that is what I am familiar with. It said that I must write an sql statement using Standard SQL that will work in any of the listed DBMS. I have searched google, but no luck on finding any tutorials or anything related to the issues below. Any suggestions on where to look or what to search would be great.

去年我参加了一个数据库竞赛。描述说我可以使用MySQL、Oracle或MS SQL Server。我选择MySQL是因为我对它很熟悉。它说，我必须使用标准sql编写一条sql语句，它将在任何列出的DBMS中工作。我搜索了谷歌，但是没有找到任何教程或任何与以下问题相关的东西。任何关于去哪里找或者找什么东西的建议都很好。

1. Group by and perform a control break on the plan ID (skip one line after the break).
2. 分组并对计划ID执行控制中断(中断后跳过一行)。
3. Insert a top title and bottom title as shown.
4. 插入顶部标题和底部标题，如下所示。
5. Set your page size to 30, and the width of the line to 65.
6. 将页面大小设置为30，行宽度设置为65。
7. Column 1 has a width of 10, Column 2 has a width of 15, Column 3 has a width of 15, and Column 4 has a width of 20.
8. 第一列宽度为10，第二列宽度为15，第三列宽度为15，第四列宽度为20。

The expected formatted report is shown below:

预期格式的报告如下:

2 个解决方案

#1

The comments above to the question not withstanding, 18 months ago I had to build 2 queries similar to this for a real-world reporting application. The requirements were unusual, and for an unusual reporting environment, but none-the-less were quite sane and sensible. The end-product of the query was then spun into MS-Access for final reporting and printing. It was quite fun to program actually.

上面关于这个问题的评论不管怎样，18个月前，我为一个真实的报告应用程序构建了两个类似的查询。这些要求是不寻常的，对于一个不寻常的报告环境来说也是如此，但无论如何都是相当理智和明智的。然后，查询的最终结果被分解为MS-Access，用于最终报告和打印。实际上编程是很有趣的。

Using SQL Server, the SQL dialect I am most familiar with, the report can be built as follows.

使用SQL Server(我最熟悉的SQL方言)可以构建如下的报表。

Note that of the 30 lines for the report outlined 20 are data rows, and 10 are page header, columns header, or page footer rows.

注意，报告中列出的30行中有20行是数据行，10行是页眉、列头或页脚行。

1. Build a query for the main data grouped on Plan Id that outputs each field converted to a varchar of the requisite length.
2. 为按照计划Id分组的主数据构建一个查询，该查询输出将每个字段转换为所需长度的varchar。
3. Build a super query over (1) that generates a single 65 character text string as each row output
4. 在(1)上构建一个超级查询，该查询在每一行输出时生成一个65个字符的文本字符串
5. Buid a super-query that groups (2) on PlanId with a test on grouping(PlanId) that generates empty strings in every column;this is the blank line after each PlanId group.
6. Buid是一个超级查询，它在PlanId上对(2)进行分组，并进行分组测试(PlanId)，在每一列中生成空字符串;这是每个PlanId组之后的空行。
7. Build a super-query over(3) that assigns a sequential RowNumber to each row, and then calculate fields PageNumber = floor(RowNumber / 20) and PageSequence = RowNumber mod 20.
8. 在(3)上构建一个超级查询，为每一行分配一个顺序行号，然后计算字段PageNumber = floor(行号/ 20)和PageSequence = RowNumber mod 20。
9. Manually build the header/footer rows with appropriate PageSequence values, and cross join to the distinct values of PageNumber from (4).
10. 使用适当的PageSequence值手工构建页眉/页脚行，并与(4)中不同的PageNumber值交叉连接。
11. Union All (4) and (5) with an appropriate Order By clause.
12. 将所有的(4)和(5)结合在一起，并按条款发出适当的命令。
13. Etc.
14. 等。

The actual application where I had to apply these techniques required creating an MS-Access report that had an indefinite number of pages running out horizintally, with 6 numeric columns and 1 descriptive column per page, with the final numeric column being a total of all previous numeric columns. The whole report was three of these, each starting on a new page, but each with a different number of numeric columns. Because of how the raw data was stored on the SQL Server, and the slow data link, it was much more efficient to run the whole report as one query than three separate ones.

应用这些技术的实际应用需要创建一个MS-Access报告，该报告的水平方向上有无数页，每个页面有6个数字列和1个描述性列，最后的数字列是所有以前的数字列的总和。整个报告就是其中的三个，每个都从一个新页面开始，但是每个都有不同数量的数字列。由于原始数据是如何存储在SQL服务器上的，而且数据链接速度很慢，因此将整个报表作为一个查询运行要比三个单独的查询高效得多。

Have fun.

玩得开心。

P.S. Because of other project constraints, I had the benefit of dynamically creating this SQL using a SQL-macro facility similar to that of old-style C; hand-rolled of course, because who ever heard of such a thing. When you can write it that way, it ALMOST looks structured.

由于其他项目的限制，我可以使用与旧式C相似的SQL-macro工具动态创建这个SQL;当然是手工卷的，因为谁听说过这样的事。当你可以这样写的时候，它看起来是结构化的。

#2

I'm going through cleaning up old questions. For anyone who might be curious, I actually used PL/SQL for the competition. Worked great for creating "reports" directly in SQL. http://www.oracle.com/technetwork/database/features/plsql/index.html

我正在整理旧问题。对于任何可能好奇的人，我实际上使用了PL/SQL来进行竞争。在SQL中直接创建“报表”非常有用。http://www.oracle.com/technetwork/database/features/plsql/index.html