为了可读性/可维护性而拆分长查询(Splitting up long queries for readability / maintainability)

为了可读性/可维护性而拆分长查询(Splitting up long queries for readability / maintainability)

我继承了一个Access数据库，该数据库的SELECT查询超过50列。 即使在MS Access图形查询设计工具中，处理IMO的信息也太多了。

快速免责声明 - 我有编程背景，但几乎没有数据库经验。

对于这个特殊问题，我需要50多列的数据。 获取此信息是否有比一个大查询更好的方法？

我对如何继续有点不知所措。 网络上的共识似乎是SQL查询应该保持相对较小并且格式良好。 当你需要这么多领域时，我只是没有看到如何应用这个原则。 我可以使用UNION或INSERT INTO临时表来进行大量简单查询吗？ 有任何建议或RTFM吗？

编辑：有关应用程序的更多信息。 数据分布在14个表中。 我正在抓取数据将其写入外部文件，每行有50多个字段（想想电子表格的CSV版本）。

编辑： 在MS Access中管理和调试SQL查询看起来像包含相关的建议。

I have inherited a Access database that has a query that SELECTs over 50 columns. Even in the MS Access graphical query design tool, it's too much information to handle IMO.

A quick disclaimer - I have a programming background, but almost no experience with databases.

For this particular problem, I need data from 50+ columns. Is there a better way to get this information than one huge query?

I am a bit at a loss on how to proceed. The consensus on the web seems to be that SQL queries should be kept relatively small and well formated. I just don't see how to apply that principle when you need so many fields. Can I use lots of simple queries with a UNION or INSERT INTO a temporary table? Any suggestion or RTFMs?

EDIT: More info on the application. The data is spread across 14 tables. I'm grabbing the data to write it out to an external file which has 50+ fields per row (think CSV version of a spreadsheet).

EDIT: Managing and debugging SQL queries in MS Access looks like it contains relevant advice.

最满意答案

我觉得你一点也不担心自己。 从14个表中提取50个字段以输出到平面文件根本不算什么。 关键是你有意对数据进行非规范化，因为你需要一个平面文件输出。 根据定义，这意味着很多列。

正如其他人所说，一个表中的50列不同寻常（虽然不一定是设计错误），但在你描述的情况下，我根本没有看到问题。

简而言之，它听起来像一个完美的设置，我只是想知道是什么让你认为这是一个问题开始。

I think you're worrying yourself over nothing at all. Pulling 50 fields from 14 tables in order to output to a flat file is nothing at all. The point is that you're intentionally denormalizing data because you need a flat-file output. By definition, that will mean lots of columns.

As others have said, 50 columns in one table would be out of the ordinary (though not necessarily a design error), but in the situation you describe, I don't see an issue at all.

In short, it sounds like a perfectly fine setup and I just have to wonder what makes you think it's a problem to begin with.