问题描述
我有下表
表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中的查询?怎么做和怎么做?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!