sql - SQLITE：如何根据另一个表中的不同列选择列值

Question

我提前道歉，因为我不知道如何构造这个问题。我有以下表格：

Sessions:
+----------+---------+
| login    | host    |
+----------+---------+
| breilly  | node001 |
+----------+---------+
| pparker  | node003 |
+----------+---------+
| jjameson | node004 |
+----------+---------+
| jjameson | node012 |
+----------+---------+

Userlist:
+----------+----------------+------------------+
| login    | primary_server | secondary_server |
+----------+----------------+------------------+
| breilly  | node001        | node010          |
+----------+----------------+------------------+
| pparker  | node002        | node003          |
+----------+----------------+------------------+
| jjameson | node003        | node004          |
+----------+----------------+------------------+

我应该执行什么样的 SQL 查询才能得到这样的表？：

+----------+---------+------------+
| login    | Host    | Server     |
+----------+---------+------------+
| jjameson | node004 | Secondary  |
+----------+---------+------------+
| jjameson | node012 | Wrong Node |
+----------+---------+------------+
| pparker  | node003 | Secondary  |
+----------+---------+------------+
| breilly  | node001 | Primary    |
+----------+---------+------------+

目前我只是使用 Go 和一堆结构/哈希图来生成它。我计划将用户/会话迁移到内存sqlite数据库，但我似乎无法围绕查询来获取这种表。

该Server列基于用户是登录到他的主要/次要机器还是错误的机器。

我也把它放在SQL Fiddle中

标签： sqlsqlite

Answer 1

使用case逻辑：

select s.*,
       (case when s.host = ul.primary_server then 'primary'
             when s.host = ul.secondary_server then 'secondary'
             else 'wrong node'
        end) as server
from sessions s left join
     userlist ul
     on s.login = ul.login;