在编写SQL查询时如何不重复自己？

Lets take this simple query (code below may not be exact)

让我们来看看这个简单的查询(下面的代码可能不准确)

select a, b, c from inventory where x=:x AND y=:y limit 20

Lets say I use using an ORM that maps the rows to an inventory class and my function was this

假设我使用ORM将行映射到库存类,我的功能就是这个

inventory[] get_inventory(int x, int y)

Now lets say I need extra data using the SAME query. I want to join a table and get 2 extra fields on top of that query

现在假设我需要使用SAME查询来获取额外数据。我想加入一个表,并在该查询之上获得2个额外字段

class inventory2 : inventory { string owner_name; long owner_id; }
...
inventory2[] get_inventory2(int x, int y) {
...
select a, b, c, o.owner_name, o.id as owner_id from inventory i join owner o on i.owner=o.id where x=:x AND y=:y limit 20
CHANGES         ^----------------------------^                  ^--------------------------^

This should be the same query in every way. I ONLY want extra fields. I can't think of a way to do this except copy/pasting sql and hope I don't forget to change one when I change the other.

这应该是各种方式的相同查询。我只想要额外的字段。除了复制/粘贴sql之外,我想不出办法做到这一点,并希望我在改变另一个时不要忘记更改一个。

How do I not repeat myself?

我怎么不重复自己?

3 个解决方案

#1

It's not possible to share the select list since this is two distinct queries, and both need their own list of fields to select.

共享选择列表是不可能的,因为这是两个不同的查询,并且都需要自己的字段列表来进行选择。

#2

You will need to change your query to use an INNER JOIN or your query (as written) will get a Cartesian join resultset.

您需要更改查询以使用INNER JOIN,否则您的查询(如编写)将获得笛卡尔连接结果集。

SELECT a, b, c, o.owner_name, o.id as owner_id 
FROM inventory i INNER JOIN owner o ON i.owner=o.id 
WHERE x=:x AND y=:y LIMIT 20

If this is for SQL Server you will also need to remove the LIMIT 20 and put TOP 20 in the selection list.

如果这是用于SQL Server,您还需要删除LIMIT 20并将TOP 20放入选择列表中。

SELECT TOP 20 a, b, c, o.owner_name, o.id as owner_id 
...

#3

If you're not using an ORM tool that generates the select queries for you, then don't try to over-reduce duplication.

如果您没有使用为您生成选择查询的ORM工具,请不要尝试过度减少重复。

It is possible to do it by separating the queries into fragments and then based on flags you add the fragments you need, like:

可以通过将查询分成片段然后根据您添加所需片段的标志来实现,例如:

var sql = "SELECT A, B, C";

if (test1) sql += ", D, E, F";
if (test2) sql += ", G, H, I";

sql += " FROM inventory ";

if (test1) sql += "inner join .. ";
if (test2) sql += "inner join .. ";

This does work and in some cases (particularly reporting with very dynamic filters) I've done this myself, but it's usually far better to lie with the duplication and have separate queries that are readable then a mess of conditions making it unmaintainable, harder to test, and easily able to generate bad queries depending on combinations of conditions.

这确实有效,并且在某些情况下(特别是使用非常动态的过滤器进行报告)我自己也这样做了,但通常情况下更好的是复制并且具有单独的查询,这些查询是可读的,然后是一堆条件使其无法维护,更难以测试,并根据条件的组合轻松地生成错误的查询。

One of our developers did an experiment with a SQL pre-parser to embed conditions. We had SQL like this:

我们的一位开发人员使用SQL预解析器进行了实验以嵌入条件。我们有这样的SQL:

SELECT
    a, b, c
    , d, e, f   /*? test1 */
    , g, h, i   /*? test2 */
FROM
    Table1
        INNER JOIN Table2 ON Table1.ID = Table2.FKID    /*? test1 */
        INNER JOIN Table3 ON Table1.ID = Table3.FKID    /*? test */

This is better than traditional dynamic SQL, it's more readable, but it's still not great and can still be easily messed up and product bad queries depending on combinations of conditions. We ended up not using this in production at all.

这比传统的动态SQL更好,它更具可读性,但它仍然不是很好,仍然可以很容易搞砸和产品不良查询,具体取决于条件的组合。我们最终根本没有在生产中使用它。

All that said, a good ORM is also a good way to go. :-)

总而言之,一个好的ORM也是一个很好的方法。 :-)

#1