sqlite - 如何正确处理 sqlite 表自引用？

Question

我为一小群牛创建了一个 sqlite 数据库。

CREATE TABLE Animals
(
animal_id PRIMARY KEY,
animal_name CHAR(15) NULL,
date_born DATE NULL,
f_parent REFERENCES Animals (animal_id) NULL,
m_parent REFERENCES Animals (animal_id) NULL,
date_purchased DATE NULL,
registered BIT NOT NULL,
gender CHAR(1) NOT NULL CHECK(gender IN ("M","F")),
breed INTEGER NOT NULL REFERENCES breed (breed_id)
);


CREATE TABLE termination (term_key INTEGER PRIMARY KEY, animal_id   INTEGER, term_date DATE, sold BIT, price SMALLMONEY, comp_market_price SMALLMONEY, comp_market_tier TEXT);

我有这样的声明:

SELECT a1.animal_id, a1.animal_name, a1.f_parent, t1.term_date,   t1.price, t1.comp_market_price, t1.comp_market_tier
FROM Animals AS a1, termination AS t1
WHERE a1.animal_id = t1.animal_id
AND a1.f_parent NOT NULL;

结果是:

id#|'动物名称'|' parent id#'|销售日期...

15|一些名字|4|2014-05-26 ...

...

这是正确的，也是我想要的，除了我想要 parent 的名字来代替“ parent id#”。父 id# 是与后代在同一个表中的键(正如您从上面的 create 语句中看到的那样)，但我不知道如何处理这种自引用。我知道这个问题相当普遍，我尝试过查看表、多个连接等，但无济于事。请显示我如何打印相同结果的代码片段，显示 parent 姓名代替 parent id#/key no。

非常感谢!

Answer 1

可能是这样的？

select a.animal_id, a.animal_name,
       (select animal_name
          from animals
          where a.f_parent = animal_id) as parent,
       t.term_date, t.price, t.comp_market_price, t.comp_market_tier
  from animals as a, termination as t using(animal_id)
  where a.f_parent not null;

或这个？ (更好的执行计划)

select a.animal_id as id, a.animal_name as name,f.animal_name as mother,
       t.term_date, t.price, t.comp_market_price, t.comp_market_tier
  from animals as a, termination as t using(animal_id),
       animals f
  where a.f_parent = f.animal_id
    and a.f_parent is not null;