帮助编写复杂SQL查询的最佳工具？ [英] Best tool for assisting writing complex SQL-queries?

问题描述

我经常发现自己花了很多时间来弄清楚为什么某些SUM（） - 聚合在SQL查询中错误地总结。这个问题经常发生，如果我不在乎加入一个JOIN，导致重复的值被总结等。如果我使用一个大查询，大量的JOIN，嵌套的子查询，GROUP BY等等的事情变得非常复杂的调试。

我想知道是否存在一个工具，通过图形化地说明正在汇总，连接在一起的数据等，使得更容易构建和调试复杂的查询。

我正在寻找的示例在 http://www.imada.sdu.dk/~sorenh07/misc/datacentric-querytool.png

解决方案

这是一个早期的模型，我对任何相关的评论感兴趣。最好的工具这是一本教科书。这是软件不能为你做的那些领域之一。当然，它可以建立简单的选择&连接，但不是聚合函数或分组，或子选择等。为此，你必须学习做旧的方式 - 手/ wetware。我推荐：Lynn Beighley通过O'Reilly Press出版的Head First SQL。

1. 取得一本书。


2. 安装您选择的数据库。


3. 导入一些测试数据。


4. 尝试使用尽可能多的函数执行查询。

练习，练习，练习。

I often find myself spending a lot of time figuring out why certain SUM()-aggregates sum up wrongly in SQL-queries. This problem often occurs if I do not take care when adding a JOIN, resulting in duplicate values being summed up etc. If I work with a big query with lots of JOINs, nested subqueries, GROUP BYs etc. things get very complicated to debug.

I would like to know if there exist a tool that will make it easier to construct and debug complicated queries, by graphically illustrating the data being summed up, joined together etc.

An example of what I'm looking for is illustrated in http://www.imada.sdu.dk/~sorenh07/misc/datacentric-querytool.png

It is of course just an early mockup, and I am interested in any comment related to this.

解决方案

IMHO, the best "tool" for this is a textbook. This is one of those areas where the software can't do it for you. Sure, it can build simple selects & joins, but not aggregate functions or groupings, or sub-selects, etc. For that, you've got to learn to do it the old fashioned way -- by hand/wetware. I recommend: "Head First SQL" by Lynn Beighley, via O'Reilly Press.

1. Get a book.
2. Install the database of your choice.
3. Import some test data.
4. Try to execute queries using as many functions as possible.

Practice, practice, practice.

这篇关于帮助编写复杂SQL查询的最佳工具？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！