将变量添加到连接表

I'm struggling to figure out how I can add a convenience data column to a join table to use for evaluation. Sorry if the terminology is incorrect, I'm a bit of an SQL newbie.

我正在努力弄清楚如何将一个便利数据列添加到连接表以用于评估。对不起,如果术语不正确,我有点像SQL新手。

For this query structure:

对于此查询结构:

SELECT t1.id 
    FROM 
        table1 t1 
    INNER JOIN 
        table2 t2 ON t1.c2 = t2.c1 
    WHERE 
        t1.c5=5;

...I would like to add a column in the join table that is calculated using a function of a few of t1s columns. For example, the sum of t1.x, t1.y, t1.z in a variable called score that can then be referenced in the WHERE clause.

...我想在连接表中添加一个列,该列是使用几个t1s列的函数计算的。例如,t1.x,t1.y,t1.z在一个名为score的变量中的总和,然后可以在WHERE子句中引用。

The reason behind wanting to do this is it will be referenced multiple times and will reduce verbosity and help readability.

想要这样做的原因是它将被多次引用,并将减少冗长和帮助可读性。

I presume it will look something like:

我认为它看起来像:

SELECT t1.id 
    FROM 
        table1 t1 
    INNER JOIN 
        table2 t2 ON t1.c2 = t2.c1 
        -- function(t1.x, t1.y, t1.z) as score
    WHERE 
        t1.c5=5;
        --AND score ...

Using PostgreSQL 9.3

使用PostgreSQL 9.3

2 个解决方案

#1

If the score is calculated from the t1 columns, then use a subquery on t1:

如果分数是从t1列计算的,那么在t1上使用子查询:

SELECT t1.id 
FROM (select t1.*, function(t1.x, t1.y, t1.z) as score
      from table1 t1 
     ) t1 INNER JOIN 
     table2 t2
     ON t1.c2 = t2.c1 
WHERE t1.c5 = 5 AND
      t1.score . . .

#2

A good option would be to use the having clause.

一个好的选择是使用having子句。

Select t1.id, SUM(t1.x + t1.y + t1.z)
FROM table1 t1 inner join table2 t2 on t1.c2 = t2.c1
WHERE t1.c5 = 5
GROUP BY t1.id
Having SUM(t1.x + t1.y + t1.z) = ...

Or, you could do a sub-query in the from clause. The below code makes some assumptions on structure.

或者,您可以在from子句中执行子查询。下面的代码对结构做了一些假设。

SELECT t1.id 
    FROM 
        (Select SUM(t1.x + t1.y + t1.z) as score, t1.c5, t1.c2, t1.id
         FROM table1 t1
         GROUP BY t1.id
        ) t1
    INNER JOIN table2 t2 on t1.c2 = t2.c1

    WHERE 
        t1.c5=5
        And t1.score = ...

#1