主页 / dba / 问题 / 346020
Accepted
showkey
Asked: 2025-04-04 08:28:23 +0800 CST

如何从全外连接中获得增强的结果？

  • 772

有两个原始数据文件q4.csv和q3.csv。
显示q4.csv：

cat q4.csv    
nameofissuer,shares
nvr inc,11112
pool corp,598689
jefferies finl group inc,433558
constellation brands inc,5624324
bank amer corp,680233587
occidental pete corp,264178414

展示q3.csv：

cat /tmp/q3.csv
nameofissuer,shares
nvr inc,11112
pool corp,404057
jefferies finl group inc,433558
bank amer corp,797683307
occidental pete corp,255281524
vanguard index fds,43000
spdr s&p 500 etf tr,39400
ulta beauty inc,24203

进入psql准备表：

create database project;
\c project
create table q4(nameofissuer  text,shares int);
copy q4(nameofissuer,shares)
from '/tmp/q3.csv' delimiter ',' csv header;
create table q3(nameofissuer  text,shares int);
copy q3(nameofissuer,shares)
from '/tmp/q3.csv' delimiter ',' csv header;

使用完全外连接：

select           
    q4.nameofissuer as issuer_q4,q4.shares as shares_end,q3.nameofissuer as issuer_q3,q3.shares as shares_start from q4
full outer join q3
on q4.nameofissuer  = q3.nameofissuer;

        issuer_q4         | shares_end |        issuer_q3         | shares_start 
--------------------------+------------+--------------------------+--------------
 bank amer corp           |  680233587 | bank amer corp           |    797683307
 constellation brands inc |    5624324 |                          |             
 jefferies finl group inc |     433558 | jefferies finl group inc |       433558
 nvr inc                  |      11112 | nvr inc                  |        11112
 occidental pete corp     |  264178414 | occidental pete corp     |    255281524
 pool corp                |     598689 | pool corp                |       404057
                          |            | spdr s&p 500 etf tr      |        39400
                          |            | ulta beauty inc          |        24203
                          |            | vanguard index fds       |        43000

如何使用纯 SQL 命令获得以下所需输出？

     nameofissuer         | shares_end | shares_start |   change   
--------------------------+------------+--------------+------------
 bank amer corp           |  680233587 |    797683307 | -117449720
 constellation brands inc |    5624324 |            0 |    5624324
 jefferies finl group inc |     433558 |       433558 |          0
 nvr inc                  |      11112 |        11112 |          0
 occidental pete corp     |  264178414 |    255281524 |    8896890
 pool corp                |     598689 |       404057 |     194632
 spdr s&p 500 etf tr      |          0 |        39400 |     -39400
 ulta beauty inc          |          0 |        24203 |     -24203
 vanguard index fds       |          0 |        43000 |     -43000
postgresql

1 个回答

  1. Best Answer
    WITH q34 (nameofissuer) AS (
    SELECT nameofissuer FROM q3
    UNION 
    SELECT nameofissuer FROM q4
    )
SELECT q34.nameofissuer,
       COALESCE(q4.shares, 0) AS shares_end,
       COALESCE(q3.shares, 0) AS shares_start,
       COALESCE(q3.shares, 0) - COALESCE(q4.shares, 0) AS change
FROM q34
LEFT JOIN q3 ON q34.nameofissuer = q3.nameofissuer
LEFT JOIN q4 ON q34.nameofissuer = q4.nameofissuer

    或者

    SELECT COALESCE(q3.nameofissuer, q4.nameofissuer) AS nameofissuer,
       COALESCE(q4.shares, 0) AS shares_end,
       COALESCE(q3.shares, 0) AS shares_start,
       COALESCE(q3.shares, 0) - COALESCE(q4.shares, 0) AS change
FROM q4
FULL OUTER JOIN q3 ON q4.nameofissuer  = q3.nameofissuer;

    查询假设nameofissuer在中是唯一的q3，并且对于也是唯一的q4。

    • 3

相关问题