高效的SQL表联接或POSTGRES中的查询?怎么做和怎么做? [英] Efficient SQL table joining or query in POSTGRES ? How and what to do?

问题描述

我有下表

表1

 ID1   YEAR1       
 1      1980 
 2      1964 
 3      1910

表2

 ID2  YEAR2   VALUE 
 1     2000     A 
 1     1900     B 
 2     1950     C
 2     1900     B 
 3     2000     C 
 3     1970     B 
 4     1900     D 
 4     1800     E

我想将这些表联接/查询到:

I would like to join / query these tables to:

对于表1中的每个ID1匹配项，添加一个名为VALUE的列-这样列名将为A，B，...，依此类推，并且该列将为TRUE(T)或FALSE(F).

for each ID1 in table 1 match, add a column named VALUE - so the column names would be A, B, ... and so on, and the column would be either TRUE(T) or FALSE(F).

如果存在ID1匹配表2中的ID2，并且表2中给定行的YEAR2匹配ID2小于表1中给定1的YEAR1，则A ...列为TRUE来自表2中ID2的行中具有A

The column A ... would be TRUE, if there was ID1 matching ID2 from table 2, and the YEAR2 for that given row in table 2 which matched ID2 was less than YEAR1 from that table 1 having given 1, and VALUE from row having ID2 from table 2 had A

因此，结果表如下:

ID1   YEAR1     A   B   C   D   E         

 1      1980    F   T   F   F   F 
 2      1964    F   T   T   F   F 
 3      1910    F   F   F   F   F

推荐答案

您可以使用如下查询:

SELECT t1.ID1, t1.YEAR1,
       COUNT(CASE WHEN t2.VALUE = 'A' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS A,
       COUNT(CASE WHEN t2.VALUE = 'B' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS B,
       COUNT(CASE WHEN t2.VALUE = 'C' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS C,
       COUNT(CASE WHEN t2.VALUE = 'D' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS D,
       COUNT(CASE WHEN t2.VALUE = 'E' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS E,
       COUNT(CASE WHEN t2.VALUE = 'F' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS F      
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t1.ID1 = t2.ID2
GROUP BY t1.ID1, t1.YEAR1

例如列A的值是1，则将其视为T，否则将其视为F.

If, e.g. value of column A is 1, then this is considered as T, otherwise it is considered as a F.

> 此处演示

这篇关于高效的SQL表联接或POSTGRES中的查询?怎么做和怎么做?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！