android - 在这种情况下如何使用 JOIN 和 CASE WHEN 语句(sqlite android)

Question

我有两个表)

CREATE TABLE `Company` (
    `companyName`   TEXT,
    `status`    TEXT,
    `extra` TEXT,
    `companyId` TEXT,
    `companySecondId`   TEXT

CREATE TABLE `UserList` (
    `userName`  TEXT,
    `companyId` TEXT
);

我想用 companyId 加入 UserList 和 Company

数据示例)

companyName     status  extra  companyId  companySecondId
"CompanyA"  "456"   "456"   "A" "A"
"CompanyB"  "789"   "789"   "B" "A"
"CompanyC"  "123"   "123"   "C" "E"
"CompanyD"  "111"   "111"   "D" "D"

UserName  CompanyId
"UserA" "A"
"UserB" "B"
"UserC" "C"
"UserD" "D"

情况是这样的。

1. 公司可以合并B公司并入A公司

2. 公司可以破产CompanyC 已合并到 CompanyE，但 CompanyE 已破产。

在这种情况下我是这样加入的

select UserList.*, t1.companyName
    from UserList 
    inner join (
        select companyId, 
            (case when exists 
                (select * from Company where a.companySecondId = companyId) 
            then (select companyName from Company where a.companySecondId = companyId) 
            else (select companyName from Company where a.companyId = companyId) 
            end) as companyName 
        from Company a
    ) as t1 on UserList.companyId = t1.companyId

如果每个用户的 companyId 都存在于带有“companySecondId”的公司表中，则显示 companySecondId 的 companyName

如果公司表中不存在每个用户的 companyId 以及“companySecondId”，则显示 companyId 的 companyName 而不是 companySecondId

所以我可以得到这样的结果

userName  companyId  companyName
"UserA" "A" "CompanyA"
"UserB" "B" "CompanyA"
"UserC" "C" "CompanyC"
"UserD" "D" "CompanyD"

但我想像这样显示状态和额外信息但是当存在的情况下 ~ 然后 ~ 否则只返回一列。我怎样才能在 SQLite 中做到这一点？？

userName  companyId  companyName  status  extra
"UserA"     "A"     "CompanyA"        "456"    "456"
"UserB"     "B"     "CompanyA"        "456"    "456"
"UserC"     "C"     "CompanyC"        "123"    "123"
"UserD"     "D"     "CompanyD"        "111"    "111"

Answer 1

问题中的逻辑有点难以理解。但是，我认为您只是在寻找 left join:

select ul.userName,
       coalesce(c2.companyId, c.companyId) as companyId,
       coalesce(c2.status, c.status) as status,
       coalesce(c2.extra, c.extra) as extra
from userlist ul left join
     company c
     on ul.companyId = c.companyId left join
     company c2
     on c2.companyId = c.companySecondId;

第二个 left join 主要是为了查看表中是否存在第二家公司。