sqlite - 兩個表之間的連接

我有兩個表，相同的結構（文件名、文件大小、權限），並且兩者之間可能存在不同或缺失的值..

我想顯示具有以下欄位的所有記錄：

文件名 | 文件大小表1 | 權限表1| 文件大小表2 | 權限表2 | 匹配

我想：

1. 相同的文件名只顯示一次
2. 如果兩個表之一中缺少文件名，則該表的列文件大小和權限將為空
3. 如果兩個表上都存在文件名，但其他欄位之一不同，則“匹配”欄位將顯示“1”
4. 如果兩個表上都存在文件名，並且其他欄位相同，則“匹配”欄位將顯示“0”

步驟 1 可以通過兩個表之間的連接來實現（從 table1 中選擇文件名 union select filename from table2 order by filename;）但是當兩個表之一中缺少文件名時，我被困在如何繼續使用空行表..

解決方案（感謝 Lennart）：

select a.filename as filename
    , a.size as filesize1, a.permissions as permissions1
    , b.size as filesize2, b.permissions as permissions2
    , CASE
       WHEN a.filename IS NULL or b.filename IS NULL THEN 1 
       WHEN a.size != b.size THEN 2
       WHEN a.permissions != b.permissions THEN 2 
       ELSE 0
      END  as Match
from scanTableHost1 as a
LEFT JOIN scanTableHost2 as b 
   ON a.filename = b.filename
UNION 
select b.filename as filename
    , a.size as filesize1, a.permissions as permissions1
    , b.size as filesize2, b.permissions as permissions2 
    , CASE
       WHEN a.filename IS NULL or b.filename IS NULL THEN 1 
       WHEN a.size != b.size THEN 2
       WHEN a.permissions != b.permissions THEN 2 
       ELSE 0
      END  as Match
from scanTableHost2 as b
LEFT JOIN scanTableHost1 as a
   ON a.filename = b.filename

我假設當您說 JOIN 時，您實際上是指兩個表的更一般的組合。JOIN是 SQL 中的運算符，因此在引用UNION. 無論如何，如果我做對了，你想要FULL OUTER JOIN兩個表之間的一個：

select COALESCE(a.filename, b.filename) as filename
    , a.filesize as filesize1, a.permissions as permissions1
    , b.filesize as filesize1, b.permissions as permissions1
from T1 as a
FULL JOIN T2 as b
   ON a.filename = b.filename

COALESCE 是一個從左到右返回第一個非空值的函式。

但是，據我了解，SQLite 僅支持 LEFT JOIN，因此您必須使用兩個 LEFT JOINS 和一個 UNION 重寫查詢：

select a.filename as filename
    , a.filesize as filesize1, a.permissions as permissions1
    , b.filesize as filesize1, b.permissions as permissions1 
    , CASE WHEN b.filename IS NOT NULL -- a match
           THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)  
                     THEN 0 ELSE 1
                END
      END     
from T1 as a
LEFT JOIN T2 as b
   ON a.filename = b.filename
UNION 
select b.filename as filename
    , a.filesize as filesize1, a.permissions as permissions1
    , b.filesize as filesize1, b.permissions as permissions1 
    , CASE WHEN a.filename IS NOT NULL -- a match
           THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)  
                     THEN 0 ELSE 1
                END
      END     
from T2 as b
LEFT JOIN T1 as a
   ON a.filename = b.filename

請注意，a 和 b 在聯合的第二條腿中改變了位置。

引用自：https://dba.stackexchange.com/questions/203291